Measure Precision Customization
JANUARY 21ST, 2011.ClearPeaks Video Tutorial
In any organisation, common information can be interesting to multiple users. However, the way this information wants to be seen by different users may differ substantially. Oracle BI supports multiple visualization options by offering a wide range of views. With them, a report can be displayed as a table, chart, pivot table, etc.
In some other cases, the chosen view may be the same, but the measures precision may be different depending on the user visualization of the information. For instance, the organisation's CEO would be interested in a figure representing millions, whereas the financial department would be interested in the same figure displayed in full value, including cents.
In this article we will see how we can create an easy solution in Oracle BI, allowing the user to customize the measures precision (real value, thousands or millions) in a dashboard.
Being able to visualize a measure in its full value, thousands or millions, can be achieved by having three different columns in the Oracle BI Repository and dynamically choosing one or another using a column selector view. However, if that approach wants to be used in a report with multiple measures, the amount of measures to be created in the Repository will grow considerably. On top of that, we will need to click many times and run many intermediate queries to get the final results.
1. Create a dashboard prompt to select the chosen measure precision
2. Modify the measures formula
In any organisation, common information can be interesting to multiple users. However, the way this information wants to be seen by different users may differ substantially. Oracle BI supports multiple visualization options by offering a wide range of views. With them, a report can be displayed as a table, chart, pivot table, etc.
In some other cases, the chosen view may be the same, but the measures precision may be different depending on the user visualization of the information. For instance, the organisation's CEO would be interested in a figure representing millions, whereas the financial department would be interested in the same figure displayed in full value, including cents.
In this article we will see how we can create an easy solution in Oracle BI, allowing the user to customize the measures precision (real value, thousands or millions) in a dashboard.
The Challenge
Being able to visualize a measure in its full value, thousands or millions, can be achieved by having three different columns in the Oracle BI Repository and dynamically choosing one or another using a column selector view. However, if that approach wants to be used in a report with multiple measures, the amount of measures to be created in the Repository will grow considerably. On top of that, we will need to click many times and run many intermediate queries to get the final results.
The Solution
The approach we will use is to:
· Create a dashboard prompt to select the chosen measure precision
· Modify the measures formula to be dependent on the precision chosen in the dashboard prompt
The approach we will use is to:1. Create a dashboard prompt to select the chosen measure precision
Create a dashboard prompt that assigns the measure precision to a Presentation Variable.
In this scenario we will show the following values in the drop down list for the prompt:
· Value (default)
· Thousands
· Millions
This value will set the Presentation Variable “Formatting”.
Image 1: Create a Dashboard Prompt to set the chosen Measure Precision
In order to achieve the proper format, we will divide the value by a number being derived from the Presentation Variable being set in the Dashboard Prompt.
The formula should look as follows:
Image 2: Modify the measure formula
Check the results of the query and save your request.
Testing the Results
Now it’s time to build a new dashboard combining the new Dashboard Prompt and the request we just created:
Image 3: Testing the results in a dashboard
Image 4: Testing the results in a dashboard (all options)
Bonus Tip
Finally, as a tip for business users mention that the prompt choice could be saved as a default option. This will prevent having to set the desired measure precision in the dashboard prompt every time we visit this Dashboard. To do this, click on the “Page Options” button and select “Save Current Selection” > “For me…”. Make sure you check the “Make this my default for this page” checkbox in order to make this the default value.
Image 5: Setting the chosen measure precision as the default value
Posts
