update: Chris Webb and Mosha commented and made it clear that the reason for this error wrap is the NonEmptyCrossJoin function and nothing else. I also checked and I did not find any other function that wraps underlying errors.
This is something you need to be aware of when you're writing MDX. I don't know whether it's a bug or by-design. I'll be happy
to know (please comment if you know something that I don't).
Consider the following MDX:
SELECT
NonEmptyCrossJoin
(
[Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
,[Employee].[Employee Department].[Department].&[Sales]
) ON 0
FROM [Adventure Works];
The query will return with this error:
The Set_Count argument of the NonEmptyCrossJoin function is either negative or larger than the number of sets provided. This is quite reasonable because I written the second argument as a member, where (NonEmpty)CrossJoin expects only sets. So, let's upgrade this member to a set:
SELECT
NonEmptyCrossJoin
(
[Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
,{[Employee].[Employee Department].[Department].&[Sales]}
) ON 0
FROM [Adventure Works];All I did is wrapping the second argument with {} and we have a set. The query will return 19 columns.
Now, for the interesting part. Let's count the members of this CrossJoin before the we fix it:
WITH
MEMBER [a] AS
NonEmptyCrossJoin
(
[Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
,[Employee].[Employee Department].[Department].&[Sales]
).Count
SELECT
[a] ON 0
FROM [Adventure Works];This return... 0.
Where is the error?My guess is that the Count function wraps the error. The NonEmptyCrossJoin returns null and the count of members in null is zero. The meaning of this is that if you'll ever forget to wrap the member with {} you'll always get zero and not an error. This can be very dangerous. Just for the check, running this query after the fix:
WITH
MEMBER [a] AS
NonEmptyCrossJoin
(
[Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
,{[Employee].[Employee Department].[Department].&[Sales]}
).Count
SELECT
[a] ON 0
FROM [Adventure Works];will return 19. This been tested with both SSAS 2005 and 2008 (RC0). The examples here are from RC0.
Be careful with your MDX.