Hi Experts,
I have developed a WEBI report whose backend data source is a BEX query. The query has a hierarchy say ‘H1’ and a measure say ‘m1’. I need to calculate total of ‘m1’ for each hierarchy level. In order to avoid doubling m1 with the expansion of each hierarchy level, I have used the following formula for m1 total
= Sum(if([H1].Depth = 0 ; [m1] ; 0)) I used the formula from the following link
https://archive.sap.com/discussions/thread/3331847
I also tried using the soln. from the following link.
https://archive.sap.com/discussions/thread/3359253
No Luck…
This formula from first link perfectly calculates the m1 total for each hierarchy level without doubling the aggregation with hierarchy level expansion BUT
The problem with the formula arises when I use H1 as an input control filter on my webi report. The m1 total is correct only for the top hierarchy node for all child nodes when selected gives ‘0’ as a total because I’ve used the condition
if([H1].Depth = 0 ; [m1] ; 0)I have tried different variations of the formula, e.g., instead of 0 in else condition, I give the condition
if ([H1].Depth = 1; [m1]; if ([H1].Depth = 2; [m1]; 0))I have 3 levels of hierarchy. So my formula becomes
Sum(if([H1].Depth = 0 ; [m1] ; if([H1].Depth = 1 ; [m1] ; if([H1].Depth = 2; [m1] ; 0))) )This variation doesn’t produce 0 total for any hierarchy level but aggregation (m1 total) is doubled for depth 1 and tripled for depth 2.
I can understand that for level 1, it includes the sum of both Depth 0 and depth 1 and same for depth 2, this adds for Depth 0, Depth 1 and Depth 2.
So to avoid this I used sum with each level separately, but this gives “MultiValue error”.
Please please please suggest a formula that works for both aggregation and input control filtering the child node without producing 0 total
Any help will be greatly appreciated.
Thanks!!!
Afreen (BOB member since 2016-10-25)