CODEWITHSUNDEEP
ssasmdx
whytheq
whytheq

Reputation: 35557

NONEMPTY behaviour seemingly not limiting results

The following script is from this MSDN article http://msdn.microsoft.com/en-us/library/ms145988.aspx

SELECT 
  [Measures].[Internet Sales Amount] ON 0, 
  NONEMPTY( 
     [Customer].[Customer].[Customer].MEMBERS,
     {([Date].[Calendar].[Date].&[20010701], [Measures].[Internet Sales Amount])} 
  ) ON 1 
FROM [Adventure Works]

I basically took the script and replaced the elements from the above with equivalent elements from our cube.

What would be the reason that my script is not limiting it's results to the date specified in the NONEMPTY function but is returning all customers and their lifetime sales amount?

Upvotes: 0

Views: 84

Answers (2)

KrazzyNefarious
KrazzyNefarious

Reputation: 3230

Try this:

SELECT 
[Measures].[Internet Sales Amount] ON 0, 
NONEMPTY( 
[Customer].[Customer].[Customer].MEMBERS
) ON 1 
FROM [Adventure Works]
where
[Date].[Calendar].[Date].&[20010701]

Upvotes: 1

Hari Tallam
Hari Tallam

Reputation: 78

All the customers might have a non-null internet sales amount for the date you have specified. Moreover the nonempty will just filter out those members where the condition is not met. In your case as all the customers has non-null Internet Sales value, none of the customers are filtered out. And the Internet sales amount in the axis 0 will not be limited to the date that is specified, instead it returns the sales amount for each customer across all the dates.

Upvotes: 1

Related Questions