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.
Example Data Set
Example : I want to pivot this table from the SH schema :
|Promo Category||Channel Desc||Amount Sold|
|NO PROMOTION||Direct Sales||115,549,450|
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
How to transform a table in a pivot
All the details are here :
After having transformed our table in a pivot, it's finally possible to apply a formatting and to hide the conditional column.
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 :