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

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

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
Afreen
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

Code:
= 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ā€¦..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

Code:
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

Code:
if ([H1].Depth = 1; [m1]; if ([H1].Depth = 2; [m1]; 0))


I have 3 levels of hierarchy. So my formula becomes

Code:
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!!!!!
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.0265 seconds using 18 queries. (SQL 0.0024 Parse 0.0008 Other 0.0233)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo