OBIEE 10G/11G - Grand Total Formatting in a Pivot view

> Reporting and Data Access Tools > Oracle Business Intelligence (OBIEE/Siebel Analytics) > OBIEE - BI Presentation Service (SAW/OBIPS)

1 - About

OBIEE doesn't have in the answer GUI via the conditional formatting functionality the possibility to format the grand total. You can apply a conditional format but only on the column value and not on the total.

The Xml part of the Answer can be manually changed to overcome this limitation.

This solution will works only for a pivot view

A enhancement have been logged and this functionality is not yet implemented in the 11g releases 11.1.1.5.0

3 - Solution

3.1 - Creation of the conditional format XML part

  • On the column where you want that the grand total format condition apply,
  • Create your condition with the Answer GUI
  • Grab the xml part in the Advanced tab
  • Add the node <saw:totalValue> and <saw:displayFormat>

You will then end up with something like this:

<saw:totalValue>
  <saw:displayFormat>
    <saw:condition>
      <sawx:expr xsi:type="sawx:comparison" op="greater">
         <saw:columnRef columnID="c0"/>
         <sawx:expr xsi:type="xsd:decimal">5000000000</sawx:expr>
      </sawx:expr>
      <saw:displayFormat backgroundColor="#FF0000" wrapText="true"/>
    </saw:condition>
  </saw:displayFormat>
</saw:totalValue>
Advertising

3.2 - Modification of the XML definition of the pivot view

In the column axis part of the pivot view (included in the node <saw:edge axis=“column”>):

  • found the column where you want to add the grand total formatting (one of the node <saw:edgeLayer …>)
  • and add the previous XML

You will then end up in our example with :

<saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" rptViewVers="200510010">
<saw:edge axis="page"/>
<saw:edge axis="section"/>
<saw:edge axis="row" total="after">
	<saw:edgeLayer type="column" columnID="c1"></saw:edgeLayer>
</saw:edge>
<saw:edge axis="column">
	<saw:edgeLayer type="labels">
	     <saw:totalValue>
		<saw:displayFormat>
		   <saw:condition>
			<sawx:expr xsi:type="sawx:comparison" op="greater">
		           <saw:columnRef columnID="c0"/>
			   <sawx:expr xsi:type="xsd:decimal">5000000000</sawx:expr>
			</sawx:expr>
			<saw:displayFormat backgroundColor="#FF0000" wrapText="true"/>
		   </saw:condition>
		</saw:displayFormat>
	     </saw:totalValue>
        </saw:edgeLayer>
</saw:edge>
<saw:edge axis="measure">
        <saw:edgeLayer type="column" columnID="c0"/>
</saw:edge>
</saw:view>

3.3 - Apply the XML and result

To apply the modification, click on the “set XML” button of the Advanced Tab and you will have a grand total conditional formatting:

4 - Documentation / Reference