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

Smart development tip: OBIEE 11g- Exclude new columns from existing views

Reports keep on evolving.  Users keep demanding new addition to views or information to existing reports. Now when you add new columns to an existing report, by default it ends up in all your existing views. You have to manually edit each view to restore it to how it was earlier. It is really annoying when you have multiple views in multiple reports to modify.

To work smart in this situation, before adding any column to the existing report, open your existing report in Edit mode and click on Analysis properties.

Go to data tab and for Display of Columns Added in Criteria Tab select Exclude from existing views, but display in new views. Click OK and save your report.

After this property is set, your existing views will remain unchanged as the new column added will be in the Excluded section of the view.

Hope it helps.

Cheers!

Deepak