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.

SQL 2005 SP3 has landed

SP3 for SQL 2005 is now available for download.

Included are a number of Bug Fixes , performance enhancements for using SSRS in SharePoint Integration mode, improved rendering of PDF fonts in SSRS, support for SQL 2008 Dbs with SQL 2005 Notification Services and support for Teradata Dbs as a data source for Report Models.

This Service Pack is cumulative and can be run on any version of SQL 2005.

Reporting Services: Passing MultiValue Parameters

I recently came across an article by Wayne Sheffield on SQL Server Central which contained a very neat idea for passing multi-value parameters from SSRS to a SQL stored proc by using XML.

Because SQL stored procs can't handle arrays, it can't handle parameters with multiple values. There are a few ugly ways around this of course by using delimiters and manipulating strings but that just isn't pretty at all. Wayne's idea is to use XML string parameters.

So SSRS would send a string in the following format:


<root>
       <node>
              <element>element data</element>
       </node>
</root>

It would look something like this:


<customers>
       <customer>
              <customerid>1234</customerid>
       </customer>
</customers>

Wayne has written a bit of code which you can add to your report or create a DLL for which can then be referenced by your report.


Function ReturnXML(ByVal MultiValueList As Object, ByVal Root As String, ByVal Node As String
       **************************************************************************
       Returns an XML string by using the specified values.
        Parameters:MultiValueList - a multi value list from SSRS
        Root, Node, Element - String to use in building the XML string
        **************************************************************************
        Dim ReturnString = ""
        Dim sParamItem As Object
        ReturnString = "<" & Root & ">"
        For Each sParamItem In MultiValueList
               ReturnString &= "<" & Node & "><" & Element & ">" & Replace(Replace(sParamItem,"&","&"),"<", "<") & ""
       Next
       ReturnString &= ""
       Return (ReturnString)
End Function

This code would be referenced in your Reporting Services parameter like:

ReturnXML(Parameters!MultiValue.Value, "Customers", "Customer", "CustomerId")

To then use your XML parameter within the stored proc:

Select CustomerId, CustomerName, ActiveFlag
From tCustomer a
INNER JOIN @ipCustomerList.nodes('/Customers/Customer') AS x(item) ON a.CustomerId = x.item.value('CustomerId[1]', 'integer')


Pretty handy no?