About two year ago I wrote an article on using the BY statement to “pin” your calculation on certain level. (http://obiee101.blogspot.com/2008/02/obiee-aggregate-by.html). Recently Kurt Wolf of KPI partners did a good analysis on how to “pin” the calculations on for the complete request. (http://kpipartners.blogspot.com/2009/12/aggregate-function.html) Here is a simple implementation of his findings.
Let’s start with a simple report, YEAR and AVG PRICE ("F1 Revenue"."1-01 Revenue (Sum All)"/"F2 Units"."2-01 Billed Qty (Sum All)"):
If we drill down the time dimension, we will see the AVG Price change accordingly:
We can “pin” an extra avereg column to the calendar year by using the AGGREGATE BY function (AGGREGATE(("F1 Revenue"."1-01 Revenue (Sum All)"/"F2 Units"."2-01 Billed Qty (Sum All)") by "D0 Time"."T05 Per Name Year")) (IT’S NOT in the formula editor, you will have to type it!)
But what if we want it for the whole report? Simple leave the BY part empty: (AGGREGATE(("F1 Revenue"."1-01 Revenue (Sum All)"/"F2 Units"."2-01 Billed Qty (Sum All)") by )
Till Next Time
No comments:
Post a Comment