The Secret Life of Conditional Formatting in OBIEE

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.

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

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).

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.

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

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.

<saw:conditionalDisplayFormat>
  <saw:formatRule>
    <saw:condition>
      <sawx:expr xsi:type="sawx:comparison" op="equal">
        <sawx:expr xsi:type="sawx:columnRefExpr" columnID="c632a4f0428ecfa91"/>
        <sawx:expr xsi:type="xsd:string">Switzerland</sawx:expr>
      </sawx:expr>
    </saw:condition>
    <saw:formatSpec backgroundColor="#FF0000" wrapText="true"/>
  </saw:formatRule>
</saw:conditionalDisplayFormat>

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.

<saw:conditionalDisplayFormat>
  <saw:formatRule>
    <saw:condition>
      <sawx:expr xsi:type="sawx:logical" op="and">
        <sawx:expr xsi:type="sawx:comparison" op="equal">
          <sawx:expr xsi:type="sawx:columnRefExpr" columnID="c3d00191589cdd4e2"/>
          <sawx:expr xsi:type="xsd:string">Games</sawx:expr>
        </sawx:expr>
        <sawx:expr xsi:type="sawx:comparison" op="equal">
          <sawx:expr xsi:type="sawx:columnRefExpr" columnID="c632a4f0428ecfa91"/>
          <sawx:expr xsi:type="xsd:string">Switzerland</sawx:expr>
        </sawx:expr>
      </sawx:expr>
    </saw:condition>
    <saw:formatSpec backgroundColor="#FF9900" wrapText="true"/>
  </saw:formatRule>
</saw:conditionalDisplayFormat>

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.

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.

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.

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.

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.