29 Sep 2010 Retrieving revenue average of last N months

An effective way of measuring any business process is to compare its performance during two periods of time. This comparison reveals the effectiveness of the decisions made during the second period. Therefore the results of that comparison are key to justify and support the decisions made.

This article describes the steps needed to build a dashboard showing the average order amount for the last months. The actual number of months will be chosen by the user when refreshing the dashboard.

This solution requires the following steps:

1. Add new logical and presentation columns to the Oracle BI repository
2. Create a dashboard prompt
3. Create a request
4. Embed the dashboard prompt and the request in a dashboard

Let’s take a closer look to each of them.

1. Add new logical and presentation columns to the Oracle BI repository

First of all we need to create a new logical column for each month for which we want to obtain the average. In this example, we want to get the average up to a year in the past, so we need to create 11 average month columns.

New logical columns need to be created

To do that, we need to create in the Business Model & Mapping layer a duplicate of the “Fact – Sales”.“Revenue” logical column for each month for which we want to get the average.

The new logical columns should be defined as calculations using logical columns (click on “Use existing logical columns as the source” in the General tab) and should have the following formula:

( AGO(Revenue Measure, Month, <Nº of months> – 1) +

AGO(Revenue Measure, Month, <Nº of months> – 2) +

… +

AGO(Revenue Measure, Month, 1) +

<Revenue selected month> )

/ <Nº of months>

Notice that the AGO part will be repeated depending on the number of months. Find next the formula to obtain the average for the last 6 months:

Formula for the calculated measure “Avg Revenue 6 Last Months”

Once all the new logical columns have been defined, we need to move them to the presentation layer:

New calculated measures need to be made available in the subject area

At this point, we can check that the new presentation columns are working properly and return the expected values. We can build a simple request as this one:

Checking the results of average revenue for the last months

2. Create a dashboard prompt

The users will choose the base month and number of last months for which to get the average based on their selection in a dashboard prompt.

Create a dashboard prompt with two columns: “Month” and “Number of months”.

“Month” will be chosen from a drop down list.

“Number of months” will be using the control “Edit Box” so that the users can type the number of months. We can choose the Year, for instance, to generate this column as it has a low cardinality, and will type in a label to avoid displaying the label “Year”. The default will be set in this case to 3 months. The property “Set Variable” needs to be set to “Presentation Variable” with the variable name set to “months”.

Prompt definition showing the setting of the Presentation Variable

The prompt will show like this:

Prompt preview

When the users type in a value in the prompt and click on the Go button, the variable “months” will be automatically set.

3. Create a request

Create a request with the required columns in the criteria tab. Then add a new column with the following formula:

CASE  @{months}{3} WHEN 1 THEN “Fact Sales”.Revenue WHEN 2 THEN  “Fact Sales Month”.”Avg Revenue 2 Last Month” WHEN 3 THEN “Fact Sales Month”.”Avg Revenue 3 Last Month” WHEN 4 THEN “Fact Sales Month”.”Avg Revenue 4 Last Month” WHEN 5 THEN   “Fact Sales Month”.”Avg Revenue 5 Last Month” WHEN 6 … END

This column will always first check the value of the presentation variable “months” and, depending on its value (default is 3), will show the column corresponding to the required last number of months average.

Finally we need to create a filter specifying that the month is prompted. This will set the base month for the report.

Report criteria including columns and filters

4. Embed the dashboard prompt and the request in a dashboard

Add the new dashboard prompt and request into a dashboard page.

The users will be able to choose the base month and the number of months to be included in the average.

Report execution sample

When refreshing the report, it will retrieve data taking the value in “Month” in the dashboard prompt as the base month, and the value “Number of Months” as the number of months for which to calculate the average.