Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Analysis Services (2000)  AS2005 NonEmptyCrossJoin queries return "(null)"

Author  Topic  rdwahl Starting Member 2 Posts Posted - 2005-08-08 : 11:03:36 Hi! I'm hoping someone out there can help me out.... I have query that returns "(null)" against a AS2005 database (migrated from AS2000 using the Migration Wizard) yet runs fine in AS2000. The query returns a single, aggregate value via a calculated member. Syntax: WITH MEMBER Measures.MyCell AS 'Sum({NonEmptyCrossJoin({set expr, set expr]})}, [Measures].[measure]) ' SELECT FROM [cube] WHERE (Measures.MyCell) Sample: WITH MEMBER Measures.MyCell AS 'Sum({NonEmptyCrossJoin({[Branch].[Branch].&[DEN]},{[Data Version].[Data Version].&[Actuals]})}, [Measures].[Amount YTD]) ' SELECT FROM [Finance] WHERE (Measures.MyCell) Note: the same query using Crossjoin works fine in both AS2000 and AS2005: WITH MEMBER Measures.MyCell AS 'Sum({CrossJoin({[Branch].[Branch].&[DEN]},{[Data Version].[Data Version].&[Actuals]})}, [Measures].[Amount YTD]) ' SELECT FROM [WRI] WHERE (Measures.MyCell) however I prefer to use NonEmptyCrossJoin for performance reasons. Note: I have run NonEmptyCrossJoin queries against other dimensions, other members, and other cubes (with and without calculated measures) and get null every time. Other MDX queries work fine. All aggregations (relationships) are in place and working. Any ideas?? Thanks for the help