question

DataArchitect avatar image
DataArchitect 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
ssrsssasmdx
image001.jpg (26.1 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
DataArchitect avatar image
DataArchitect answered
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 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.