Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Conditional Formatting based on multiple character columns in a Pivot Table

Posted by Venkatakrishnan J on April 29, 2008

Use this approach only if you have no other options. Also, this would work only if you have non-numeric columns(on which you want to apply conditional formatting). Not an elegant solution but would work neverthless. As the title of this blog suggests we shall be looking at an approach to enable cross column conditional formatting on Pivot Tables. You might probably be aware of the fact that BI EE does not support cross column conditional formatting in Pivot Tables. This is stated very clearly in the New Features Guide here. But what if your end user somehow wants this feature. In such a case, just follow the below mentioned procedure. Lets start with a simple Pivot table report as shown below

    

The above report gives a report on the Region, Brand and their corresponding sales. Now, our aim is to highlight all the Regions in Red whose Brand value is Enterprise. In a tabular view this is straightforward since it supports cross column conditional formatting. But since in our case this is a pivot table, go back to the criteria tab and edit the formula tab of the Region column. Type in the below formula.

‘<!– ‘|| Products.Brand||’ –>’||Markets.Region

    

Now, convert this column to a HTML format.

    

Once this is done, go to conditional format of this column and give the necessary conditional formats (You would have to choose all the 4 Enterprise Brands||Regions. This is the main drawback with this approach. It would really nice if conditional Formatting supports the containsAny operator ).

    

    

Now, if you go to the pivot table you would notice that all the regions which are under the Enterprise Brand would be highlighted. The idea is to basically concatenate the columns and then apply the conditional formatting. The formula above (which is nothing but HTML commenting) will avoid the display of the columns multiple times.

    

    

Not the best solution but can be useful if you somehow need to use cross-column formatting in pivot tables.

 

About these ads

5 Responses to “Oracle BI EE 10.1.3.3/2 – Conditional Formatting based on multiple character columns in a Pivot Table”

  1. Stijn Gabriels said

    Hi Venkat,
    this is a nice solution, but when you print the pivot table to pdf, the HTML markup will be displayed in pdf.

    Regards,
    Stijn

  2. Mark said

    You are right. These are the kind of solutions which I honestly find very useless. But is a good try at achieving something which is used a major requirement for any sort of financial reporting.

  3. dmx said

    Hi Venkat,

    This is a quite nice solution. However, I need to do cross column conditional formatting on Pivot Tables for the direct request to data base.
    I still do not know how to construct a tag similar to this one ‘’||Markets.Region

    Could you help me out?

  4. eRosy said

    Hi Venkat ,

    This would be great help but the drawback seems to be deadly when I have the range of date in pivot column and with the change of date new concatenaed string will be generated which I need to add on each and evry day which seem to be not a good option .

    Anyway though the reqr is not fulfilled rather can be done that is the satisfaction .Hopefully it will be addressed on 11g .

    One more question I have .How possible to add a link on top of the conditional images to support as I do have reqr to redirect this to diff page if condition satifies .

    Rgds,
    Rosy.

  5. Vijay said

    Hi Venkat,

    I am getting the same columns multiple times inspite of selecting the text format as HTML. any idea why?

    thanks
    Vijay

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: