Dynamic table headings are not straight forward in Oracle BI EE. However, we can simulate it’s behavior. In this article we have presented a trick to do it. With this you will be able to increase the user satisfaction and thus the BI solution acceptance.
The challenge
It’s usual to get a requirement like this one from business users:
“I want to see the currency displayed in the column heading while removing it from the rows”
In other words, make the table heading dynamic. Unfortunately, Oracle BI EE does not come with such a feature: Column headings are static.
The solution
In this article we are proposing a trick to simulate dynamic column headings. It consists of:
- Modifying the original request criteria.
- Combine the first query with a second query containing the column headings.
- Edit the table view to adjust layout
- Create a Dashboard Prompt to select the currency
1. Modify the original request criteria
Let’s take the scenario of a report showing the Sales Revenue Amount in Dollars by District. The original query looks like this:
In order to modify the measure so that it retrieves the Sales Revenue Amount in other currencies we are going to modify the formula as follows:
The measure column is going to retrieve the value in the currency in the Presentation Variable “currency”.
Select any other column (we need a new column placeholder in criteria). Edit Column Formula and modify the headings as shown in the figure below. Type number 2 at the Field Formula and click on the Ok button. That’s going assign value 2 for the results query.
Move the new column to the first position so that sort is by default on the new column. After this, the criteria tab should look like this:
2. Combine the first query with a second query containing the column headings
Click on the button "Combine with similar Request" and select the same subject area you are using. Make sure the result Column is a combination of a Union of both criteria.
Select any column (we need a new column placeholder in criteria). Edit Column Formula and modify the headings as shown in the figure below. Type number 1 at the Field Formula and click on the Ok button. That’s going assign value 1 for the headings query.
Select the “Market”.”District” column (same as in the original request) and change the column formula as follows:
This needs to be done to ensure the second query uses at least one presentation column.
Select “Sales Measures”.”Dollars” column. Edit Column Formula and change the column formula as follows:
This is going to retrieve the value in the Presentation Variable “currency”. Its default value is set to 'EUR'.
3. Edit the table view to adjust layout
Display the results in a table view. The results should look like this:
Use conditional format in order to simulate the visuals of the heading. After that, the table should look like this:
Hide the Sort column:
Finally, modify the display to show results only.
Preview the results to discover that the new table looks like a normal table:
Save the request.
4. Create a Dashboard Prompt to select the currency
Create a new Dashboard Prompt including the currency code. Make sure that “Set Variable” is set to “Presentation Variable” and that you use the same name used in the request:
Save the dashboard prompt.
Create a new Dashboard Page including the new prompt and the request we just saved.
Click on “Save” and check the final result:
By changing the value in the Dashboard Prompt, the value in the column heading will change as well as the results shown as table rows.
Posts
