Sum based on chart of Account Name

Question

I'm trying to create a Report Dimension (Income Statement Report) in my Cube and would like to perform calculation (Sum or Divide) based on Report Action attribute. The Dimension table looks like below

Could you kindly let me know if I could achieve this by MDX script? Thanks a lot for your help!

San83 San83 · Accepted Answer · 2016-01-28T04:09:37

I tried few things and finally used Scope statement to achieve this. I'm posting my solution here which works across all measures. Please feel free to advise improving the current solution.

// Rporting Option  //

Scope ([Reports].[Report Item].Members);

//Total Sales//

IF [Reports].[Report Item].CurrentMember IS [Reports].[Report Item].&[Total Sales] THEN 
    This = Sum({[Reports].[Sort Key].&[1]:[Reports].[Sort Key].&[2]})
END IF;

//Variable Contribution//

IF [Reports].[Report Item].CurrentMember IS [Reports].[Report Item].&[Variable Contribution] THEN 
    This = Sum({[Reports].[Sort Key].&[1]:[Reports].[Sort Key].&[3]})
END IF;

//Variable Margin//

IF [Reports].[Report Item].CurrentMember IS [Reports].[Report Item].&[Variable Margin] THEN 
    This = Divide(Sum({[Reports].[Sort Key].&[1]:[Reports].[Sort Key].&[3]}),Sum([Reports].[Sort Key].&[1]))
END IF;

//Gross Profit//

IF [Reports].[Report Item].CurrentMember IS [Reports].[Report Item].&[Gross Profit] THEN 
    This = Sum({[Reports].[Sort Key].&[1]:[Reports].[Sort Key].&[4]})
END IF;

// and so on for more for other report totals

END Scope;

Sum based on chart of Account Name

1 Answers