SQL Server Reporting Services with multi-value parameters

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s