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 '}))
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