Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculated member formula for All member is not SUM of children Expand / Collapse
Author
Message
Posted Tuesday, November 2, 2010 7:23 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:18 AM
Points: 676, Visits: 432
Hello everyone,
I am working on a proof of concept to move some heavy analytical calculations into SSAS. Everything was working fine until I hit a particularly complex calculation. What appears to be occuring is the children are SUMMED correctly, but the All member is not.

Psuedo code
MEMBER Measures.x AS

SUM(
[Dimension].[Attribute]
,
CASE
WHEN [Measures].[Cost] > 0 AND ([Measures].[Measure1]*[Measures].[Measure2]*[Measures].[Measure3])/[Measures].[Cost] > 5 THEN
[Measures].[Cost]/([Measures].Measure1*[Measures].[Measure3])
ELSE [Measures].[Measure2]
END)



I understand why it is happening, It's taking the values of the All member for the measures vs summing the result of the children. The question is how do I get the sum of the CHILDREN for All vs the calculation of the values of the All member?

FYI, I have tried adding a Calculation in the cube and processed it, but I got the exact same result.

Any help is appreciated.



Post #1014456
Posted Tuesday, November 2, 2010 7:30 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 11:18 AM
Points: 676, Visits: 432
Nevermind, I found what I needed.

For the Set argument, I used a case statement.

SUM(
CASE [Dimension].[Attribute].CURRENTMEMBER
WHEN [Dimension].[Attribute].[All] THEN [Dimension].[Attribute].CURRENTMEMBER.CHILDREN
ELSE [Dimension].[Attribute].CURRENTMEMBER
END,
...
)





Post #1014465
Posted Monday, April 7, 2014 6:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 7, 2014 6:38 PM
Points: 1, Visits: 0
Thanks for posting the solution.It was a life saver! I had a similar problem and being an MDX newbie, was breaking my head to find the solution..
Post #1559319
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse