"weighted average" in total cells

building reports webintelligence
1

we have “weighted average” object in an universe which is defined for database as
Sum(amount * interest_rate) / Sum(amount)

it works good in detail cells or a report,
but how to use it in total cells?

  1. if we set up Function = SUM for this object,
    report summarized it:

grp  amount  "weighted average"
r1    500       5
r2    600      10
Total 1100     15  --here we need (500*5 + 600*10) / (500+600) =   7.72
  1. if we set up Function = Database Delegated,
    Oracle Database has to run long UNION query with all combinations of totals needed - several table scans instead of one with ROLLUP/CUBE clause… why???

Is there a better way?
WEBI XI 3.1
Thanks!

alex1st (BOB member since 2009-10-07)

2

Try using

Runningsum ( amt * wt avg) /runningsum(amt)

arunmozhi :india: (BOB member since 2007-12-26)