We solved this using a generic function in the Code area that converts a multi-value parameter list to XML, passing the XML to the sproc and then using a generic Transact-SQL table function to convert to a table. It is not that much code and it covers all situations pretty well. By converting the list to a table that can be joined, it eliminates any performance problems associated with large lists.

Here is the code in case it will help anyone else with this common problem:

This goes in the Code area under Report Properties. Note that it also works with parameters that are not multi-valued.

Function StrParmToXML(ByVal obj As parameter) As String

Dim strXML as String

Dim i As Integer

strXML = ""

If obj.IsMultiValue then

For i = 0 To obj.count - 1

strXML = strXML & ""

Next

Else

strXML = strXML & ""

End IF

strXML = strXML & ""

Return strXML

End Function

Here is the SQL table function (SQL 2005 and up)

CREATE function [dbo].[rf_ParameterTable] (@parmxml xml) RETURNS TABLE

AS

RETURN

select

parmxml.item.value('@value', 'varchar(max)') as parm_value,

parmxml.item.value('@label', 'varchar(max)') as parm_label

from @parmxml.nodes('//parameter/item') as parmxml(item)

)