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.