BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

BW Hierarchy - Aggregation and InputControl Filter

Search this topic... | Search Xcelsius - Crystal Dashboard Design... | Search Box
Register or Login to Post    Forum Index -> Other Products -> Xcelsius - Crystal Dashboard Design  Previous TopicPrint TopicNext Topic
Author Message
Principal Member
Principal Member

Joined: 24 Oct 2016

Posts: 118

PostPosted: Fri Feb 22, 2019 2:28 am 
Post subject: BW Hierarchy - Aggregation and InputControl Filter

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

I also tried using the soln. from the following link.

No Luckā€¦..icon_sad.gif

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.

Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Other Products -> Xcelsius - Crystal Dashboard Design  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0277 seconds using 18 queries. (SQL 0.0029 Parse 0.0008 Other 0.0240)
CCBot/2.0 (
Hosted by | Terms of Service
phpBB Customizations by the
Shameless plug for Moments of Light Logo