Multi-value Parameters in SQL Server Reporting Services

One of my favorite features of SQL Server Reporting Services is the ability to provide a parameter with multiple values. This is a great way to give your users flexibility at runtime by allowing them to select one, two, several, or all values from a given list. It even has a built-in way to easily select and de-select all the possible values. A common scenario that would benefit from this is when you are going to publish a single report that could potentially be used by multiple locations or departments.

Looking for more Reporting Services?

Because it’s always a good practice to have SSRS reports use SQL stored procedures for datasets, we need to have a good way to handle this unique type of parameter. This is where it can get tricky: while a single varchar parameter value from an SP is not a problem for SSRS, when you provide more than one (typically with an IN statement followed by several comma-separated values), the list gets passed to SQL as a delimited string. So now we must parse it apart into separate values while handling commas, single quotes, etc.

T-SQL Code

The first part of this solution we create with a table-valued function. User-defined functions let us create utility procedures in SQL that we can reuse many times. In this case, we have one that presents a delimited list of string values as a table where each value gets its own row. This makes it MUCH easier to work with and we don’t have to worry about parsing, handling quotes or datatypes. Once you create this function (and we always recommend creating your code in your own database on the DR server, not in the livefdb or livendb) it’s built into your database so it can be easily referenced from other stored procedures.

Our function uses XML to parse any list you pass into unique row values. (It assumes that the delimiter is a comma which is what comes out of SSRS by default.)  So now the strategy is to call this function from the stored procedure that produces your main dataset using our parameter and return the list to populate the parameter value. Then we can use a simple IN clause to get all those values.

Let’s use a simple discharge report from ABS as an example. Say we want to allow our users to select one or many location mnemonics as the parameter to filter a list of discharged patients for only those locations. Our main report stored procedure looks like this:

The LocationID parameter must be large enough to hold all the possible values you might pass to it; hence the varchar(max). Finally note how we refer to the function in the WHERE clause with the inner select statement.

For this example, we also need a list of all possible parameter values, in this case, location mnemonics. The best approach is to create another stored procedure that simply queries the active locations from your MIS dictionary:

Now we have 3 code objects to use for this example:

1. A stored procedure (spMisLocation) to build a list of unique locations, to be used a parameter pick-list;
2. A function (fnParseMultiParameter) to parse a string of parameter values, whether there is just one or many, and;
3. A stored procedure (spDischargeByLocation) that will be the basis for our finished report, that references the function (#2) to populate its required @LocationID parameter.

Reporting Services - Visual Studio

Let’s see how we refer to these items in our report itself, from SQL Server Data Tools / Visual Studio. I’ll assume you know the basics of building an .rdl report in either Visual Studio or Report Builder. We’ll focus on the datasets that reference our code objects above.

First will be the dataset that’s provided by spMisLocations – our list of locations. It’s pretty simple; here are its properties:

We haven’t changed any defaults here, just specified spMisLocation as our populating procedure.

Second is our “main” dataset, that is, the one that returns the fields of interest on the report. Ours here is greatly simplified, it’s called dsDischarges and here are its properties:

It’s using the spDischargeByLocation procedure for its fields. Additionally, it has the LocationID parameter specified. We’ll need to adjust the properties for this parameter to use the multi-value feature: right-click on the LocationID parameter from the Report Data tab:

From the General tab, check the box to allow multiple values.

Next click the “Available Values” tab and fill it in as below. What we’re doing here is defining the list of values we’ll use to present to the user to select when the report is run, using the dsLocations dataset (which, remember is populated by spMisLocation).

When you’re done, go ahead an preview your report. If all is well, you should see a drop-down list of locations, which you can select from freely.

As you can see, with a little planning you can build your reports now to leverage multiple parameters, which make them more flexible for your users. In addition, using stored procedures with lookups to MEDITECH application dictionaries are a great way to build parameter lists.  Happy coding!