This discussion is archived
4 Replies Latest reply: Sep 11, 2013 3:25 AM by user10132385 RSS

ASO Formula issue

ce29f14e-f6a9-423f-8b26-86ff422236ee Newbie
Currently Being Moderated

Hi All,

 

We have an ASO cube with 21 dimensions and Accounts dimension has Indicators which are calculated using MDX formulas.

As per requirement , these formulas are to be calculated only for level 0 members.For upper level members, the values are to be rolled up as per the consolidation property.

The following CASE statement worked:

 

CASE 

WHEN IsLeaf([Dimension1].CurrentMember) THEN ([Actual]/([Actual],[Gross Sales]) *100)

ELSE

SUM({LEAVES([Dimension1].CurrentMember)}, [Actual GS %]),

END

 

But below is our requirement (we need the formula for 21 dimensions):

 

CASE 

WHEN IsLeaf([Dimension1].CurrentMember) THEN ([Actual]/([Actual],[Gross Sales]) *100)

WHEN IsLeaf([Dimension2].CurrentMember) THEN ([Actual]/([Actual],[Gross Sales]) *100)

ELSE

SUM({LEAVES([Dimension1].CurrentMember)}, [Actual GS %]),

SUM({LEAVES([Dimension2].CurrentMember)}, [Actual GS %])

END

 

But this throws error as we cannot have multiple statements in ELSE.

Can anyone suggest me how to achieve this?

 

Thanks,

Tejaswi

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points