Hello
Taking the reference of this OTN Post I thought of playing around prompts and measure columns.
The exercise is based on Sample Sales Lite SA ( XML Based SA )
Environment- OBIEE 11.1.1.6.0 ( Windows 7 -32 bit /Tested on IE 8 )
SA- Sample Sales Lite
Create an analysis of 3 columns namely
1> "Time"."Per Name Year"
2>"Base Facts"."Revenue"
3>"Base Facts"."Revenue"
I would rename the columns ( no. 2 & 3 ) as shown in the snapshot below.
Column formula of column no 2
Column formula of column no 3 ( You can have complex calculation - In the given example I have used a very simple one )
Save the report. [R1]
Go back to home page and once again navigate to Sample Sales Lite SA.Take 2 to 3 measures for example in the analysis and capture their names from the respective column formulae.We would need this while creating a dashboard prompt. Look at the below snapshot.
"Base Facts"."Revenue"
"Base Facts"."Billed Quantity"
"Base Facts"."Discount Amount"
Now go to dashboard prompt editor.Select the option Variable Prompt.Add the details as shown in the below snapshot.
Save the prompt and pull it on the dashboard with the report R1.
Select the first measure ( Revenue ) and hit apply.You will see the Revenue measure values getting populated in the second column while values of 3rd column would base upon the 2nd column that we select from the prompt.
You may find blogs on changing the column name dynamically.
I have to test this solution against an SA based on a relational database and perform multiple rounds of testing. Try it out - You may find some even more interesting facts. Good luck.
Thanks.
Taking the reference of this OTN Post I thought of playing around prompts and measure columns.
The exercise is based on Sample Sales Lite SA ( XML Based SA )
Environment- OBIEE 11.1.1.6.0 ( Windows 7 -32 bit /Tested on IE 8 )
SA- Sample Sales Lite
Create an analysis of 3 columns namely
1> "Time"."Per Name Year"
2>"Base Facts"."Revenue"
3>"Base Facts"."Revenue"
I would rename the columns ( no. 2 & 3 ) as shown in the snapshot below.
Column formula of column no 2
Column formula of column no 3 ( You can have complex calculation - In the given example I have used a very simple one )
Save the report. [R1]
Go back to home page and once again navigate to Sample Sales Lite SA.Take 2 to 3 measures for example in the analysis and capture their names from the respective column formulae.We would need this while creating a dashboard prompt. Look at the below snapshot.
"Base Facts"."Revenue"
"Base Facts"."Billed Quantity"
"Base Facts"."Discount Amount"
Now go to dashboard prompt editor.Select the option Variable Prompt.Add the details as shown in the below snapshot.
Save the prompt and pull it on the dashboard with the report R1.
Select the first measure ( Revenue ) and hit apply.You will see the Revenue measure values getting populated in the second column while values of 3rd column would base upon the 2nd column that we select from the prompt.
You may find blogs on changing the column name dynamically.
I have to test this solution against an SA based on a relational database and perform multiple rounds of testing. Try it out - You may find some even more interesting facts. Good luck.
Thanks.
2 comments:
Hey thanks for the solution.
I want to display the column name as per the measure selected in the title or static text. Using the presentation variable name in the title or static text gives me "table.column name" but actually I want to display only column name.
Any idea?
Thanks
I am unable to reproduce the scenario at this moment but here are some pointers -
1) Presentation variable @A in the given example holds the custom values. "Table Name"."Column Name"
2) If you want only column - change the custom value to only Column name.
3)In the original report wherever we are using @A in the column formuale, you can try writing case statements such as [ case when @A='Column name' then "Table Name"."Column name"]
4) Another approach is trying ( Slice and substr javascript functions on the presentation variable value in narrative view.
Post a Comment