Friday, February 1, 2013

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

6 comments:

  1. Hey did you find the fix for this ? I have a very similar complex report request.

    ReplyDelete
  2. this is not working for me. I fyou have solution please post me. muralik.dw@gmail.com

    ReplyDelete
  3. Agreed !!! very helpful ...

    I changed the multi select prompt, @{pv_region}, Default selection to "None" and edited the filter to

    FILTER("HR Facts"."Headcount" USING "Location"."Region Name" LIKE '@{pv_region}%' or "Location"."Region Name" IN (@{pv_region}['@'])

    This allows for a default unfiltered Headcount that can be filtered by the selected values from the prompt.

    ReplyDelete
  4. Hello,
    Do you also know how to solve the issue that appears when you have a prompt that has "All columns", in that case there is no presentation variable beeing passed to the analysis, which makes the filter not to work. Do you have any solution for this?

    In my situation I am trying to filter a specific measure, so this code (1) works in every situation except from when a user press "All columns" or if the user leaves the prompt empty. To solve that issue I can add a code (2), but I have no idea how to combine them. Any help is much appreciated!

    Code (1): ("D Organisation"."Aktuell avdelning" IN (@{avdelning}['@']{'NFC VB'}))
    Code (2): ("D Organisation"."Aktuell avdelning" LIKE '@{avdelning}%')

    ReplyDelete
    Replies
    1. Found the solution

      Code: "Location"."Region Name" IN (@{PV}['@']{''}) OR '@{PV}{}' = ''

      This gives you the option to select one, multiple or no values(all column values) in the prompt

      Delete