Tuesday 13 October 2015

How to work with multiple value select prompt in obiee11g Direct Database Request


Scenario:

You have a dashboard prompt with multi-select option and a presentation variable set. You have a report with a field using a filter formula based on the presentation variable.  When multiple values are selected in the dashboard prompt, the query generated is incorrect. The query should select the multiple values selected by a comma. Instead of the results, you get this message:
No Results

The specified criteria didn't result in any data. This is often caused by
applying filters and/or selections that are too restrictive or that contain
incorrect values. Please check your Analysis Filters and try again. The
filters currently being applied are shown below.

This occurs when the Filter SQL function is used in a column formula within a request; for example:
FILTER("Base Facts"."1- Revenue" USING ("Products"."P4 Brand" IN ('@{var_mlt}{FunPod}')))

It does not occur if a standard filter is applied to the analysis.  The incorrect SQL produced is, for example
[2012-02-07T14:47:34.000+00:00] [OracleBIServerComponent] [TRACE:3] [USER-18]
[] [ecid: 20f12d143003324e:d29df33:13558086f8a:-8000-000000000000084b] [tid:
a30] [requestid: 33990013] [sessionid: 33990000] [username: weblogic]
-------------------- Sending query to database named 01 - Sample App Data
(ORCL) (id: <>), connection pool named Sample Relational Connection:
[[
WITH
SAWITH0 AS (select sum(T42442.Revenue) as c1,
T42406.PER_NAME_YEAR as c2
from
SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ ,
SAMP_TIME_QTR_D T42406 /* D03 Time Quarter Grain */ ,
SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */
where ( T42406.QTR_KEY = T42442.Bill_Qtr_Key
and T42409.Brand = 'BizTech,FunPod,HomeView'
and T42409.Prod_Key = T42442.Prod_Key )
group by T42406.PER_NAME_YEAR)
select distinct 0 as c1,
D1.c2 as c2,
D1.c1 as c3
from
SAWITH0 D1
order by c2

The problem is that the multiple values are passed in a single string to the physical SQL, and the operator used is "=", when it must be "IN":
and T42409.Brand = 'BizTech,FunPod,HomeView'

 instead of:
and T42409.Brand in ('BizTech','FunPod,HomeView')
 
However, if you create a report filter like this, you get the correct values:
P4 Brand is equal to / is in @{var_mlt}{FunPod}
 
Root Cause of the Issue:
 
Incorrect syntax used in the 'Filter' option, 'Criteria' tab. There is a specific format of syntax that should be used when you want to work with Presentation Variable.

Solution:

You need to make sure the syntax is correct.


Review the correct syntax to use in the Advanced SQL in a filter to reference a presentation variable with potentially multiple values. The syntax differs slightly depending on the column type.
Text column:
Select T1.C1 from SA where T1.TextColumn in (@{myTextVar}['@']{'Default'})

Numeric column:
Select T1.C1 from SA where T1.NumberColumn in (@{myNumVar}{1000})

Date Time column:

Select T1.C1 from SA where T1.DateTimeColumn in (@{myDateTimeVar}{timestamp '2000-01-21 00:00:00'})

Date-Only column:
Select T1.C1 from SA where T1.DateColumn in (@{myDateVar}{date '2000-01-21'})


Time-Only column:

Select T1.C1 from SA where T1.TimeColumn in (@{myTimeVar}{time '00:00:00'})
 

Applies to:

Business Intelligence Suite Enterprise Edition - Version 11.1.1.5.0 [1308] and later
Business Intelligence Server Enterprise Edition - Version 11.1.1.5.0 [1308] and later
Applies to any platform. 
 

References


OBIEE 11g: Presentation Variable With Multi Value Selected Gives 'No Results' and Wrong Query Generated. (Doc ID 1418868.1)

BUG:13696529 - INCORRECT PHYSICAL QUERY WHEN USING FILTER AND A PRESENTATION VARIABLE MULTIPLE