zaterdag 30 januari 2010

Oracle BI EE - Conditional Formatting in Pivot Tables

In tables and pivot tables, conditional formatting helps direct attention to a data element if it meets a certain condition. For example, you can show below-quota sales figures in a certain color, or display an image such as a trophy next to the name of each salesperson who exceeds quota by a certain percent.
You do this by selecting one or more columns in the request to use, specifying the condition to meet, and then making selections for font, cell, border, and style sheet options to apply when the condition is met. The conditional formats can include colors, fonts, images, and so on, for the data and for the table cell that contains the data.
You can add multiple conditions so that the data and the table cell are displayed in one of several formats, based upon the value of the data. For example, below-quota sales can be displayed in one color, and above-quota sales can be displayed in another color.

But... conditional formats that format one column based on the value of another column are not reflected in a pivot table, but are reflected in a standard table. For example, setting the color of a region name based on the sales in that region has no effect in a pivot table. So you can in a pivot tale only define a conditional format based on the data of the same column.

But conditional formatting on multiple columns in a pivot table is (fortunately) still possible in the following way. We can do this using some simple HTML code with the administration tool in the business layer.
First we create a new column (column 3) in which we display the value of the data column (column 1). With a case statement based on the data of column 2 and column 1 (for example) we add HTML instructions for showing the data of column 1 in a red color (too low) or green color (ok):














With the cast operator we cast this new column to a varchar10 column.
Now we can add this column to the presentation layer and use it within Answers. The last thing we have to do is change the format of this column into HTML:
























This seems to work perfect. It's easy to use this in the administration tool and if needed we can construct very advanced instructions for conditional formats. And by using pivot tables it is not necessary to format every individual cell.
But by using HTML it is not possible to download our pivot table to a PDF report. I am looking forward to the next release of OBIEE. Perhaps there are new opportunities in the area of conditional formatting...

3 opmerkingen:

  1. Dear rinie, I try your method and it works.
    However, when adding new column for the answer or change Pivot Table's column placement, the value will cease to exist in the pivot table's measure.
    Have you experience any kind of situation like this?

    BeantwoordenVerwijderen
  2. Dear azure, i'm glad it works. I did not have any weird experiences with this method. It's just a "simple" html-field which in my case always worked.
    Is your problem not being caused by a different aggegration / level?

    BeantwoordenVerwijderen
  3. Well, once I change the Measure's aggregation Rule from Default to Max, the Text Show up. Maybe the problem is in the Pivot Table itself then…

    BeantwoordenVerwijderen