Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX Returning Member Properties Expand / Collapse
Author
Message
Posted Tuesday, September 24, 2013 8:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:53 AM
Points: 18, Visits: 234
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?


  Post Attachments 
SSAS2.png (4 views, 24.31 KB)
Post #1497918
Posted Wednesday, September 25, 2013 12:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:42 PM
Points: 3,103, Visits: 1,343
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


Post #1498527
Posted Thursday, September 26, 2013 2:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:53 AM
Points: 18, Visits: 234
That's fixed it! Many thanks
Post #1498713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse