Tuesday, May 18, 2010

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:

image

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:

image

image

image

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

image

<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:

image

image

hmmmm this is not what we want!

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

image

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:

image

image

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

image

Remove the legend:

image

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

image

image

Till Next Time

3 comments:

Michal said...

wow, that's gorgeous!!

Alan - São Paulo said...

Thank you, John.

It worked greatly!
I own you another one.

Ed said...

this works in OBIEE 11g?