Answered by:
CrossJoin doesn't work with Muliple filters in subcube.
Question

I have an .aspx page that generates some MDX. It works great unless I want to use multiple filters on the data AND multiple "views" (crossjoin) of the data. This is no doubt best illustrated by example.
here is the query that does not work. It does not error, but it returns 0 for a rowcount, which is unexpected (ie: incorrect):
WITH SET MyRows AS NONEMPTY( NONEMPTYCROSSJOIN ([Advertiser Dimension].[Product].CHILDREN, [Time Dimension].[Date].CHILDREN), {[Measures].[Clicks],[Measures].[Leads]} ) MEMBER [Measures].[Count] AS Count(MyRows) SELECT { [Measures].[Count] } ON AXIS(0) FROM ( SELECT ({[Time Dimension].[Hour].&[20100202T00:00:00] : [Time Dimension].[Hour].&[20100225T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP] );
WITH SET MyRows AS NONEMPTY( NONEMPTYCROSSJOIN ([Advertiser Dimension].[Product].CHILDREN, [Time Dimension].[Date].CHILDREN), {[Measures].[Clicks],[Measures].[Leads]} ) MEMBER [Measures].[Count] AS Count(MyRows) SELECT { [Measures].[Count] } ON AXIS(0) FROM ( SELECT ({[Time Dimension].[Hour].&[20100202T00:00:00] : [Time Dimension].[Hour].&[20100224T23:00:00]}) ON AXIS(0) FROM [Filinet OLAP] );
And, on the other hand, this is a query that does not do a crossjoin, but does have multiple filters in the subcube. This query also works, illustrating that, singly, my crossjoin technique works, and my filtering technique works. It's only when combined that they don't work together. If anyone can explain why, and better yet, offer a solution that works, I'd be much appreciative:
WITH SET MyRows AS NONEMPTY( [Time Dimension].[Date].CHILDREN, {[Measures].[Clicks],[Measures].[Leads]} ) MEMBER [Measures].[Count] AS Count(MyRows) SELECT { [Measures].[Count] } ON AXIS(0) FROM ( SELECT ({[Time Dimension].[Hour].&[20100202T00:00:00] : [Time Dimension].[Hour].&[20100224T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP] );
Thanks!Wednesday, February 24, 2010 9:37 PM
Answers

Query scoped named sets, like the one in your first query, are resolved once in the context of the WHERE clause, before any subselects are evaluated. If you want to calculated in the context of subselects you will need to put the set definition inline in the calculation.
eg.
MEMBER [Measures].[Count] AS Count(NONEMPTY(
[Advertiser Dimension].[Product].CHILDREN
* [Time Dimension].[Date].CHILDREN),
* {[Measures].[Clicks],[Measures].[Leads]}
))
SELECT
{ [Measures].[Count] } ON AXIS(0)
FROM (
SELECT ({[Time Dimension].[Hour].&[20100202T00:00:00]
:
[Time Dimension].[Hour].&[20100225T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP]
);
PS. I have also replaced the NonEmptyCrossjoin() function with the * operator as NonEmptycrossjoin() is a depreciated function and can be replace with normal crossjoins and the NonEmpty() function.
http://geekswithblogs.net/darrengosbell  please mark correct answers Marked as answer by Tab Alleman 2 Tuesday, March 2, 2010 7:55 PM
Thursday, February 25, 2010 3:02 AM
All replies

Hmm, on further investigation, I'm finding that my MDX is formed correctly, and the problem may be in the data. I'll mark this resolved if I figure it out completely.Wednesday, February 24, 2010 10:25 PM

Query scoped named sets, like the one in your first query, are resolved once in the context of the WHERE clause, before any subselects are evaluated. If you want to calculated in the context of subselects you will need to put the set definition inline in the calculation.
eg.
MEMBER [Measures].[Count] AS Count(NONEMPTY(
[Advertiser Dimension].[Product].CHILDREN
* [Time Dimension].[Date].CHILDREN),
* {[Measures].[Clicks],[Measures].[Leads]}
))
SELECT
{ [Measures].[Count] } ON AXIS(0)
FROM (
SELECT ({[Time Dimension].[Hour].&[20100202T00:00:00]
:
[Time Dimension].[Hour].&[20100225T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP]
);
PS. I have also replaced the NonEmptyCrossjoin() function with the * operator as NonEmptycrossjoin() is a depreciated function and can be replace with normal crossjoins and the NonEmpty() function.
http://geekswithblogs.net/darrengosbell  please mark correct answers Marked as answer by Tab Alleman 2 Tuesday, March 2, 2010 7:55 PM
Thursday, February 25, 2010 3:02 AM 
Ok, I noticed in my data that for the parameters I was using, I would have an empty [Measures].[Clicks] measure. I changed the parameters in my subselect such that both the [Clicks] measure and the [Leads] measure would have values, and I saw the expected results.
So apparently NONEMPTY doesn't work the way I think it does. Based on my test, it is only returning rows where both [Clicks] AND [Leads] have values.
What I want is to return rows where EITHER [Clicks] OR [Leads] is not empty. Is that possible?Tuesday, March 2, 2010 7:37 PM 
Actually, Darren, replacing the deprecated NONEMPTYCROSSJOIN() with * does solve the problem, even with the parameters where one of the measures is empty, so I think I will go with that.
Thanks! Edited by Tab Alleman 2 Tuesday, March 2, 2010 9:26 PM more specific wording
Tuesday, March 2, 2010 7:55 PM