Conditional Formatting of Calculated Items in OBIEE 11g

By Victor Fagundo

Calculated items in OBIEE Pivot tables can be very useful in certain reporting circumstances, either for ease of development, or to meet specific report requirements. While calculated items in OBIEE are easy, and flexible, they do have one important drawback: they take on the data and display formatting of the fact column they are calculated against.

The most common case is the calculation of a % change across a time dimension in financial reporting ( Year over Year, Quarter over Quarter, etc.). ¹   This type of calculation usually takes the form of a percent change calculation similar to below:

 (( $2 - $1 ) / $1) *100 

By default, if you perform this calculation against a numerical fact ( sales, customers) you will run into the problem of how to display the % change in the correct format, since the calculated % will want to take the form of the fact it is calculated against, as can be seen in the example ² below:

Figure 1 - Pivot Table

Figure 2 - Calculated item

Figure 3 - Results

Not very pretty at all.

As people searched for a work around to this problem 3 common solutions have arisen:

1. Use HTML formatting tricks to “hide” trigger text in the results, then conditionally format off those triggers. While inventive, as the comments note, this solution falls flat if the report is ever printed, as the PDF engine will pick up and display all of the hidden characters.
   
   
2. Convert the pivot table to a regular table with some complex column formulas. Very time consuming and cumbersome, would also not solve the requirement of showing the dimension values noted as noted in Footnote1.
   
   
3.  Convert the calculated result to text and manually add your formatting characters. I don’t think this actually works since the calculated fields won’t accept logical SQL functions, and this would be very cumbersome.

Now with 11g providing conditional formatting that allows you to override the default data format, this is possible via the following steps:

1. Add a column that is a COUNT DISTINCT on the dimension that you are calculating across ( in the displayed example, “Time T05 Per Name Year”. This column will serve as your “trigger” to apply your conditional formatting.
   
   

   

   Figure 4 - Column Formula

   
   
2. For each of your facts, apply a conditional format that is triggered when the above column value is zero. In the formatting, apply whatever visual and data formats you desire. In this example we will format the data as a percent, with one decimal place.
   
   

   

   Figure 5 - Condition

   
   
   

   

   Figure 6 - Format when condition is met

   
   
3. Exclude the “trigger” column from your pivot view. View your results and be satisfied:
   
   

   

   Figure 7 - Correct formatting of calculated item.

* Note that this would also allow you to apply visual formatting if you wanted to distinguish this row/column as a total.

Why it works

The use of conditional formatting that applies a data type as part of the format is a straightforward leap of logic, but what to use as the trigger? Most people will try to use the dimension they have setup the calculation in. However, if you try to use the text description given to the calculated item you will find that the condition is never applied:

Figure 8 - Condition on dimension

Figure 9 - Condition never met, format never applied

If you try to setup a filter that is true when the dimension is not in reasonable range of values ( in this example we try to format off all years not in the 2000s ) you will find that your calculated item is skipped as well (this has the added vulnerability of being very explicit):

Figure 10 - Condition on dimension values

Figure 11- Condition never met, format never applied

The reason for all this is that the calculated item “borrows” EACH of the dimension values it operates against. Hence, no matter how inventive your filter is, as long as you are trying to somehow separate the calculated member away from the members it is operating on, you will never succeed. This member “borrowing” is apparent if you add the dimension it operates against to the query a 2nd time, and look at the table view.

Figure 12 - Calculated item "borrows" members

But since the “member value” given to the calculated item does not actually exist in the dimension, if you try to perform a count distinct against it, you will always get zero.

Figure 13 - Count distinct against dimension

There is your difference; there is your “trigger.” The rest is basic formatting.

1: You might suggest that this requirement is better served using column(s) with time series calculations, and you might be right. However, more often than not the user will want to SEE the time periods being compared ( 2012 vs 2011, or 08/07/2012 vs 08/07/2011). When using facts with time series calculations you will only be able to show “this year” vs “last year” since the column heading of the time series calculated fact will always be static. In these cases you will need to use the base fact and a time dimension, along with the solution provided here.

2: All screen shots, and examples used in this post are performed in Sample App V305. An XML of the final correctly formatted report can be downloaded here.