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” :
{{:dat:obiee:presentation_service:obiee_filter_on_internet.jpg|}}
===== Conditional Formatting =====
After completing the last three columns, it’s finally possible to apply a formatting and to hide the conditional column.
{{:dat:obiee:presentation_service:obiee_conditionnal_formating.jpg|}}
{{:dat:obiee:presentation_service:obiee_hide_column.jpg|}}
===== The Result =====
{{:dat:obiee:presentation_service:obiee_pivot_table_cross_conditional_formatting.jpg|}}
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 :
{{:dat:obiee:presentation_service:obiee_pivot_to_format.jpg|}}
Tags: formatting, OBIEE, pivot
