Saturday, March 14, 2015

OBIEE - Selecting columns dynamically - part3, better looking prompts

Sorry for the long delay, I was busy performing services in exchange for currency (mostly interesting services).
In the previous post I demonstrated how to use Prompts with column names to select column dynamically. In this post I will do the same but with User Friendly values in Prompt.

The result from the previous post was the ability to select first column using prompt, including it's header and selecting the second measure, based on the prompt (if the first column is Year, we wanted to see Discount Amount, else Billed Quantity):

There are 3 UI problems in that solution:
  1. Unfriendly column names.
  2. The selected column header.
  3. The second measure header.
The faithful readers of this blog might be guessing I will use here the method described in the post OBIEE Prompts - 2 prompts on same column - Part 3, of hidden prompts. 

 The solution:

I will create a variable prompt with user friendly values (Year, Product Type, Department). Its value will be placed in a presentation variable that will be in the header of the first column and used to update 3 other presentation variables:
1. The real column function.
2. The measure name. 
3. The second measure function.

If you insist you can do 1 and 3 at the analysis level.

Step1, Create the user friendly prompt:

I created a Variable Prompt, lets name it P_col1_header and default it to be Year. I prefer the Radio Buttons UI.

This is the user friendly Prompt:

Unfortunately, there is no hidden line option in the same prompt (Yes, I know, I should fill an enhancement request). So we need another Variable Prompt. This one will be hidden.

Step 2, The Hidden Prompt:
I created another Dashboard Prompt with 3 rows of Variable Prompts. Since I plan to hide it, I don't care how it looks.It will update 3 Presentation Variables:
  • P_Col1
  • P_Col2
  • P_col2_header

They all look the same: Presentation Variable, Text Type with Default Selection "SQL Result". The only difference is the SQL.

P_Col1 (returns the dimension column):
SELECT CASE '@{P_col1_header}{Year}'
WHEN 'Year' THEN '"Time"."Per Name Year"'
WHEN 'Product Type' THEN '"Products"."Product Type"'
ELSE '"Offices"."Department"' END saw_0 
FROM "Sample Sales Lite"

P_Col2: (returns the measure column) :
SELECT CASE WHEN '@{P_col1_header}{Year}'='Year' 
THEN '"Base Facts"."Discount Amount"' 
ELSE '"Base Facts"."Billed Quantity"'  END saw_0 
FROM "Sample Sales Lite"

P_col2_header (the measure header):
SELECT CASE WHEN '@{P_col1_header}{Year}'='Year' 
THEN 'Discount Amount' 
ELSE 'Billed Quantity'  END saw_0 
FROM "Sample Sales Lite"

All is left is to hide the second dashboard Prompt and modify the Analysis.

To hide the Prompt, Edit the dashboard and select Dashboard Properties in the little menu:

Next place the cursor on the relevant dashboard page and press on the filters and prompts icon.

Now add the hidden Prompt there:

Last step is to modify the analysis. I used defaults for the presentation variables, just to make the development easier.
So the first column now has:
Header: @{P_col1_header}{Year}
Formula: @{P_Col1}{"Time"."Per Name Year"}

The last column:
Header: @{P_col2_header}{Discount Amount}
Formula: @{P_Col2}{"Base Facts"."Discount Amount"}

and the other 2 are regular: "Orders"."Order Type" and "Base Facts"."Revenue".

Now I have a dashboard:

I select Product Type, press Apply and... NOTHING. Nothing changed! Why? Because, as I mentioned in that post (OBIEE Prompts - 2 prompts on same column - Part 3), the analysis doesn't "know" that the prompt changed. The fact that the variable is in the header of the first column is not enough. We have to add a column with formula '@{P_col1_header}' and set it as hidden in the Column Properties.  

Now everything works fine:

I'm happy,  but the customer has new ideas: "you know, sometimes I don't want the first column at all". Can you add this option as well?

This will be cover in the next post: OBIEE - Selecting columns dynamically - part4, working with none.  


  1. Hi Have a requirement like, need to create "region"dashboard prompt based on the user login.
    Forex: USER1 USER2
    if user1 login need to show only user1 related region list
    if user2 login in prompt user2 related list of regions
    can anyone help me to solve

  2. Prabhu, assuming it's based on login, you should use Session variables in the repository. Using initialization block update for each user his relevant regions in the variable and then use the session variable as part of the security or as a where condition in the relevant data source/sources of the business layer.
    It's covered well in blogs and documentation.

    1. Thanks u Dahav..
      But still small issue.. for Ex "Ram" belongs to east and west..
      when i am sign with ram a/c i am able to see only east

  3. I have a requirement year product customer name like this
    Year 2001,2002
    When I am selecting 2001 it show one report 2002 show another report by using presentation variable please tell me how ? Problem I am phasing adding dashboard condition? How to add that ?

  4. Hi,

    I have one requirement, in which using different filter values, different column would get select in report(analysis) for Example, if filter value is Month then it should hit monthly table for fetching that column if Day then daily table.


  5. This comment has been removed by the author.

  6. Very useful post.
    THANK YOU, Toda Rabah!