The Secret Life of Conditional Formatting in OBIEE

March 26th, 2014 by

When dealing with conditional formatting the GUI allow us to add as many formats as we want, but every single format has a unique and single condition, not more, not less. In this post I am going to show how it is possible in OBIEE to use multiple conditions to define a special formatting on an analysis column. Sometimes the requirement for conditional formatting is a little more complex, mainly when using pivots, requiring 2 conditions like for example “year = current-year AND country = UK”. In general we try to manage this requirement by a set of conditional formats sorted in a specific order trying to achieve something as close as possible to this, sometimes also setting one format first and then overriding it back in some cells of a pivot to make them looks like the cells without special format. It would be a lot easier to be able to just define a more complex rule for the special format but the GUI doesn’t allow it.

Does it means OBIEE can’t manage complex conditions formatting? No, not at all! Let me introduce you the secret life of conditional formatting…

Before we look at the practical implementation, first let us consider the “history” of conditional formatting, and why we can be optimistic that the method I describe will work.

It must be noted that because it’s not a functionality available through the GUI there is no guarantee it will work correctly or OBIEE will support it in futures releases, but for me it works fine in OBIEE 11.1.1.7 and related patched versions.

Some theory

OBIEE Analyses are stored in an XML format that OBIEE interprets when building and running the report. Using the Advanced tab of an analysis we can examine this XML. The XML can tell you a lot of information and give some hints about functionality the GUI doesn’t allow to perform but based on the XML you can easily guess it will work if coded by hand. Let’s focus on the XML related to conditional formatting as it’s the topic of this post.

Part of the XML standard is an associated XSD file, the XML Schema definition. OBIEE’s XML honours this and we can use this to examine the XML that OBIEE will accept and expect for an analysis. jDeveloper is a good tool to analyze XSD thanks to its built-in “design/source” viewer.
This is the graphical representation of the XSD describing the code behind conditional formatting (click to zoom). If you compare it with the XML you will recognize most of the elements. The important element is into the formatRule block, inside condition: the XSD expect a sawx:expr element there.

cond-format_displayFormat_XSD

Why is it important? Well if you look at a separate part of the XSD, which deals with the filters in an analysis.

cond-format_filter_XSD

Surprise: it’s the same sawx:expr. What does it mean for us? Because the filters accept AND and OR logical operator to join filters together, the XML of the analysis will accept the same syntax in the conditional formatting condition block. There is no guarantee at this point it will work, the XML will be valid but if the code parsing and interpreting the XML doesn’t implement this functionality it will not produce anything (or an error in the worst case). Time to move to the practical part and try it.

Step by step example

I start by creating my pivot where I display Revenues by Country (filtered list to some elements) and Line of Business (LOB).

cond-format_simple_pivot

Now my target is to highlight figures for “Switzerland” in the countries and “Games” in the LOB, so I add a conditional format on the Revenue column and set a red background for Switzerland and yellow background for Games.

cond-format_normal_condition

As expected the result is my pivot with a red row (for Switzerland) and a yellow column (for Games).

cond-format_normal_condition_result

It doesn’t really looks good because the intersection of Switzerland and Games will use the format of the last matched condition, in my case the yellow of Games because it’s the second conditional formatting defined. I would prefer to have this intersection in a nice orange background, but the GUI doesn’t have an option to combine conditions.

The GUI doesn’t do it but it doesn’t mean it’s not possible! Let’s have a look at the XML managing the conditional formatting. First we look an example of the code producing the red background based on the country.

Now, as we saw before, the XSD allow me to use the syntax of filters, including filters containing logical operators (AND, OR) to join conditions together. So if I add the XML with the AND operator I will be able to use the condition country=Switzerland AND LOB=Games to apply a different format.

In a text editor I prepare the new XML for the analysis with an additional <saw:conditionalDisplayFormat> element with my new hand-made condition.

It looks good and I can now try to paste it back in OBIEE and see if the syntax is correct and my XML will be accepted: in the Advanced tab of the analysis I replace the existing XML with my own version of the code.

When clicking “Apply XML” OBIEE will parse and evaluate the XML. If there is an error a message is displayed and the code is not applied (so as to not break the analysis). If you get an error, double check your XML, make sure every tag you open is closed and read the error message as it says what is the problem.

cond-format_edit_xml

Time to click the Results tab and check the new result of my analysis. YES! There is a nice orange background in the intersection now, proving that even if the GUI can’t do it OBIEE is able to accept complex conditions to define conditional formatting.

cond-format_normal_condition_final_result

What do we see if we check the properties of the column in the GUI? As you can see in the next screenshot we see there is a condition setting an orange background, but the condition itself is just shown as sawx:expr:sawx:logicaland. Where does this text come from? It’s a concatenation of the name of the first XML tag containing the logical operator and the values of the attributes of the tag itself. So if you use a OR instead of AND you will see a condition named sawx:expr:sawx:logicalor.

cond-format_final_condition

Can we edit the condition in the GUI? Not really, the edit window is opened, but the formula will not be recognized and if you set an operator and a value you will lose your hand-made conditional formatting and generate a meaningless piece of XML, so don’t do it for any reason. On the other hand the format can be edited using the GUI with no impact on the rule, so feel free to do it and correct the format if you don’t feel comfortable coding a format by hand in the XML.

cond-format_final_condition_edit

To resume, and some advice

  • It’s possible to have a complex formula as condition in a conditional formatting rule.
  • It’s not supported by the GUI, needs to be done by hand in XML.
  • Do it as one of the last steps when building an analysis because you can’t edit it via the GUI.
  • Be familiar with the produced XML before you play with it.
  • Use the Filters section to create the condition and avoid mistakes, so a copy/paste of the generated rule will help in creating the condition.
  • Document, document, document! As the condition is visible only in the XML document it somewhere, even just in a Static Text view you add to the analysis itself (and don’t display on the screen).
  • Be aware of the maintenance overhead that direct XML manipulation will have – don’t abuse the “cheating”.
  • If you don’t want to hack the XML, an alternative solution can be to use a extra column added to the analysis to evaluate the complex condition and produce a flag used for the conditional formatting. The downside of this is that the condition will be sent to the database as part of the query for evaluation.

Tags: , , , , ,

Comments

  1. Kanishk Says:

    Hi,

    thanks for the post.

    Please can you tell how to do conditional formatting for Grand total in OBIEE 11g? In 10g it used to work with changes done in the xml, but in 11g it throws error.

    any idea?

  2. krishna Says:

    Hi Ceresa,

    Nice article. I had similar situation where I created custom column and based on that I did formatting. It was really interesting to know how the XML works. Thanks for the details.

  3. Manish Says:

    We can simply add the 2 logical conditions together using “And” logic and make that as 1st condition….

  4. Gianni Ceresa Says:

    @Manish:
    How do you want to add 2 logical conditions in the GUI with a AND? This is the point: the GUI doesn’t allow you to do it, but the XML yes (and it’s exactly the sample: both conditions joined with a AND)

  5. Manish Says:

    @Gianni Ceresa – You need to do all math in 1 logical column and use that column for conditional formatting…Lets say you have highly complex logic…So rather than doing everything in Conditional Logic formatting or XML…I feel it is better to create additional computed column in presentation/criteria tab…and then only use that column directly to do conditional formatting.

  6. Manish Says:

    To add 1 example….Suppose you have analysis with 4 columns A, B , C and D….Now you want to do some conditional formatting on column D based on values in Column A and Column Column B…..Add extra column “Coloring Logic” in analysis …and use all logic under column formula for this new column….This is easily possible, easy to edit and maintainable also…Now use this column to decide the conditional formatting on column D. I hope this explains

  7. Gianni Ceresa Says:

    @Manish
    Yes, you can use that option, it’s what I wanted to express with the last point in the resume & advice. But your logical column with all the logic inside will be sent to the database as part of the physical query. It’s easier to maintain, easier to find and change, but you are generating extra work for the physical source of data. Hacking the XML only generate the work on the BI part, not impacting at all the physical sources. And in a context like multiple UNIONS you will also need to replicate your complex column multiple times. I will say it’s good to have (and know) both options in the end so you can choose the best one for your need.

  8. Manish Says:

    @Gianni Ceresa – Yep i agree that knowing this extra option will definitely help as well. I still don’t think that in all circumstances logical computation is being sent to database….I have seen many times (but not all times) that its being done in BI server…I am still learning so don’t know why sometimes it does 1 way and other times other way….

  9. Narendra Says:

    This XML Editing works like a champ in Pivot table multiple condition formatting which is not working with concatenation of columns value result.

Website Design & Build: tymedia.co.uk