OBIEE 11g: Presentation Variable initialization in a Multi-Select Prompt

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