Wednesday, 23 July 2014

OBIEE Sum of Average

The image below shows a common business request. Sales Share figure is defined within Oracle BI server with aggregation rule average (avg). Share needs to be aggregated on level Client No and Code, but coming from Product No detail aggregation rule is requested to be sum of Sales Share Avg as highlighted in the waterfall chart. Setting aggregation rule sum in OBIEE server isn't feasible, because report doesn't show lowest level of detail for the fact presentation table. Thus, just removing Product No wouldn't solve the requirement.

Oracle BI Waterfall Chart

Solve the demand by using exclude column and table view aggregation rule. There are several possibilities to implement the sum of average functionality, even if you want to use it for derived calculations in formulas. But report based aggregation can be very complex and needs some more explanations. Therefore it would be great if you join our Oracle BI expert class at Oracle Education (see Oracle BI useful things for a detailed overview and schedule at OU). We elaborate report aggregation in an own section.

Go to edit view, exclude Product No and set Sales Share view aggregation rule to Sum:

Result looks like:

To check just calculate the sum of Sales Share from the first image ;-)

No comments:

Post a Comment