4 Replies Latest reply: Sep 11, 2013 5:25 AM by AmarnathK RSS

    ASO Formula issue

    ce29f14e-f6a9-423f-8b26-86ff422236ee

      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