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
