Tuesday, 20 May 2008

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:

              <element>element data</element>

It would look something like this:


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,"&","&"),"<", "<") & ""
       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?


Anonymous said...

I've implemented this and compared it to the query run as text and the performance was unacceptable. It was nearly three times as long to use the XML join in the stored procedure.

NicMeneses said...

This method of passing multiple parameters to a complex procedure improved performance on our database significantly. Try it and test for your query before discounting this method.

Anonymous said...

To do this in a SP try this

where charindex( productname , @ProductIDs,1) > 0

-- Link @ProductIDs to your Report multivalue Parameter