Search code examples
mdxsubtotal

MDX using allmembers with [all] for subtotals

I would like a result that looks like

dim1 dim2 dim3
all  all  all
1    all  all
1    2    all
1    2    3
2    2    all

If i use both allmembers and [all] i get something about not being able to crossjoin the two.

I have tried the following where i would like subtotals on the Areas and Types dimensions.

SELECT 
 NON EMPTY { [Measures].[Cost] } ON COLUMNS, 
 NON EMPTY {( 
 [Areas].[Area].[Area].ALLMEMBERS
 * [Areas].[Area].[All]
 * [Types].[Type].[Type].ALLMEMBERS 
 * [Types].[Type].[All] 
 * [Years].[Year].[2011] : [Year].[Year].[2018]
 )} ON ROWS 
 FROM [Cube]
WHERE ([Departments].[Department].&[6])
Solution

  • Try the following: 

    Select 
[Measures].[Cost] on 0, 
Non Empty {
            [Areas].[Area].Members *
            [Types].[Type].Members *
            {[Years].[Year].[2011]:[Year].[Year].[2018]} 
} on 1
From [Cube]
Where ([Departments].[Department].&[6])

    Also you may read my blogpost for more usages.