asked
MDX issue - the set must have a single hierarchy to be used with the complement operator
Hi all I am having an issue with a mdx query powering an ssrs drop down (see script below). It returns the attached error message for a minority (i think) of users. There is also cube based data segregation on the same dimension members based on their ad groups. WITH MEMBER [Measures].[ParameterCaption] AS [Dim Office Current].[Office Name].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Dim Office Current].[Office Name].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Dim Office Current].[Office Name].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue] , [Measures].[ParameterLevel]} ON COLUMNS , [Dim Office Current].[Office Name].CHILDREN ON ROWS FROM ( SELECT ( -{ [Dim Office Current].[Office Name].[All].UNKNOWNMEMBER} ) ON COLUMNS FROM [TRANSACTION_REFERRALS]) thanks in advance for your help ![alt text][1] [1]: /storage/temp/583-image001.jpg
think i may have found a work around using except statement instead of restricted from WITH MEMBER [Measures].[ParameterCaption] AS [Dim Office Current].[Office Name].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Dim Office Current].[Office Name].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Dim Office Current].[Office Name].CURRENTMEMBER.LEVEL.ORDINAL SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue] , [Measures].[ParameterLevel]} ON COLUMNS ,Except ([Dim Office Current].[Office Name].CHILDREN , {[Dim Office Current].[Office Name].[All].UNKNOWNMEMBER} ) ON ROWS // , [Dim Office Current].[Office Name].CHILDREN ON ROWS //FROM ( SELECT ( -{ [Dim Office Current].[Office Name].[All].UNKNOWNMEMBER} ) // ON COLUMNS FROM [TRANSACTION_REFERRALS] this feels like a bug to me, have found some simiar references to TFS that were patched with a download from msoft. solution here
http://blogs.socha.com/2009/03/tfs-report-issues-with-sql-server-2008.html
10 People are following this question.
