When modifying a drop down selector in a SSRS report to allow a user to select multiple values I noticed all other reports which provided this functionality had the accompanying SQL query embedded within the report. I thought this was strange as the majority of the other SSRS reports I had looked at used stored procedures.
After a little bit of research I found the following:
"The data source cannot be a stored procedure. Reporting Services does not support passing a multi-value parameter array to a stored procedure." Essentially, SSRS passes the multi-value parameter as a comma separated string to a stored procedure.
Note: If the SQL query is embedded within the report, this format works perfectly fine: "IN ('Item 1', 'Item 2', 'Item 3')"
All is not lost if you do not want to embed your SQL within the SSRS report. If you read this excellent article written by David Leibowitz Puzzling Situations: Using MultiValue Parameters with Stored Procedures in SSRS, it is possible (and quite straight-forward) to write an SQL function that will convert the comma separated string into a table. The table can then be enumerated using the IN keyword within your stored procedure.
Happy days!
