MDX Returning Member Properties

  • The following query returns a list of customers I am interested in:

    select{[Measures].[Net Value]} on 0,

    NON EMPTY [Customer].[Customer].[Customer].Members on 1

    from (select (

    [Transaction Date].[Date].&[2013-01-25T00:00:00]

    ) on 0 from [Invoice And Credits])

    In my customer dimension I have several member properties which hold address information for the customers. I need to be able to include them in the results and have tried this:

    with member [Measures].AddressLine1

    as [Customer].[Customer].Properties( "Address Line 1" )

    select{[Measures].[Net Value],AddressLine1} on 0,

    NON EMPTY [Customer].[Customer].[Customer].Members on 1

    from (select (

    [Transaction Date].[Date].&[2013-01-25T00:00:00]

    ) on 0 from [Invoice And Credits])

    This returns all customers in my dimension (see image attachment SSAS2.png) - How can I include the member property?

  • I think you are asking for the same rows values as the first query, without all the NULLs. The NON EMPTY instruction any row that contains at least 1 non null column. In you case the address line 1 fits that criteria so all customers are now valid.

    If you change to use the NONEMPTY function instead then you can target a measure when checking for NONEMPTY values as below:

    with member [Measures].AddressLine1

    as [Customer].[Customer].Properties( "Address Line 1" )

    select {[Measures].[Net Value],[Measures].AddressLine1} on 0,

    NONEMPTY ([Customer].[Customer].[Customer].Members,

    [Measures].[Net Value]) on 1

    from (select (

    [Transaction Date].[Date].&[2013-01-25T00:00:00]

    ) on 0 from [Invoice And Credits])

    Fitz

  • That's fixed it! Many thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply