Reporting Services (SSRS/MSRS) 2008 Error: Set used with the complement operator must have all members from the same level
When you use the Not In operator in a SSRS 2008 MDX query filter to exclude a named set, it uses a the complement operator in the constructed MDX. This is fine as long as "all members [are] from the same level." Since you got this error, they are not 
You can get around this by using the Except() MDX function instead of letting SSRS use the Complement operator
In the ReportServerService log, you'll see something like this:
Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: Query (..., ...) Set used with the complement operator must have all members from the same level.
Original filter
Dimension: Time
Hierarchy: Calendar Date
Operator: Not In
Filter Expression: [Today]
New filter
Dimension: Time
Hierarchy: Calendar Date
Operator: MDX
Filter Expression: Except([Time].[Calendar Date].[Calendar Date].MEMBERS, [Today])
Searching for keywords in all Reporting Services (SSRS) reports
During impact analysis for any changes to existing database tables, cube dimensions, cube measures, etc, it's nice to know which reports are going to horribly break before your end-users let you know about it All of the rdl content for reports uploaded to SSRS is stored in the ReportingServices database in the Content column of the Catalog table (in binary, of course) so here's what I came up with to get the list of soon to be broken reports:
SELECT [Path], ContentText FROM ( SELECT [Path], cast(cast([content]AS varbinary(8000)) AS varchar(8000)) AS [ContentText] FROM [catalog] cat WITH(nolock) WHERE [type]=2 ) a WHERE ContentText LIKE '%ColumnName%' OR ContentText LIKE '%columnname%' OR ContentText LIKE '%MeasureName%' OR ContentText LIKE '%AttributeName%' OR ContentText LIKE '%etc...%'
Social Networks
What I'm Doing...
- @scaleovenstove @jbollinger let's do it. "BItter" ;-) in reply to scaleovenstove 3 weeks ago
- @joekieffer good luck! in reply to joekieffer 2010-07-10
- @ben_p bah, i'm proud of him - that's a ridiculous name to have on a resume. give him at least 2 years before you start the peer pressure in reply to ben_p 2010-06-11
- @michaelfransen ha, I was pretty sure u were ignoring me ;-) ty. in reply to michaelfransen 2010-06-11
- Last day at W3i today - moving to Trek Bicycle Corp! 2010-06-11
- More updates...
Recent Posts
- Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter
- Locating Rampant Database Growth
- Script sp_configure Output To Migrate Settings Between SQL Servers
- Script RESTORE DATABASE … WITH MOVE Stub
- VersionOne Nag: Reminding Us To Burn Our Points