3 way switch in OBIEE 11g

Tweet

While we all know about using conditions on sections in a dashboard, and while we all have used presentation variables to show or hide a report using these conditions, this blog talks about a process to use conditions on sections to implement a 3 way decision rather than just a boolean (true/false) decision.


Lets say that we have to show one section based on one value of a presentation variable, another based on another value and none of the sections based on a 3rd value in the presentation variable.

3 - way switch in OBIEE dashboards is tricky because the condition on the sections in the dashboards rely on the number of rows and not the values in them. So we have to devise a way to produce different number of rows for different values of the presentation variable in order to implement a 3 way switch.

We will see the implementation of this use case in this blog.

We will show either Whale curve or Date range scale graph or neither of these 2 based on the values of a presentation variable. Let me show you how my presentation variable looks

I create an analysis with one column in it and I put the following formula in the column. 

Case when '@{pv_switch}' ='Whale curve' then "Offices"."D3  Organization" when '@{pv_switch}' ='Date range scale graph' then '1' end

Replace  Offices"."D3  Organization" with a column from the smallest dimension in your model. Note that this dimensional column should have more than 1 distinct values.

I add the following filter to this analysis

('@{pv_switch}' IN ('Date range scale graph', 'Whale curve'))

Now let me explain the logic here. We are going to use this analysis for the conditions in the section in the dashboard. We are using the fact that OBIEE puts a distinct in every query by default. So If the user selects 'Whale curve', we display the column of the smallest dimension. This will ensure that our analysis returns more than 1 row. If the user selects 'Date range scale graph', we display '1'. OBIEE puts a distinct and hence the number of rows displayed in the analysis will just be one and the value in the row will be '1'.

We have also put a filter. This filter will ensure that no row is returned if anything apart from 'Whale curve' or 'Date range scale graph' is selected. Hence the row count will be 0 in this case.

Now the only thing left is to put the 2 analysis in different sections on the dashboard and then put the conditions on top of them.

My conditions on the section that contains the Whale cure looks like the following.

Note that I mark the condition as > 1 because my column formula is such that we will get more than 1 row when the user selects the 'Whale curve' radio button.

Similarly, I put the following condition on the section that contains Date range scale graph

Now I run the dashboard page.

Till next time ..