OBIEE Conditional format based on other column in charts

When you want to do a conditional format on a chart in OBIEE by default you only have two choices:

Based on a value or based on a presentation variable.

But what if you want to do it based on an other column. Let’s say you want the bar to be completely red if it’s below the line and completely green if it’s above the line:

First add two ‘dummy’ conditions one for red and one for green:

Next goto the advanced tab and located the conditions in the XML:

<saw:conditionFormats>
            <saw:conditionRule>
               <sawx:expr xsi:type="sawx:comparison" op="less">
                  <saw:columnRef columnID="c1"/>
                  <sawx:expr xsi:type="xsd:decimal">0</sawx:expr></sawx:expr>
               <saw:visualFormats>
                  <saw:visualFormat className="barProp" name="normalBar" color="#FF0000"/></saw:visualFormats></saw:conditionRule>
            <saw:conditionRule>
               <sawx:expr xsi:type="sawx:comparison" op="greaterOrEqual">
                  <saw:columnRef columnID="c1"/>
                  <sawx:expr xsi:type="xsd:decimal">0</sawx:expr></sawx:expr>
               <saw:visualFormats>
                  <saw:visualFormat className="barProp" name="normalBar" color="#00FF00"/></saw:visualFormats></saw:conditionRule>

Chance <sawx:expr xsi:type="xsd:decimal">0</sawx:expr> to the column you want use like: <sawx:expr xsi:type="sawx:sqlExpression">"11 Time Series"."1-07  Revenue  (Qago)"</sawx:expr>

Don’t forget to press the set XML button:

hmmmm this is not what we want!

What went wrong? The expression builder for the chart conditional format accepted our tweak:

The problem is the way the Presentation-server tells the Corda Engine to create the graph. Since the Corda engine can’t ‘see’ the data it needs to get it’s conditions HardCoded. This means the Presentation server will evaluate the first value of the SQL expression and sent it to the Corda engine.

Is there a workaround? It’s actually quit simple. Add two more columns to your table RED and GREEN:

Add them to your chart and make it a stacked bar:

Remove the legend:

Add it to your compound layout and add a extra legend:

Till Next Time