Search This Blog

Sunday 2 December 2012

Grand total Conditional Formatting in Pivot tables


Sometimes because of the users requirements we need to apply a specific format to our reports, different from the default one. As an example in a table we might have to change the cell colour depending on the value of that column (i.e. Green cell for positive values, red for negatives). In this case we will just apply conditional formatting and we could easily solve it, but this solution does not work in grand totals in pivot tables.
In this article we are going to see how to conditionally format a grand total in a pivot table by changing the XML code of the report.
In the table below we have applied conditional formatting to the quantity column. It is coloured in green when Quantity is >=100 and red if lower.
ab 1
As we see it works for all the rows except for the grand total. To solve it, we need to go the XML code and modify it.
Go to the advanced tab and retrieve the XML and paste it in your favourite XML editor. In the XML code search for the conditional formatting statements that we have just created (Look for tag; <saw:condition>) and copy them in a new sheet.
ab 2
In the code above we can see the conditional request that we have created in our quantity column.
Now go back to the report and edit the format of the grand total value. You can change it as you want as we only need to do it to get the grand total format statement in the XML and it will be overwritten with the right formatting later.
ab 3
Go now back to the advanced tab and retrieve the XML. Look for the pivot table view statement:
ab 4
Copy your condition statement inside the display format section. The final code should look like this:
ab 5
Notice that you might have to edit the code depending on how the previous grand total formatting was done.
Now the XML code is ready, go back to the Advanced tab and set it as the new XML. Go to results and select pivot table view:
ab 6
As we can see, the grand total now has also the conditional formatting depending on the quantity.
We can easily reuse the code to apply the same formatting to more than one column as we only have to change the column id in the condition XML statement:
ab 7
If we now add the amount column the code will be:
ab 8
And the table will look like this:
ab 9
Conclusion:
In this article we have seen that modifying the XML code of a pivot table is an easy way to apply conditional formatting in grand totals in pivot tables. Also we have seen that it is possible to reuse the XML code to easily apply the same conditional formatting to more columns. 

1 comment:

  1. Is anyone getting this to work in 11g? I have tried and get the error: DXE compiler error. No table 'GTGT' found in DXE. Source name: DxeAPI. XML: None. Error Codes: YV3KVTDM

    ReplyDelete