CODEWITHSUNDEEP
ssasmdx
Elif Ak
Elif Ak

Reputation: 59

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

Upvotes: 1

Views: 1592

Answers (2)

Danylo Korostil
Danylo Korostil

Reputation: 1484

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]

Upvotes: 0

BICube
BICube

Reputation: 4681

Can you try this query

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

you generally need to use allmembers to be able to display the All member. If that didn't return the All member, then you need to calculate it as such 

WITH Member [Customer].[Gender].[All Genders] AS [Customer].[Gender].&[M] + 
                                                 [Customer].[Gender].&[F] 
SELECT
NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,
NON EMPTY { ([Customer].[City].[City] * [Customer].[Gender].allmembers ) } ON ROWS
FROM [Analysis Services Tutorial]

Upvotes: 1

Related Questions