SSAS: Changing Object Id's Breaks Report Builder Reports

Ugh. Found this one out the hard way. Usually when you change underlying object id’s in SQL Server Analysis Services, it shouldn’t cause any harm. You might have some XMLA to process dim’s and measure groups, if so you would have to change those, etc. But all reporting services reports and excel 2007 pivot tables, and MDX should keep working. What breaks? Report Builder reports.

You can build reports with Report Builder (the link is in Reporting Services to open Report Builder) off a cube model. They are paired down reports, you can’t do as much as you can with SSRS, but for advanced end users, they do the trick.

Thing is, the use SemanticQuery XML behind the scenes for the query and data source is to the model, and the XML is build off the object id’s of the cube. Ugh again. Even worse is that all parameters that were set as drop down lists (in this list) type are converted to IN formulas, and all the other params are converted to formulas. Graphs break. Matrix Reports break. Tabular Reports break. It just sucks. They shouldn’t build the query off the underlying id’s of objects, they should build them off the displayed names, like everything else does. Whew 🙂