Getting a SUM() of Members Independent of a Measure

A simple, yet common, problem for MDX reports is combining non-measure members which are query specific. The requirement to combine the members is a reporting requirement which means it doesn't belong in the cube. Here's hte problem statement and two solutions.

I want to select not only [Aus - New South Wales], but also [Canada - Alberta], and show both as one sum, not 2 separate columns. Normally, the WHERE clause needs a tuple, and each dimension can only appear once in a tuple. The following MDX illustrates the use of a single reference to the customer dimension in the WHERE clause.

SELECT
NON EMPTY {Measures.[Internet Sales Amount], Measures.[Internet Tax Amount]} ON COLUMNS,
NON EMPTY {[Product].[Product Categories].[Category].ALLMEMBERS} ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2004],[Customer].[Customer Geography].[State-Province].&[NSW]&[AU])

One solution is creating a calculated member which SUM()s the values for NSW and Alberta, but the trick is placing the new calculated memebr as part of the Customer dimension which makes it independent of the Measure selected. Most MDX beginners assume you can only create new Measures. That's not the case. A Calculated member can belong in any Dimension.Hierarchy. Here's the solution with the calculated member which is then referneced in the WHERE clause to slice by the SUM() of the two country members.

WITH
MEMBER [Customer].[Customer Geography].[Test SUM] AS
'
SUM({[Customer].[Customer Geography].[State-Province].&[NSW]&[AU], [Customer].[Customer Geography].[State-Province].&[AB]&[CA]}, Measures.CurrentMember)
'

SELECT
NON EMPTY {Measures.[Internet Sales Amount], Measures.[Internet Tax Amount]} ON COLUMNS,
NON EMPTY {[Product].[Product Categories].[Category].ALLMEMBERS} ON ROWS
FROM [Adventure Works]
WHERE ([Date].[Calendar].[Calendar Year].&[2004],[Customer].[Customer Geography].[Test SUM])

Another solution (thanks to Deepak Puri) is using a SET reference in the WHERE clause which combines the two members as a set, and CROSSJOINs the set with the other members in the WHERE clause. This a nice trick, and not obvious - even to experienced MDX coders.

SELECT
NON EMPTY {Measures.[Internet Sales Amount], Measures.[Internet Tax Amount]} ON COLUMNS,
NON EMPTY {[Product].[Product Categories].[Category].ALLMEMBERS} ON ROWS
FROM [Adventure Works]
WHERE CROSSJOIN({[Customer].[Customer Geography].[State-Province].&[NSW]&[AU], [Customer].[Customer Geography].[State-Province].&[AB]&[CA]}, {[Date].[Calendar].[Calendar Year].&[2004]})