MDX query to use a set but return a single row

Question

I am new to MDX and have just started using Named sets to group several members of a dimension. Whenever I use a SET in a query, the results returned are always detailed out for each individual member of the set. I am looking to get one one for the set.

For example: I have two Measures: Sales Dollars and Shipped Units. The then have a State dimension for each of the 50 states in the United States.

I want to see the Sales and Units measures for 3 specific states and then also for a group (Named Set) of 4 other states.

Example MDX:

With SET [My Favorite States] AS '{[States].[Illinois], [States].[Wisconsin]}'
select NON EMPTY {[Measures].[Sales], [Measures].[Shipped Units]} ON COLUMNS,
  NON EMPTY {[States].[Alabama], [States].[New York], [My Favorite States]} ON ROWS
from [cubename]

This returns:

           Measures
States     Sales       Shipped Units
Alabama     $100              5
New York    $500             20
Illinois    $150             15
Wisconsin   $900             25

What I want is for the Set to appear as a total on a single line. Similar to:

                     Measures
States               Sales       Shipped Units
Alabama               $100              5
New York              $500             20
My Favorite States    $1,050           40

Is there an MDX function that will allow the set of specific members to be treated as a group?

Rudolf Rudolf · Accepted Answer · 2010-12-09T09:19:33

You can use a calculated member to aggregate the separate states:

With Member [States].[My Favorite States] AS 'Aggregate({[States].[Illinois], [States].[Wisconsin]})' 
select NON EMPTY {[Measures].[Sales], [Measures].[Shipped Units]} ON COLUMNS, 
  NON EMPTY {[States].[Alabama], [States].[New York], [States].[My Favorite States]} ON ROWS 
from [cubename]

MDX query to use a set but return a single row

1 Answers