OBIEE MasterChef: January 2016

3 Way Switch Obiee

Scenario:

The requirement is to create a prompt that displays a section based on the user selection (out of 3 possible options). This 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.

In summary a few elements are required:

1. A Dashboard Prompt which sets a Presentation Variable

2. An interim Analysis which returns a single row for one of the possible user choices.

3. A Condition which evaluates to true when the Analysis in (2) returns a row.

4. A Dashboard with the Condition applied to the Sections which will be shown / hidden.

Solution

Create Prompt

· Create a Variable prompt, and set a presentation variable Report_flag,

· Add the custom values that will be used to prompt the user

Create Analysis

· Create an analysis with one column in it and put the following formula in the column

CASE WHEN '@{Report_flag}' = 'Funding' THEN "TIME_DIM"."FISCAL_YEAR" WHEN '@{Report_flag}' = 'Trading' THEN 1 END

Note: Use a column from the smallest dimension in your model. Note that this dimensional column should have more than 1 distinct value. In the case we used "TIME_DIM"."FISCAL_YEAR"

· add the following filter to this analysis

'@{Report_flag}' IN ('Funding', 'Trading')

Logic:

We 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 'Funding’, we display the column of the smallest dimension. This will ensure that our analysis returns more than 1 row. If the user selects 'Trading', 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'.

The filter will ensure that no row is returned if anything apart from 'Funding’ or 'Trading’ is selected. Hence the row count will be 0 in this case, which will be the default condition which will allow the last option “AC Balance” to be displayed.