31 May 2011 Switching Between Two Reports within an Oracle BI Dashboard
Dynamically changing the information displayed in a dashboard should be one major objective for the developers. This flexibility reduces the number of requests to be developed and increases the user experience.
A common way of achieving this flexibility is by using the Column Selector view. This view allows the user to select a subject area column dynamically and to get results based on that selection (e.g. Revenue by Year/Region/Product).
The Challenge
Unfortunately, using the Column Selector view has some limitation. An important one is that every time a new subject column is selected, a new query is executed to refresh the data. So in the case that we need to change multiple columns, we will be executing multiple intermediate useless queries.
In this blog article we will show a way of simulating the Column Selector view when we need to change multiple columns in a report.
The Scenario
The requirement is to be able to switch between two reports that differ only in some columns (in this case, a set of measures). The user does not want to have them in separate Dashboard Pages but in one single dashboard page, displaying one report or the other based on a “Measure Selector”.
Image 1: Business User’s requirement
The Solution
Here are the main steps to implement this solution:
- Create a Dashboard Prompt to set a Presentation Variable
- Create both reports separately adding a filter based on the Presentation Variable
- Add Navigation to the Dashboard Sections including each of the reports
1. Create a Dashboard Prompt to set a Presentation Variable
First of all we need to create a Dashboard Prompt that contains the two possible values for the new Presentation Variable. These values will be included in the drop down list and will be hardcoded using the “SQL Result” option in “Show” property of the Dashboard Prompt.
Then we will need to specify that this Dashboard Prompt is going to set a Presentation Variable. In this case, the Presentation Variable is called “Gross_Measure”.
Also let’s make sure we provide a meaningful name to the Dashboard Prompt column by specifying it in the “Label” property (e.g. “Measure”):
Image 2: Dashboard Prompt assigning a Presentation Variable
2. Create both reports separately adding a filter based on the Presentation Variable
Second, we are going to apply a filter condition in each report to force the report to return columns only when the Presentation Variable has a specific value.
To do that, select a measure and apply a filter based on a Presentation Variable:
Image 3: Create and Edit a Filter based on a “Presentation Variable”
Image 4: Modify the filter condition using “Edit Column Formula”
Fill in the “Column Formula” with the variable name:
Image 5: The Presentation should be ‘TL Year’ to pass the filter
As a result, this report only returns rows when the Presentation Variable equals ‘TL Year’.
Repeat the process for the other report, but this time fill in the “Column Formula” with the other value, that is ‘YTD’:
Image 6: The Presentation should be ‘YTD’ to pass the filter
3. Add Navigation to the Dashboard Sections including each of the reports
Once we have both reports filtered we need to modify the section’s properties for each report of the dashboard. The goal is to apply guided navigation to a specific Source Request. Each section is going to refer to a particular report.
The goal is to display the report only if it returns rows. In our case, it will return rows when the filter condition including the Presentation Variable “Gross_Measure” is satisfied.
To apply guided navigation, click on the Section’s Properties button > Guided Navigation:
Image 7: Selecting Section Guided Navigation
We need to specify a Source Request to create a conditional Dashboard Section. In our case, we will use the same report shown in the Section:
Image 8: Specify the source of the report
Check the bullet “if request return rows”.
The previous steps need to be done for both sections.
If we open the dashboard, we will see the message “Preparing Guided Navigation”. This is shown while both source requests (one for each conditional section) are being executed.
Image 9: Preparing Guided Navigation
After they have been executed, only one of them will be shown. This will depend on the value chosen in the Measure drop down box from the Dashboard Prompt:
Image 10: Dashboard when selecting Measure = ‘TL Year’
Image 11: Dashboard when selecting Measure = ‘YTD’
Note: Due to confidentiality agreement, some data and metadata have been removed from the images.
