OBIEE – Report Selection Prompt IGATE Interview

I was asked yesterday how to create View Selector type functionality that will allow the user to select between reports, rather than just views of the same report. I have a feeling this information must already be available, but I said that I’d provide instructions on how to achieve this and may as well add it to my blog. 

I will go through the steps with screenshots. We want to create 2 reports, Option 1 and Option 2; we need to create a third report that will return either true or false (results or no results). Both reports, Option 1 and Option 2 will be placed on the Dashboard, both in their own Sections; and both sections with Guided Navigation making use of the conditional request. We create a Dashboard prompt giving the options of Option 1 and Option 2; and add a filter to the conditional report so that it filters by the value selected in the prompt. Essentially if Option 1 is selected in the prompt then Report 1 will be displayed; and if Option 2 is selected then Report 2 will be displayed. Reading through this sounds very complex, but it isn’t really – if you haven’t understood then follow the steps below. 

Create the Dashboard Prompt 

The Dashboard Prompt will display a list of the Reports available; we can enter anything that we like for each option.  To achieve this we must use SQL to generate the Show Values.  OBI forces us to select an existing column to populate the prompt, but we get around this by using the expresssion CASE WHEN 1=2; the expression will never result to true and will always show the result of the else statement, our option.  We create a SQL statement for each option we would like in the Prompt and union the statements together (in the order that we would like them to appear). 

Note: The Column used should match the values you would like to generate 

Prompt Show SQL

For the SQL above, obviously change the references to “column”, “table” and “Business Model”.  Once happy with your SQL I would usually select the Preview Button to check my code. 

Test Prompt Show SQL

For this functionality you would usually not want an ‘All Choices’ option; uncheck its inclusion.  We should also choose to default to a Specific Value to restrict the list to only valid options.  Click on the elipses button and type your preferred default value from the last - no need for quotes, just the text itself.  I would usually verify that the default value is working by viewing the preview again. 

Updated Prompt

The only remaining task is to populate a Presentation Variable using the Set Presentation Variable Drop Down.  I have created a variable pVar_ViewOption.  You will also probably want to relabel the prompt to something more meaningful and then you can save it. 

Set Presentation Variable

Create a Conditional Report 

We need to create a conditional report; the report will filter by the presentation variable created.  We will design the report to return values when one Prompt option is selected and return no values otherwise.  Essentially the report will return true or false, based upon the option selected in the prompt. 

We need only a single column in the report; which should be the column referred to by the prompt.  Similar to the expression used in the prompt, we use a CASE statement to always return the relevant option; in this case the preffered option.

Conditional Column Expression

We now add the filter to the conditional report to filter this column by the Presentation Variable.   If the option displayed by the column is selected then the report returns results; otherwise it returns no results.

Conditional Filter

The Conditional Report is complete.  We also need to create the reports to be displayed with each option.  For this example I’ve created one for each of the 2 options.  So thats 3 reports in total and a prompt.  The screenshot below shows these object in My Folder.

My Folder

Configure the condition on a Dashboard

Create a section for the prompt and another for each report. 

Dashboard Layout

We need to use Guided Navigation on each of the Report Sections; we will only show each section based upon the results returned by the Conditional Request we have created.  The Guided Navigation setup for Report Option 1 is given below; notice that it returns a report on the condition the conditional report returns results (ie the prompt selection is our preferred option).

Report Option 1 Guided Navigation

Report 1 - Conditional Navigation

We need to set Guided Navigation for the second report to show when our conditional report returns no results, as below.

Report 2 - Conditional Navigation

And thats the process complete.  When we save our Dashboard and view the page our prompt is shown and defaults to the preferred option; the guided navigation kicks in to display the first report and not the second.

Test One

And then when we select the other option in our prompt the guided navigation kicks in to display only Report Option 2 section, and not our other Report Section.

Report Two Test

It is worth noting that behind the scenes all three reports will be ran by the BI Server. I wouldn’t be concerned unless the approach causes too much load on the server.  However, this approach should not be used to improve performance; it doesn’t work like that unfortunately.