Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Measure: Calculate customers for a city

Answered Measure: Calculate customers for a city

  • Sunday, December 30, 2012 5:34 PM
     
     
    Sign In to Vote
    0

    Hi everybody, I need some help in creating a measure in MDX.

    My customer dimension looks like that:

    |-State
    |---City
    |-----Street
    |--------Street number
    |-----------Customer

    So for example

    |-Saxony
    |-Bavaria
    |---Munich
    |-----Example-Street
    |-------1
    |-----------Customer A
    |-------2
    |-----------Customer B
    |-----------Customer C
    |-------3
    |-----------Customer D

    Now I'd like to create a measure, that gives me to every level the amount of customers, for example '4' for Munich (and something like 10 for Bavaria).

    I read about the count function but can't seem to make it work properly. Could somebody tell my how to implement this feature? :)

    Regards and best wishes,
    Sonnfan

     

     

All Replies

  • Sunday, December 30, 2012 7:24 PM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    The following definition should work.

    CREATE MEMBER CurrentCube.[Measures].[CustomerCount] AS
	COUNT(Descendants( [Customer].[Hierarchy].CurrentMember, [Customer].[Hierarchy].[Customer] ) )

    HTH, Martin

    http://martinmason.wordpress.com


     
  • Tuesday, January 01, 2013 5:49 PM
     
     Answered
    Sign In to Vote
    0

    Hey, thanks for your suggestion. I was able to solve the Problem with the following code:

    with member [Measures].[Amount] as 'sum(descendants([Customer].currentmember, [Customer].[Name]),1) '

    ...

    Regards,
    Sonnfan