Wednesday, 12 January 2011

SSRS: Disappearing MDX Queries

The Reporting Services bug which removes the MDX you've previously typed into the query designer is not a new one. It has been well documented however for the purposes of clarity, I'll review it here too.

You have a SQL 2005 report which uses an SSAS data source. Rather than using the MDX designer, you write your own MDX because you need to do more than the weak-arse SSRS MDX designer can do. All works well you save, deploy, close, whatever. Some time later you come back to it to make a change. You open your Visual Studio project and when you click on the dataset the query is no longer there - you are presented with the blank MDX designer pane as if your query never existed.

It's worth noting that, your query isn't actually gone at this point. Just don't hit SAVE!
If you close the report, right-click it in the Solution and select View Code. Do a search for a tag. You should see your MDX still there.

The common answer directs you to the knowledge base and a particular hotfix which was included in SP1. However, as an SP3 user, I'm currently experiencing the same problem and installing the hotfix solved nothing.

I believe this knowledge base item is no longer relevant because when I look at my report's code-behind by selecting View Code, there is no tag which that hotfix presumes exists.

Googling the issue has also found suggestions like updating your version of SQL Express to the latest service pack. Well, I don't have SQL Express installed at all so it ain't that.

I have had the problem before but I couldn't find a reasonable solution then, as now, either. What did I do to fix it before, you ask? The answer is unfortunately quite painful. I reinstalled BIDS.

Loathe to go through that pain again, I started thinking about what changed since it stopped working this time. Well, loads obviously. But the most obvious change? Installing Visual Studio 2010 Professional.

So here I am installing Visual Studio 2010 SP1 Beta. Yep, beta. That's how desperado I am.

Did it work? I'll tell you as soon as it finishes installing :)

UPDATE: Can't remember what the outcome of this was because at some point I uninstalled VS2010 for some unrelated reason. Having just reinstalled it on Friday, I find that the problem is reoccuring. SP1 Beta is installed however obviously there's no fix there either. Have logged Connect Feedback.

No comments: