Business Intelligence – Oracle

Archive for March 12th, 2008

Oracle BI EE 10.1.3.3/2 – Conditional Formatting based on Multiple Columns

Posted by Venkatakrishnan J on March 12, 2008

Another simple question came today to me via email today. Though everyone would know about this, i thought i would blog about it to keep this as a future reference. As soon as 11g comes out(not sure when though), i shall be blogging about how the same can be done in 11g. The question is “I have a very simple report containing Region, Dollars Year Ago, Forecast and Dollars as shown below. I need to highlight all the Regions in Red for which Forecast is greater than Dollars Year Ago but is less than the actual sale( Dollars).” i.e in the report shown below we would have to highlight all the regions except the WESTERN REGION.

      

In order to achieve this we will be following a very simple technique. The first step is to create a new column containing the below formula.

CASE WHEN Measures.Dollars > Measures.”Forecasted Dollars” AND Measures.”Forecasted Dollars” > Measures.”Year Ago Dollars” THEN 1 ELSE 0 END

      

Now, hide this column and go to the properties of the Region Column and add a conditional Format, something like “If the new column created above is 1 then Red Color”.

      

      

Now, you would have all the Regions for which Forecast is lesser than Sales but greater than year Ago sales, to be highlighted in Red.

      

Again very useful technique. But this can be applied only for tabular views since conditional formatting on one column based on other columns are not available in other views.

Advertisements

Posted in All Posts, OBI EE Plus | 6 Comments »