Obiee Cross conditional formatting on a pivot

OBIEE does not support cross column conditional formatting in Pivot Tables.
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 in a pivot table.

What is a pivot table ?

A pivot table is just the same table where :

• the pivot column has been suppressed
• for each distinct value of the pivot column, a column with a conditional sum is created.

Example :
If 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

And the pivot table will result of this sql statement :

SELECT
   "Promo Category",
   sum(case "Channel Desc" = 'Direct Sales' then Amount Sold else 0 end) "Direct Sales",
   sum(case "Channel Desc" = 'Internet' then Amount Sold else 0 end) "Internet",
   sum(case "Channel Desc" = 'Partners' then Amount Sold else 0 end) "Partners",
   sum(case "Channel Desc" = 'Tele Sales' then Amount Sold else 0 end) "Tele Sales"
FROM
   FactTable,
   DimensionTable,
   ...
GROUP BY
   "Promo Category";

Transformation of a table in a pivot table

For the query above, we need 6 column :

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

In each formula column and for each distinct value of “Channel Desc”, enter a filter formula.

Below an example for the value 'Direct Sales' :

FILTER("Sales Facts"."Amount Sold" USING (Channels."Channel Desc" = 'Direct Sales'))

And here for the value “Internet” :

Conditional Formatting

After completing the last three columns, it's finally possible to apply a formatting and to hide the conditional column.

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 :