September 24, 2013 at 8:46 am
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?
September 25, 2013 at 12:12 pm
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
September 26, 2013 at 2:13 am
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