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:
This was previously a bug in OBIEE that was addressed by adding the ReportAggregateEnabled option. It’s not really documented anywhere other than on Metalink, so hopefully this will be helpful to someone.
October 4th, 2008 at 1:16 pm
Thanks Kevin,
I was already on my TODO list. I made a reference to this entry: http://obiee101.blogspot.com/2008/10/obiee-reportaggregateenabled.html . Keep up the good work!
October 7th, 2008 at 12:42 am
This is a great tip Kevin. Are you aware of any tradeoffs that occur when turning this option on? Some other reporting becomes problematic?
October 9th, 2008 at 11:45 am
Kevin,
this is an awesome OBIEE tutorial - and I’ve actually been looking for this info for some time! I actually came here through John Minkjan’s blog. I want to wish you many posts and keep up with good work. Hopefully, we’ll nail more of those undocumented OBIEE settings to make our consultants life easier.
Andy
October 27th, 2008 at 11:20 am
Hi Everyone,
Sorry, I just realized I have Wordpress configured so that comments require approval. And since I post with Windows LiveWriter, I rarely log into my WP control panel.
@John - thanks!
@David - I have turned this option on in one of my production environments and have not encountered any problems related to this feature. As always, ymmv.
@Andriy - thanks for the kind words, and I hope to make many more useful posts in the future when time permits