Report development is one of my favorite areas of Business Intelligence and I have to say I’ve been fairly happy with the new interface for SSRS 2008. Except for when it comes to MDX queries.
Off the top of my head I can think of three major issues with the way SSRS 2008 handles MDX queries. To be fair, only one of those issues is related to 2008. The other two issues, which I’m saving for another blog post (no, I’m not even going to hint at them!), are related to SSRS in general, not 2008 specifically.
So what issue am I talking about? Well, if you’ve ever developed a report in SSRS 2008 that used a parameterized MDX query then I’m pretty sure you’re familiar with it: after switching to MDX mode, making and saving changes to the report query, the MDX Query Designer overwrites the parameter datasets and wipes out all changes made to these parameter datasets.
If you define a parameter in your MDX query, SSRS 2008 will automagically create the parameter, and its dataset, for you. I have to admit, that’s pretty slick. Unless the dataset already exists, in which case it gets overwritten. In fact, anytime you modify an MDX query that references the parameter, the dataset will get overwritten. If you’re using the generic dataset then this isn’t a problem. But if you’ve customized the dataset in any way, then it gets annoying fast. Really fast. Can you imagine having to update an MDX query that referenced several parameters? And can you imagine having to make multiple tweaks to said MDX query, just so it would operate just right?
After suffering a mild aneurism I finally said Enough is Enough. Something must be done. And, thanks to Teo, something has been done. Well, kinda-sorta-not really. There is a bug listed on the Microsoft Connect site but due to introducing backward-compatibility issues, Microsoft is unable to fix this behavior in the current release. However, they have promised to fix the behavior in a future release. (Has it been fixed in R2?)
In the meantime, you can perform the workaround described below. But be warned, it involves manually editing the XML code, so you should probably make a backup of your RDL in case things go horribly awry.
How to Prevent SSRS 2008 from Overwriting Datasets:
-
Open the report in XML mode. There are two ways to do this: One way is to navigate to the RDL file in Windows Explorer, right click on the RDL, and select Open With… and then choose Notepad. The second way is to open the report in BIDS, right click on the report in the Solution Explorer, and select View Code.
-
Do a search on “<Dataset>”, and add “<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>” to the “<Query>…</Query>” tags, as shown below. Just to be on the safe side, I added this line to all of my datasets.