OBIEE 10G - Cross conditional formatting on a pivot

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - About

OBIEE does not support cross column conditional formatting in Pivot view. For instance, it's not possible to set a red background of a column based on the value of an other column.

Below is a workaround to be able to perform this kind of formatting and the principal idea is to transform a table view as a pivot table.

Advertising

3 - Example Data Set

Example : I want to pivot this table from the SH schema :

Promo Category Channel Desc Amount Sold
internet Direct Sales 2,658,953
internet Internet 642,251
internet Partners 1,097,558
NO PROMOTION Direct Sales 115,549,450
NO PROMOTION Internet 26,154,910
NO PROMOTION Partners 51,682,188
post Tele Sales 554,853
TV Direct Sales 696,703
TV Internet 4,166

If the pivot column is the column “Channel Desc”, we have 5 distinct values :

Direct Sales
Internet
Partners
Tele Sales

We create therefore 6 columns :

  • one for the “Promo Category”
  • 4 formula columns for each distinct value of the “Channel Desc”
  • one for the cross conditional formatting
Advertising

4 - How to transform a table in a pivot

5 - Conditional Formatting

After having transformed our table in a pivot, it's finally possible to apply a formatting and to hide the conditional column.

6 - The Result

This method has a weak point is that you must know the number of distinct value in advance. For instance, if a new distinct value appear in the column “Channel Desc”, you must change your report.

To resolve this issue, you can also achieve a pivot statement with a stored procedure or wait for this functionality.

Below the pivot view :