OBIEE - repeated conditional format - the lazy way

I saw a question today: what is the easiest way to create the same conditional format many times in the same Analysis? It's OK to do it manually 2-3 times, but its painful 30, 40 or 50 times for different columns.

So the question is how to have repeated conditional format in OBIEE, the lazy/sane way.

In OBIEE 11 there is a nice option of "Import formatting from another Analysis"

but it doesn't seem to copy conditional format...

So I'm going to do something that is not recommended to do: modify the Analysis XML manually. The problem with it, even if it seems fine at the moment, you don't know when it's going to surprise you. In most cases, in your next major upgrade (or never).

So lets create a sample Analysis:

Nothing special: 3 dimension columns + 1 measure. In the 3 dimension columns we will change the Column format to "Repeat" (this can be done with the "Import formatting from another Analysis" if you have many columns)

Now lets go to the last - Advanced Tab of the Analysis definition 

 and copy the Analysis XML. Paste it to a text editor. We will use it for reference.

Next step is to add actual conditional format, for example I'll set the Product type to have red background when the value of Revenue is bellow 2000.

Lets copy the updated XML to Text Editor again and compare with the original XML.

We will see that the XML of the "Product Type" column changed from:

<saw:column xsi:type="saw:regularColumn" columnID="ce4c38164528dcaa4">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Product Type"</sawx:expr></saw:columnFormula>
            <saw:displayFormat>
               <saw:formatSpec suppress="repeat" wrapText="true"/>
</saw:displayFormat>

To:

<saw:column xsi:type="saw:regularColumn" columnID="ce4c38164528dcaa4">
<saw:columnFormula>
<sawx:expr xsi:type="sawx:sqlExpression">"Products"."Product Type"</sawx:expr></saw:columnFormula>
            <saw:displayFormat>
               <saw:formatSpec suppress="repeat" wrapText="true"/>
               <saw:conditionalDisplayFormats>
                  <saw:conditionalDisplayFormat>
                     <saw:formatRule>
                        <saw:condition>
                           <sawx:expr xsi:type="sawx:comparison" op="less">
                              <sawx:expr xsi:type="sawx:columnRefExpr" columnID="c1ed85b47b1b78eff"/>
                              <sawx:expr xsi:type="xsd:decimal">2000</sawx:expr></sawx:expr></saw:condition>
                        <saw:formatSpec backgroundColor="#FF0000" wrapText="true"/></saw:formatRule></saw:conditionalDisplayFormat></saw:conditionalDisplayFormats></saw:displayFormat>

What is the difference?

The last part of </saw:displayFormat>

Was replaced by

<saw:conditionalDisplayFormats>
                  <saw:conditionalDisplayFormat>
                     <saw:formatRule>
                        <saw:condition>
                           <sawx:expr xsi:type="sawx:comparison" op="less">
                              <sawx:expr xsi:type="sawx:columnRefExpr" columnID="c1ed85b47b1b78eff"/>
                              <sawx:expr xsi:type="xsd:decimal">2000</sawx:expr></sawx:expr></saw:condition>
                        <saw:formatSpec backgroundColor="#FF0000" wrapText="true"/></saw:formatRule></saw:conditionalDisplayFormat></saw:conditionalDisplayFormats></saw:displayFormat>

Now I'll copy it and replace for each of the 2 other columns. Note to replace the </saw:displayFormat> that comes after <saw:formatSpec suppress="repeat" wrapText="true"/> and not any other. 

The last part: copy the entire fixed XML and paste it back in the Analysis XML.

Press "Apply XML":

That's it.

If you wonder why I replaced the </saw:displayFormat> and didn't just add the code that comes before it, both are OK.

Please note the Revenue column is described here as: columnID="c1ed85b47b1b78eff",so you can't copy paste it between different cases.

 

The result is:

QED.