BIEE Grand Totals with Calculated Columns

This is a bit of a specific problem, but I could see it being something that those new to OBIEE could run into. It deals with incorrect Grand Total values being displayed for a Calculated Column when using report filters.

Consider the following report:

In this report, Amount A and Amount B are standard columns in a database table, and Variance is a calculated column in the repository. The calculation for Variance is 100 * (Amount B - Amount A) / Amount A. So, going by the Grand Total amounts in the report above, 100 * (696 - 550) / 550 = 26.55.

Suppose you want to filter the results of this request so that it only shows items having a Variance greater than 30:

Note the Grand Total for Amount A and Amount B are summed correctly for the filter, but the Variance still shows 26.55. Obviously, this is incorrect, so what’s going on here? The filtered amount should be reported as 100 * (220 - 130) / 130 = 69.23.

If you want to show the correct variance for the filtered Grand Total amounts, you need to enable an option in your instanceconfig.xml file. Open the file $OracleBIData/web/config/instanceconfig.xml, and add the following line somewhere inside your <ServerInstance> and </ServerInstance> section:

<ReportAggregateEnabled>true</ReportAggregateEnabled>

Make sure you don’t accidentally put this inside an element nested inside <ServerInstance>, or it won’t work.

Save instanceconfig.xml, and restart your BI Server and Presentation service.

Now, the correct amount should be shown for the filtered Grand Total variance:

Thank you,