Search code examples
ssasmdx

MDX query "with member" and sum problem

I have a query like 

   WITH MEMBER [measures].[Count] AS 
   SUM(([Location].[Hierarchy].[Zone].[1].Children),[Measures].[Length])
   SELECT {[measures].[Count]} ON 0,
   {[Location].[Hierarchy].[Zone].&[1].Children} on 1
   FROM [NTAP]

I'm a real beginner with MDX but from my understanding this should get me a list with all Zone 1:s children and a sum of all those children Length summarized. The problem is I get a list with the children and a sum of all Zone 1:s Length?

I get this:

1 103026769420
2 103026769420
3 103026769420
4 103026769420

But what I would like to get is something like this

1 84984958
2 9494949
3 934883
4 9458948588

Location is a hierarchy like:

Zone Children

1
   1
   2
   3
2
   1
   2
   3

edit: should probably say that the reason I use with member is that the measure.length will with a Iif in the final version. But I cant even get this working :(

edit2: fixed spelling

Solution

  • You are getting the sum of all children of Zone 1 for each child of Zone 1.

    You can rewrite it as:

    WITH MEMBER [Measures].[Count] AS 
   SUM([Location].[Hierarchy].CurrentMember.Children, [Measures].[Length])
SELECT {[Measures].[Count]} ON 0,
   {[Location].[Hierarchy].[Zone].&[1].Children} on 1
FROM [NTAP]

    By the way, [1] <> &[1]. Without the & you are specifying the name and with - the key. If in your case key = name you have nothing to worry about.