SQL Server Reporting Services with multi-value parameters
March 31st, 2005 by alpriest
Sometimes you need to pass multiple parameters into SQL Server Reporting Service.
Unfortunately Microsoft didn’t include this functionality in the first release, although they’re rumoured to be looking into this. This means that if you want to include a bunch of parameters and pass them through to an SQL ‘IN’ statement it’s a little awkward, but not impossible.
You pass through all the items as a delimited string, then in your stored procedure convert the delimited list to a table using a user-defined function. Details of how to do this part are given in this article from 4guysfromrolla.
In SQL Server Reporting define a report parameter and pass this through to your Stored Procedure through the data source mapping.
When you view the report you’ll see a textbox letting your user enter a value. They can enter a single value now, or multiple delimited values. Neat. Or you could automate the process and invoke reporting service from your app passing the parameters via the URL.