CODEWITHSUNDEEP
ssasmdx
nzyme
nzyme

Reputation: 185

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]

Upvotes: 2

Views: 938

Answers (2)

FrankPl
FrankPl

Reputation: 13315

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.

Upvotes: 2

Rick
Rick

Reputation: 1755

Using allmembers as you have it should return you the ALL line and then each year and each month. If you are interested in only the all line you can change you query to:

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

Notice how it is specifying only the Dimension and the hierarchy.

If you wanted to get the data without the all line use the following:

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

Upvotes: 1

Related Questions