Exporting data using MDX Part 3 -- More Totals

A question came up on Network54 (the greatest forum in the history of forums) the other day about how to create an MDX report where the column value is from the parent of a row member. This isn’t easy to do in the Excel Add-In (we’d probably have to create a calculated Measure in the outline) but MDX makes it relatively simple. Once again I’ll use our beloved Sample Basic database.

Using MDX we can create a member that crosses dimensions. In this case we will create a Measure whose value is Sales for a relative of a Market member. I’m going to create a report that shows Sales for the States in one column and the Sales for that State’s Market (i.e. East, Central, etc...) in the column next to it. Just for fun I’ll add a calculated column showing what percentage of Sales each State accounts for. The key here is to create a tuple with the member from the Measures dimension you want along with the reference to the row member.

1:  WITH  
2:  SET [_States] AS '[Market].Levels(0).Members'  
3:  MEMBER [Measures].[_MarketSales] AS '([Sales],[Market].CurrentMember.Parent)'  
4:  MEMBER [Measures].[_MarketSales%] AS '([Sales],[Market].CurrentMember)/([Sales],[Market].CurrentMember.Parent)'  
5:  SELECT  
6:  {[Sales],[_MarketSales],[_MarketSales%]} on columns,  
7:  NON EMPTY  
8:  [_States]  
9:  on rows  
10:  from [Sample.Basic]  
11:  where ([Year].[Qtr1],[Scenario].[Actual],[Product]);  

Line 2 creates the _States SET which will give us the list of states.

Line 3 creates the calculated member called _MarketSales which is defined by the tuple crossing Sales and whatever the Parent is of the Market member on the row -- in this case it will be a state.

Line 4 creates our calculated member that calculated the % of sales a state contains for the given market.

Lines 6 defines our columns which are made up of the members we just created along with Sales.

Line 8 references the SET we created on Line 2 which will make up our row selection.

And there we have our report. New York has sales of 7,705. New York's Market (East) has sales of 20,621. New York makes up 37.36% of the sales for the East.