OBIEE 11g: Presentation Variable With Multi Value Selected Gives 'No Results' and Wrong Query Generated.
(Doc ID 1418868.1)
Last updated on JANUARY 26, 2018
Applies to:Business Intelligence Suite Enterprise Edition - Version 220.127.116.11.0  and later
Business Intelligence Server Enterprise Edition - Version 18.104.22.168.0  and later
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2013***
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:
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:
It does not occur if a standard filter is applied to the analysis. The incorrect SQL produced is, for example
 [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:
SAWITH0 AS (select sum(T42442.Revenue) as c1,
T42406.PER_NAME_YEAR as c2
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
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":
However, if you create a report filter like this, you get the correct values:
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!