There are times we want to create a presentation variable that should not be seen by end-users and depends on other variables. We want it declared implicitly and not explicitly as most presentation variable.
What is my case?
In the OBIEE demos there are samples of presentation variable containing a column name. For example "Products"."Product Type" or "Offices"."Department". Next that presentation variable can be used for more general cases than column selectors.
My customers didn't like it and preferred something like 'P Type' and 'Dept' to be selected.
Lets start with a regular presentation variable p1, that has 2 static text values:
Next a second Dashboard Prompt is created with a second presentation variable, depending on the first. The SQL Result of Default Selection is:
SELECT case when '@{p1}{P Type}'='P Type' then '"Products"."Product Type"' else '"Offices"."Department"' end saw_0 FROM "Sample Sales Lite"
The second presentation variable, actually return the value I want, but I don't want the user to see it, or be aware of it. So I'll have to hide it.
There are few options to do it.
1. Place the dashboard prompt in a section with condition set to false.
2. Put it as in the dashboard properties variables. That is what we will do:
Edit the dashboard:
go to dashboard properties:
there in 'Filters and Variables' add the second dashboard prompt:
Now, create a new analysis with a column @{p2} (or any function that uses it).
Unfortunately, this is not sufficient.
The value of the variable p2 is updated implicitly. There is no action on it. The analysis needs to "know" something was updated. So we have to add variable p1, that our p2 based on, to the analysis, so it will be refreshed when p1 value changes.
We will create a new column in the analysis, for example the formula '@{p1}'.
Of course we should hide it. (Column Properties, hide).
Now when I select 'P Type' in the prompt, I see the "Product Type" values:
And the 'Dept' changes the column to "Department"
SELECT case when '@{p1}{P Type}'='P Type' then '"Products"."Product Type"' else '"Offices"."Department"' end saw_0 FROM "Sample Sales Lite"
There are few options to do it.
1. Place the dashboard prompt in a section with condition set to false.
2. Put it as in the dashboard properties variables. That is what we will do:
Edit the dashboard:
go to dashboard properties:
there in 'Filters and Variables' add the second dashboard prompt:
Now, create a new analysis with a column @{p2} (or any function that uses it).
Unfortunately, this is not sufficient.
The value of the variable p2 is updated implicitly. There is no action on it. The analysis needs to "know" something was updated. So we have to add variable p1, that our p2 based on, to the analysis, so it will be refreshed when p1 value changes.
We will create a new column in the analysis, for example the formula '@{p1}'.
Of course we should hide it. (Column Properties, hide).
Now when I select 'P Type' in the prompt, I see the "Product Type" values:
And the 'Dept' changes the column to "Department"
This helped me a lot with a similar request. Thank you for posting this.
ReplyDeleteAs an alternative to the hidden column you can use different syntax for refering to the second variable which depends on the first. Double @ sign forces the second variable to refresh following the change of the first one e.g. @{@{p1}}. This syntax does not work in the analysis itself but works fine once on the dashboard.
ReplyDeleteThanks, unfortunately it didn't work in the dashboard as well (syntax invalid error). Pity, could be a nice option.
DeleteIt's seem to be similar to my problem
ReplyDeleteI'd like de define a second date prompt from a first prompt.
For example :
p1 = 2015-09-01 (define by user)
p2 = p1 - 30 days
Have you an idea ?
If you want it for specific analysis, all you need is to add a calculation of TIMESTAMPADD(SQL_TSI_DAY, -30, @{p1}). Else use a second variable. You might want to read http://obieeil.blogspot.co.il/2014/02/obiee-prompts-last-year-last-month-and.html or http://www.rittmanmead.com/2014/12/timestamps-and-presentation-variables/ to get some ideas.
DeleteThanks Boris for your answer and your links. I'll try it soon !
DeleteBrilliant
ReplyDeleteVery Good article. I used this same funda in my project. Thank you boss for sharing.
ReplyDelete