A couple of colleagues of mine were working on a complicated reporting services project; they had one report which had a parameter which allowed a user to select multiple values of numbers, the problem they were facing was how pass the selected values into the query.
They were looking for a way within SQL Server to split a string by a delimiter. As they were using a stored procedure to access the data and multiple value parameters, regardless of parameter type, are passed in as a string of comma separated values.
One thing which they weren’t aware of was that with a multiple value parameter, the behaviour can differ between how the data is being retrieved i.e.: stored procedure or embed sql statement.
If the data set is using direct query to gather the data then the multi value parameter can be used in the query with a IN clause like so:
Where column1 in (@pMultiValueParam)
What happens is that before the query is sent to the SQL Server the query the report server substitutes the @pMulitValueParam with a comma separated list which makes it valid in clause like so:
Where column1 in (10, 3, 4, 56)
Also it has been blogged (http://www.socha.com/blogs/john/2009/03/tfs-report-issues-with-sql-server-2008.html) that in reporting services 2008 there is a new change in the behaviour. You could see the following message when you have no values to select:
Incorrect syntax near ')'.
When there are no values to pass, Reporting Services 2008 simply removes @pMultiValueParam, so you get something like "Where column1 in ()" preventing this query from running.
The solution is to add an expression to the pMultiValueParam report parameter. The expression is evaluated in order to determine what is passed to the query.
Like so:
=IIF(Parameters! pMultiValueParam.Count > 0, Parameters!pMultiValueParam.Value, "")