Exporting data using MDX Part 2 -- Totals

I feel like adoption of MDX within Essbase is limited because 1) the output format is messy and 2) the documentation is challenging to following and understand. I couldn't find any examples in the Tech Ref for adding column totals to a query (let me know if you find any) so I'll try to address that here.

The following query selects the children of Qtr1 in Sample Basic and totals them in the final column. The difference here is that we're using MDX to do the aggregation instead of Essbase. It might not make sense to do that when you already have Essbase giving you the total but there could be a case where you need a custom rollup not available in your cube.

SET

1:  WITH  
2:  SET [_TimeSet] as '[Qtr1].CHILDREN'  
3:  MEMBER [Year].[_TimeTotal] AS 'AGGREGATE([_TimeSet])'  
4:  SELECT  
5:  {[_TimeSet],[Year].[_TimeTotal]} on COLUMNS,  
6:  {[100].Children} ON ROWS  
7:  FROM Sample.Basic;  

Line 2 creates a SET called _TimeSet. I start it with an underscore so I can easily tell that it's not a member within the Sample.Basic outline.

Line 3 then creates a calculated member called _TimeTotal which is an aggregate of _TimeSet.

Line 5 places the contents of _TimeSet and _TimeTotal onto the columns.

The beauty of the SET command is that if I ever need to change the columns in this extract, all I need to do is change line 2. The other lines automatically inherit the changes. It might not seem like a big deal on a seven line MDX statement, but just imagine one that's 50 lines long.

SUM()

While reading the documentation of the Sum() function I came upon what I think is an awful example. It looks like the developer wanted to print a report with the major markets and a sum of them at the bottom. They use the UDA to pick them out in the report but take the time to write out the individual states that are major markets. Why use UDAs if you're going to type out all of the members with that UDA? You'd have to update your report if any of the UDAs changed. <wagging finger> Also, the total doesn't add up all of the lines but only the lines that are states. Let's see if we can improve on this report.

We need the Level 0 members that are major markets. We can create that set with the following statement:

Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members)

The Intersect() function selects the, ahem, intersection of two lists -- what they have in common. In this case the list of members with a "Major Market" UDA and the list of members that are at Level 0.

We can either choose to keep the Market rollups:

1:  WITH MEMBER [Market].[Sum Expense for Main States]  
2:  AS  
3:  'Sum (Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members))'  
4:  SELECT  
5:  {[Measures].[Total Expenses]}  
6:  ON COLUMNS,  
7:  {UDA([Market], "Major Market"),  
8:  [Market].[Sum Expense for Main States]}  
9:  ON ROWS  
10:  FROM  
11:  Sample.Basic  
12:  WHERE ([Scenario].[Actual])  

Lines 1-3 define a member that will sum the intersection of those members with a Major Market UDA and those members at Level 0.

Line 7 lists the members with a Major Market UDA on the rows and line 8 prints the Sum that we created in lines 1-3.

Or we can print the report with only the states:

1:  WITH MEMBER [Market].[Sum Expense for Main States]  
2:  AS  
3:  'Sum (Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members))'  
4:  SELECT  
5:  {[Measures].[Total Expenses]}  
6:  ON COLUMNS,  
7:  {Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members),  
8:  [Market].[Sum Expense for Main States]}  
9:  ON ROWS  
10:  FROM  
11:  Sample.Basic  
12:  WHERE ([Scenario].[Actual])  

The difference between this report and the last is that now we omit the Major Market members with UDA that are above Level 0. Now the column of figures actually adds up to the sum at the bottom of the report.

If we need to use the same set multiple times within the same query we can write it using the SET command:

1:  WITH  
2:  SET [_MajorMarketSet] as 'Intersect(UDA([Market], "Major Market"),[Market].Levels(0).Members)'  
3:  MEMBER [Market].[Sum Expense for Main States]  
4:  AS  
5:  'Sum ([_MajorMarketSet])'  
6:  SELECT  
7:  {[Measures].[Total Expenses]}  
8:  ON COLUMNS,  
9:  {[_MajorMarketSet],  
10:  [Market].[Sum Expense for Main States]}  
11:  ON ROWS  
12:  FROM  
13:  Sample.Basic  
14:  WHERE ([Scenario].[Actual])  

This is essentially the same report as the prior one but now we've employed the SET command. If we ever need to update the list of members in this extract, we just update line 2. Line 5 inherits the change as does line 9. In my opinion, a much cleaner way of writing the MDX.