How to get an "(all)" value for a level in a user defined hierarchy in SSAS/MDX

I am trying to work out how I can get a total for a level in a user defined hierarchy. For example if I do (using Adventure works here):

SELECT [Measures].[Internet Sales Amount] ON 0,
([Date].[Calendar Year].ALLMEMBERS * [Date].[Month of Year].ALLMEMBERS) ON 1
FROM [Adventure Works]

I get totals for each pair of year/month values.

How can I rewrite this query using the Calendar user hierarchy in the Date dimensions? Eg something like:

SELECT [Measures].[Internet Sales Amount] ON 0,
([Date].[Calendar].[Year] * [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]

Solution

You can use

SELECT [Measures].[Internet Sales Amount] ON 0,
Hierarchize([Date].[Calendar].[Calendar Year] + [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]

This will show you each year containing its value (which is the sum of the months contained in it) before the months.

The + is the short form of Union in MDX, and Hierarchize sorts a set in hierarchical order, which means parents before children.

And if you want something similar to your first result, the closest thing I can imagine would be via calculated measures like this:

WITH Member Measures.Year AS
            IIf([Date].[Calendar].CurrentMember.Level IS [Date].[Calendar].[Calendar Year],
                [Date].[Calendar].CurrentMember.Name,
                Ancestor([Date].[Calendar].CurrentMember, [Date].[Calendar].[Calendar Year]).Name
               )
     Member Measures.Month AS
            IIf([Date].[Calendar].CurrentMember.Level IS [Date].[Calendar].[Month],
                [Date].[Calendar].CurrentMember.Name,
                'All Months'
               )
SELECT {
        Measures.Year,
        Measures.Month,
        [Measures].[Internet Sales Amount]
       }
       ON 0,
Hierarchize([Date].[Calendar].[Calendar Year] + [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]

Note that calculated members need not return numbers, they can as well return strings.