Sunday, June 19, 2016

OBIEE 11g : Using multiple value for a presentation variable in Criteria

We have a multi select prompt which is initializing a presentation variable (which is a string). When we use this presentation variable in a column formula using a FILTER and IN operator it results in no Result.

FILTER("HR Facts"."Headcounts" USING "Location"."Region Name" IN ('@{pv_region}{West}'))



When ever multiple values are selected in the prompt the SQL being generated by OBIEE Server results as below-
"Location"."Region Name" IN ('East Africa, New Delhi, Central India')


Which results in No Result since there is no region like 'East Africa, New Delhi, Central India' exists in the DB.  But we want OBIEE Server to generate SQL as below

"Location"."Region Name" IN ('East Africa','New Delhi','Central India')

To achieve it use below syntax for Presentation Variable :

FILTER("HR Facts"."Headcounts" USING "Location"."Region Name" IN (@{pv_region}['@']{'West '}))


No comments:

Post a Comment