Everything Oracle

Introduction

Let’s suppose we have a table that shows sales volume by calendar year and calendar month, and let’s suppose we want to colour code sales volume cells within the table according to the following criteria: if the current sales volume is less than 20% of what it was a year ago, then the cell should be coloured dark blue; if less than 20% of what it was a month ago, then the cell should be coloured blue-green; and if the decrease in sales volume three months hence is less than 20%, then the cell should be coloured light blue. In other words, we want a table that looks like this:

Formatted Report

Defining Selector Columns

Our starting point is a table that shows sales volume by calendar year and calendar month (we’ve added column “Calendar Month Number” to order the months correctly within years):

Unformatted Report

We want to use the “Conditional Format” tab in the “Column Properties” window to specify when a cell should be coloured. However, the list of available conditions consists of only simple predicates, such as “<column> is less than <value>”. So we need to define a set of selector columns that can be used within these constraints (the selector columns can be hidden in the report):

Last Month % Increase

Last Year % Increase

Next Quarter % Increase

Calculating Lags from Running Sums

Two of our selector columns require the calculation of lagged sales volumes. The list of functions available within the “Column Formula” window doesn’t include “lag”, but we can easily compute it from a difference in running sums. The “msum” function, msum( <sales volume>, <n> ), will give us the sum of sales for the last “n” months (if “n” equals one we get the sales volume for the current month). So, if lag( <sales volume>, <n> ) equals the sales volume “n” months ago, then

lag( <sales volume>, <n> ) =
msum( <sales volume>, <n> + 1 ) –
msum( <sales volume>, <n> )

The percentage change in sales volume from “n” months ago to the current month equals:

100 * ( <current sales> – <months ago sales> ) /
<months ago sales>

which equals:

100 * ( <sales volume> –
      msum( <sales volume>, <n> + 1 ) +
      msum( <sales volume>, <n> )
      ) /
      ( msum( <sales volume>, <n> + 1 ) –
      msum( <sales volume>, <n> ) )

For a lag of one month, this formula simplifies to:

100 * ( 2 * <sales volume> –
      msum( <sales volume>, 2 )
      ) /
      ( msum( <sales volume>, 2 ) –
      <sales volume> )

So our column formula for the “Last Month % Increase” equals:

Last Month % Increase

and that for the “Last Year % Increase” equals:

Last Year % Increase

Note that there is an added complexity in the calculation that must be taken into account. For the first 11 months of the year both function calls to “msum” point to non-existent rows and the formula correctly returns null. But for the 12th month, one call finds a row and the other doesn’t, which leads to an incorrect non-null value for the formula. To circumvent this problem we need to add a case statement so that the formula is only applied for rows greater than 12 (using the “rcount” running aggregate function).

Calculating Leads with Evaluate

Calculating the “Next Quarter % Increase” is different. The running aggregates only look towards the past (a negative value of the “msum” index is not allowed). As OBIEE does not support any forward-looking windowing functions, we need another method of finding the sales volume one quarter into the future. The Oracle database has, of course, a very extensive set of analytic functions, including one called “lead”. So we can use the OBIEE “evaluate” function to function-ship our “lead” function directly to the Oracle database (a restricted form of DBR, if you like). To calculate the sales volume “n” months ahead we need to call:

evaluate( 'lead( %1, <n> ) over ( order by %2 )' as double,
<sales volume>, <ordering column> )

Converting this to a percentage change gives the formula:

Next Quarter % Increase

We’ve added “Calendar Month Desc” to the request as a hidden column to provide a single sort column for the evaluate function (we don’t have the same problem here with trailing rows – rows to which the formula doesn’t apply will have null values).

Checking Our Sums

Hiding the “Calendar Month Number” and “Calendar Month Desc” columns in the request we get:

Report With Selector Columns

Note that the “Last Month % Increase” starts in February and that the “Last Year % Increase” starts in January of the following year as required.

Now it’s time to get out the calculator and spot check a few values to ensure that we haven’t got our sums wrong!

Adding the Conditions

Finally, it’s simply a case of adding the conditions for our three selector columns and choosing the corresponding formatting:

Conditional Formatting

Running our query gives:

Formatted Report With Selector Columns

or with the selector columns hidden:

Formatted Report

Everything Oracle

Home

Everything Oracle