Search code examples
ssasmdx

MDX how to get total sum for sub groups?

I have MDX query like following:

SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { ([Customer].[City].[City] * [Customer].[Gender].[Gender] ) } ON ROWS
FROM [Analysis Services Tutorial]

This MDX Query gets following table result as you know:

|---------------------|-----------|---------------|
|        City         |   Gender  |  Sales Amount |
|---------------------|-----------|---------------|
|     Ballard         |     F     |    300        |
|---------------------|-----------|---------------|
|     Ballard         |     M     |    500        |
|---------------------|-----------|---------------|
|     Berkeley        |     F     |    200        |
|---------------------|-----------|---------------|
|     Berkeley        |     M     |    100        |
|---------------------|-----------|---------------|
                      .
                      .
                      .

But I need sub totals in the table like that:

|---------------------|-----------|---------------|
|        City         |   Gender  |  Sales Amount |
|---------------------|-----------|---------------|
|     Ballard         |     F     |    300        |
|---------------------|-----------|---------------|
|     Ballard         |     M     |    500        |
|---------------------|-----------|---------------|
|     Ballard         |    All    |    800        |
|---------------------|-----------|---------------|
|     Berkeley        |     F     |    200        |
|---------------------|-----------|---------------|
|     Berkeley        |     M     |    100        |
|---------------------|-----------|---------------|
|     Berkeley        |    All    |    300        |
|---------------------|-----------|---------------|
                      .
                      .
                      .

Is there any suggestion to do that in MDX? Any help would be appreciated. Thanks

Solution

  • You may add the ALL member:

    SELECT
NON EMPTY [Measures].[Sales Amount] ON COLUMNS,
NON EMPTY [Customer].[City].[City].Members * {[Customer].[Gender].[Gender].Members + [Customer].[Gender].[All]} ON ROWS
FROM [Analysis Services Tutorial]

    Or all members of the Gender hierarchy:

    SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY [Customer].[City].[City].Members * [Customer].[Gender].Members ON ROWS
FROM [Analysis Services Tutorial]