Tuesday, August 13, 2013

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.). 1This 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 2 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:

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

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

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.

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

12 comments:

After spending $$$$$$ on a "Cutting Edge" BI Suite...I wonder why such usability issues have not been addressed. Why does one need to "hack" using html/javascript or spend enormous amounts of time trying to find "workaround" to seemingly simple usecases?

I agree with you completely, If we are able to set the visual style of the calculated member heading, why not the data itself as well? Spending time doing these types of things is not value-add. But is is necessary, as presentation is as important as the data itself.

On the other hand the ability to "hack" to expand the capabilities of the tool using java / css / html is completely value-add.

I'm trying this out but it's not working for my total/grand total percent change. The conditional formatting doesn't work on the total. I'm probably missing something obvious but I really don't find any of this very obvious.

I see what you are saying. You're not missing anything obvious, it appears the grand total row ALWAYS takes the data format form of the base fact column, and there is no way to override this. I tried editing the XML to force the conditional format rule into the grand total column, and though I can get the UI to accept the edited XML, it throws an error when i try to run the report.

I will continue to work with it, but as of now the lack of work around for the grand total row is still a considerable limitation.

The measure column references the "trigger" column via a column reference expression in the XML.

I suspect when you exclude the trigger column the measure column can no longer "find" the trigger column via reference.

Leave the trigger column as a hidden dimension in your pivot table if possible. If that is not possible then you may have to edit the XML to get the measure column to format off of a "hidden" SQL expression. I wee see if I can duplicate this behavior in my test environment and post up steps to resolve.

I've achieved the same result by directly testing the base fact column (the year in your example) for "IS NULL". If you replace the total line with another calculated item that sums the row categories, the conditional format will work.Jerry C.

Jerry, I like the IS NULL solution. It prevents the need to create the calculated column. Can you point me in a direction to know how to do the calculated item that sums the rows instead of using the pivot table grand total?