OBIEE 11g: Presentation Variable With Multi Value Selected Gives 'No Results' and Wrong Query Generated.

(Doc ID 1418868.1)

Last updated on JANUARY 23, 2017

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
Information in this document applies to any platform.
***Checked for relevance on 22-Oct-2013***

Symptoms

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}

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms