Introduction

This article is a continuation from my previous article: SSRS - Report for Stored Procedures with Parameters. If you have not had a chance, please go check it out, as I am simply going to continue using that setup and report in this article.

Previously, we built a report for Pam that contained multiple parameters that were being passed into a stored procedure. Each parameter had you selecting one value, or manually entering a value, to be passed to a single stored procedure. It also provided a default view when the report is opened that simply returned all data.

Scenario

Pam has come back to let you know that the users are estatic about the report. It is has greatly helped them become much more efficient at getting access to their data. She did have one request for making a modification to the current report. The report currently returns either all of the data or just a subset for one department, they would like the ability to return data on a selection of multiple departments at a time.

The Setup

The request is basically to turn the "Department Name" parameter into a drop down that will let the user select multiple values. The next step we need to take is planning out what areas of our report will need modifications to meet this request. The remainder of this article is going to break down the steps that need to be made, and how to deal with multi-select parameters due to something SSRS does in the background.

Department Name parameter

Per the request, we need to modify this parameter to accept multiple values. This is done by going into the properties of that parameter and simply checking the option, as shown below:

You may notice I unchecked the "Allow null value", and this is because multi-value parameters cannot contain null values. You receive this error if you leave that option checked:

Once you configure the parameter to allow multiple values to be selected, previewing the report will make the parameter look like this now:

.

Now if you happen to just check "Document Control" and "Engineering", then execute the report you are going to see this error show up:

You may recall that we had set this parameter to the data type of integer, but the procedure we are calling has the parameter for DepartmentID set to a smallint. This is dealing with conversion precendence and an integer to smallint is within the boundary of support. However going from nvarchar to smallint is not a supported conversion. This is something that SSRS is doing in the background that you will have no direct control over. Even if you are dealing with a procedure that accepts a string value of nvarchar where you want something like (N'document',N'engineer',N'executive'), SSRS is going to pass it as (N'document,engineer,executive'). There are a few hacks for dealing with this that I have come across, but I am going to go over the most common method (and cleanest in my opinion).

Modify the Stored Procedure

Since SSRS is going to pass a comma-delimited string to our procedure we will have to modify that to accept it. In additon to that the procedure will have to include a way to handle and parse the string, so we can use that list in our query to correctly pull the data out. I mentioned previously about the most common method used, and that is to create a split function. A split function will take an inputed string and parse it into a clean table so it can be used within T-SQL properly.

I take the position of not reinventing the wheel when it comes to things like split functions (among other things). I already know one of the most popular split functions used was written and published on SQLServerCentral by Jeff Moden. So I am going to point you to his article: Tally OH! An Improved SQL 8K “CSV Splitter” Function. The code for the function itself is located at "Figure 21" just under the section titled "Putting it all Together in a New Splitter Function".

Once you have that function deployed and understand it we can modify the procedure. I am going to show you the sections of code as before and after so you clearly see what I am changing. You can download the full code from the resource section at the end of this article. I prefer to create this as a new procedure in the event the original code is needed in the near future. At some point I will archive the code in my library of scripts and permanently delete it from the database but to each his own. I am going to call our new procedure simply: [HumnaResources].[usp_GetLeaveReport_multiID].

usp_GetLeaveReport_multiID

The first thing to modify is going to be our input parameter for the DeptID, @DepartmentID. This originally was a smallint data type and we are going to modify this to be a data type of nvarchar(50), matching up to the data type being passed by SSRS. What you will need to ask yourself is: how long of a string could be passed in from your report?. If the user selected all possible options (select all), will your parameter length handle it?

In this case I know there are currently 16 DepartmentIDs in the [HumanResources].[Department] table. That means if the user selected all the department names and executed the report a string of about 40 characters long would be passed to this procedure:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16

I selected 50 for the simply purpose of, I just wanted to. What would happen if you set it to say 25? In the real world you would have to consider what meets your requirements. You want to make sure the requirements you are getting from the user are answered by the design and structure you are setting up; it will save a lot of headaches in the end.

So we are going to modify the @DepartmentID parameter (before/after):

You will notice that since we cannot allow a null value on the parameter there is no reason to set it to that as default, or even accept it in the procedure. That means we can go ahead and adjust our IF statement. I chose to simply put a RAISERROR statement in its place. In the event someone may try to call this procedure outside of our report they will not be able to simply execute it without providing a properly formatted parameter.

Now the last portion of code you need to change in the procedure is the WHERE clause for your DepartmentID:

The change above is allowing the string being passed from your report to be split into a nice, clean table that can be used as a reference for the IN clause. To show what this will return, the image below illustrates the results from Jeff Moden's split function:

Update Detail_LeaveReport Data Set with new procedure

In my case I decided to create a new procedure, so if you are following along you will need to modify the data set to point to this new procedure.

The Finished Result

Once you make these changes you can now preview the report and then test out the results:

Based on my selections, this is what is returned.

Happy designing!