Business Intelligence – Oracle

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

6 Responses to “Oracle BI EE 10.1.3.3/2 – Conditional Formatting based on Multiple Columns”

  1. Mano said

    Hi Venkat
    Thanks – very useful. I successfully used this technique to conditionally format data from my Excel datasource. I have one issue though – I’ve had to disable caching (in my NQSConfig.INI file) since the data in my source spreadsheet is constantly being updated. (that’s the only way I could think of)The minute I do this, the CASE WHEN formulas cease to work. (I get ODBC and SQL Preperation errors). Any ideas why?
    Thanks

  2. Mano said

    Hi Venkat
    Never mind about my query – Oracle has logged an Enhancement request for this issue.
    Thanks

  3. RestrepoBI said

    I need to create a conditional format for every cell that is above the average of every brand.

    How to Calculate the average for every brand, within a table?

    I know how to do it within a pivot table, but it cannot be used for conditional formatting.

  4. MG said

    Hello,

    I actually have a question about that example: how do you go about creating a calculated measure like “Year Ago Dollars” using Oracle BI, assuming you have an underlying star-schema?

    Thank you 🙂

  5. MG said

    To clarify my previous question, I’m looking for something like the MDX function “PrevMember” – I need to be able to relate measures to a specific year and to the past year in the Date dimension.

    Thanks 🙂

  6. Hemanth said

    Hi,

    I am using OBIEE to generate a financial report. It so happens that if I add more than a certain number of criteria (Unions etc), the data format – number, Parenthesis(red) disappears. In the data format tab I get to see only Custom text format, plain text etc. To overcome this, I have used #,##;(#,##) along with a conditional format that if column contents are less than 0, then I display text in Red color. Now, the problem arises when this particular number falls under a cell for which I need to have a border or some underlining. The conditional format doesnt seem to allow both conditions. As a result, the data is either red and in () or it is bordered/underlined depending on which condition appears first in the Conditional format list. Can you please suggest a work-around. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: