OBIEE11g Grand Totals with Calculated formulae (divison)

Lately, we ran into a User specific scenario, where they wanted us to display Grand total row also as division of total members and participants, based on region.
We tried a lot to achieve it through aggregation rule, using server complex aggregate, but it was not giving correct result. As you can see in image below:

Scenario was to achieve Grand total for “% Finalized column” as 154.33  instead of summation (sum) 598.86. Since our report was a Union Report , so we tried new functionality of OBIEE 11g , named “Add Result Column”.
We wrote small formula in Edit column as below, where saw_1 is region, saw_2 is Total Members and saw_3 is Participants
(Sum(saw_2 by saw_1)/Sum(saw_3 by saw_1)*100.00)
This gave us the correct answer.
Stay Tuned J
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s