Measure Precision Customization

ClearPeaks Video Tutorial

video-1


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”.

Untitled-1

Image 1: Create a Dashboard Prompt to set the chosen Measure Precision


2. Modify the measures formula

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.

Table 1

The formula should look as follows:

Courrier Table

Untitled-2

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:

Untitled-3

Image 3: Testing the results in a dashboard

Untitled-4

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.

Untitled-5

Image 5: Setting the chosen measure precision as the default value

Sergi G
sergi.guinon@clearpeaks.com