Grouping SSAS members into buckets using MDX

Question

SSAS and MDX newbie here.

If I have an SSAS cube with Geography, Product as the dimensions and total sales as a measure.

The following elements are under geography:

EAST
WEST
NORTH
SOUTH
MID-ATLANTIC
MOUNTAIN
NORTH-WEST

Products are:

1
2
3
4

For Product ID = 1, is there a way I can group some members into a "rest of the country" bucket and aggregate the sales?

Meaning, the expected output is:

Product ID    Geography          Sales

1             East              100
1             West              200
1             North             300
1             South             400
1             RestOfNation      1200

2             East              100
2             West              50
2             RestOfNation      1500

Is there a way I can buck some members into "RestOfNation" using an MDX query?

Answer 1

You could try something like this.

1. Create a named set made up of the members that you'd like to throw into the bucket RestOfWorld.
2. Make a member, within the geography dimension, that is the aggregation of the rest of world members.
3. Find the countries that are not in the rest of the world set.

Here is a script against AdvWrks (worth installing as it is a common prototyping tool when playing with mdx and posting to forums):

WITH 
  SET [RestOfWorld] AS 
    {
      [Customer].[Customer Geography].[Country].&[United Kingdom]
     ,[Customer].[Customer Geography].[Country].&[Germany]
    } 
  MEMBER [Customer].[Customer Geography].[All].[RestOfWorld] AS 
    Aggregate
    (
      {
        [Customer].[Customer Geography].[Country].&[United Kingdom]
       ,[Customer].[Customer Geography].[Country].&[Germany]
      }
    ) 
  SET [CountriesMinusROW] AS 
    [Customer].[Customer Geography].[Country].MEMBERS - [RestOfWorld] 
SELECT 
  NON EMPTY 
    {[Measures].[Internet Sales Amount]} ON 0
 ,NON EMPTY 
      [Product].[Category].[Category]
    * 
      {
        [CountriesMinusROW]
       ,[Customer].[Customer Geography].[All].[RestOfWorld]
      } ON 1
FROM [Adventure Works]
WHERE 
  [Date].[Calendar Year].&[2007];

The above gives the following cellset: