My Environment: 11.1.1.6.2. BP1
We have a multi select prompt which is initializing
a presentation variable (it’s a string). We wanted to use this presentation
variable in a column formula using a FILTER and IN operator as below.
FILTER("HR Facts"."Headcount"
USING "Location"."Region Name" IN ('@{pv_region}{East}'))
When multiple values are selected in the prompt the
SQL being generated by BI Server has the following WHERE clause-
"Location"."Region Name" IN ('East,
West, Central')
as opposed to
"Location"."Region Name"
IN ('East', 'West', 'Central')
This results in ‘No Result View’ as no region like 'East,
West, Central' exists in the database. When a Region ID is used instead of the character
string it works fine. So, we knew this is related to syntax for strings. A little research help us get it right using the following syntax:
FILTER("HR Facts"."Headcount"
USING "Location"."Region Name" IN
(@{pv_region}['@']{'East'}))
Quick note: I have prefixed ‘pv_’ to my
presentation variable. This is one of the best practice or naming standard
which can be used.
Interview Question: Why would you want to use an
in-line FILTER in column formula? Wouldn’t using Region Name as ‘is prompted’ filter
do the same thing?
Well, it will in a simple report. My report’s
requirement is a little complex - in this report I have three Headcounts column, each
filtered based on different criteria or filter set. If I use an ‘is prompted’
filter, it will apply to the whole report.
Hope it helps.
Cheers!
Deepak
