Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Reporting On Data From Stored Procedures (part 2)

ValentinoVBI Consultant
CERTIFIED EXPERT
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2017: MVP Data Platform
Published:
Updated:
Written by Valentino Vranken.

1. Introduction

In a previous article I announced that I would write a sequel covering how to pass multiple-value parameters from a SQL Server Reporting Services report to a stored procedure.  So that's what I will be writing about in this article.

As usual, I will be using the AdventureWorks2008 sample database (running on SQL Server 2008 SP1), downloadable from CodePlex.

The examples in this article, Part 2, are building further on the result achieved when following the steps described in Part 1, so please refer to the previous article if needed.

2. Passing Multi-Value Parameter To Stored Procedure

As we've already seen in Part 1, parameters can be passed from a Reporting Services report to a stored procedure.  The parameter that was used was just a simple, single-valued parameter.  However, a report parameter can be defined as being multi-value.  Let's set one up!

Our report currently shows a list of employees who were hired after the selected hire date.  One of the columns being shown is the department in which they're active.  We will modify the report so that it's possible to filter the data on department - only the selected departments are to be retrieved from the database.

Creating A Multi-Value Report Parameter

The first step is to create a new report parameter, so right-click the Parameters node in the Report Data pane and select Add Parameter....

 Report Data pane - Add Parameter
I'm calling my parameter Department, and I want it to be of type Integer.  In case you're wondering why Integer, it will become clear very soon.  I have also checked the Allow multiple values checkbox:

 Report Parameter Properties - setting up multi-value param
We want to make the parameter user-friendly so that the user sees a list of departments and can just select those that he needs.  That means the parameter needs to be populated with that list of departments.  To be able to do that, we first need to create a new dataset that retrieves the list of departments.

So for now, close the Report Parameter Properties screen and use the following query to create a dataset called dsDepartmentList:

select D.DepartmentID, D.Name as DepartmentName
                      from HumanResources.Department D
                      order by D.Name asc 

Open in new window


This is what our query returns:

 Result of DepartmentList query - a list of departments
Once the dataset is created, open up the properties of the Department Report Parameter created just before and select the Available Values page.

On that page, select the Get values from a query radio button, choose dsDepartmentList in the Dataset dropdown, select DepartmentID as Value field and DepartmentName as Label field.  The Label field is what the user sees while the Value field is what Reporting Services will use as value.  After all, we want to pass the IDs of the selected departments to our stored procedure, not the department names.  And we want the user to see the department names, not their ID.

 Report Parameter Properties - Available Values
By default, no values are selected.  To make it a bit more user-friendly, let's select all departments by default when the report first loads.  This is done on the Default Values page.

Select the Get values from a query radio button, dsDepartmentList as Dataset and DepartmentID as Value field.

 Report Parameter Properties - Default Values
That's it, the multi-value report parameter is created!  Of course, at this moment it doesn't have any effect on the report's content yet (switch to Preview if you don't believe me and have a look).  We'll get to that next.

Discover What Is Being Passed To The Stored Procedure

To be able to handle the values passed into our stored procedure, let's first find out what exactly our report is passing into it.  We'll do this by temporarily creating a new stored proc that will just accept the parameter values and return them.

This is what our test SP looks like:

CREATE PROCEDURE MultiValueParam
                          @MyParam varchar(1000)
                      AS
                      BEGIN
                          SELECT @MyParam as TheParameterReturned;
                      END

Open in new window


It accepts one parameter and returns it in a field called TheParameterReturned.

Set up a new dataset that calls this SP, called dsMultiValueParamTest.  I'm sure you know how to do this by now :-)

When creating the dataset, on the Parameters page, select the new parameter [@Department] that we created earlier:

 Dataset Properties - Parameters
To see what the field contains, drag it from the Report Data pane onto the report canvas, above the table that was created in Part 1.  Enlarge the textbox a bit and activate the Preview tab.  Select a hire date (doesn't matter which one) and click the View Report button.  As we've set up the report to select all departments by default, we don't need to select them manually.  But of course if you want you can have a look in the Departments dropdown to check if they are actually selected.  This is the result after clicking the View Report button:

 Content Of Multi-Value Parameter
If you compare that list of numbers with the result that our dsDepartmentList query returns, you'll see that these are the values from the DepartmentID field in exactly the same order as in the query's result.  And separated by commas.  So in other words: it's a comma-separated string of selected values.

Wrong Way To Implement The Parameter

Now that we know what exactly the multi-value parameter passes to a stored procedure, let's modify our main procedure by adding the extra parameter to it.

Here's the modified procedure:

ALTER PROCEDURE GetEmployeeData 
                          @HireDate date, 
                          @DepartmentList varchar(1000) 
                      AS 
                      BEGIN 
                          SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender, 
                              E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours, 
                              D.GroupName as DepartmentGroupName, D.Name as DepartmentName, 
                              P.FirstName, P.MiddleName, P.LastName 
                          FROM HumanResources.Employee E 
                              INNER JOIN HumanResources.EmployeeDepartmentHistory EDH 
                                  ON EDH.BusinessEntityID = E.BusinessEntityID 
                                  AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in 
                              INNER JOIN HumanResources.Department D 
                                  ON D.DepartmentID = EDH.DepartmentID 
                              INNER JOIN Person.Person P 
                                  ON P.BusinessEntityID = E.BusinessEntityID 
                          WHERE E.HireDate > @HireDate 
                              AND D.DepartmentID IN (@DepartmentList); 
                      END

Open in new window


Since our parameter is a comma-separated list of our values, I've used the IN operator to filter on only the selected values.

Next we need to add the extra parameter to the dsEmployeeData dataset.  Double-click it in the Report Data pane to get its properties and click the Refresh Fields button to have it add the new parameter to the list.  Then switch to the Parameters page and select the [@DepartmentList] parameter as Parameter Value for the newly-added @DepartmentList parameter.

Close the properties popup and run the report by activating the Preview tab.

Oh no, an error!  More precisely this one (I will only mention the last line):


Conversion failed when converting the varchar value '12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2' to data type smallint.

This error comes from our stored procedure.  It complains that it cannot convert the list of values from a string to a smallint.  Weird isn't it?  Well, maybe not.  Let's have a look at what's going on.

The following query would work perfectly:

SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender, 
                          E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours, 
                          D.GroupName as DepartmentGroupName, D.Name as DepartmentName, 
                          P.FirstName, P.MiddleName, P.LastName 
                      FROM HumanResources.Employee E 
                          INNER JOIN HumanResources.EmployeeDepartmentHistory EDH 
                              ON EDH.BusinessEntityID = E.BusinessEntityID 
                              AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in 
                          INNER JOIN HumanResources.Department D 
                              ON D.DepartmentID = EDH.DepartmentID 
                          INNER JOIN Person.Person P 
                              ON P.BusinessEntityID = E.BusinessEntityID 
                      WHERE D.DepartmentID IN (12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2);

Open in new window


But that is not what is being executed by our SP!  In the query above, we are passing a list of numbers to the IN operator.  But our SP accepts a varchar, a string.  Sure, the report parameter passes a list of numbers, but they are stored in a string!  An equivalent query for what our SP actually executes is the following:

SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender, 
                          E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours, 
                          D.GroupName as DepartmentGroupName, D.Name as DepartmentName, 
                          P.FirstName, P.MiddleName, P.LastName 
                      FROM HumanResources.Employee E 
                          INNER JOIN HumanResources.EmployeeDepartmentHistory EDH 
                              ON EDH.BusinessEntityID = E.BusinessEntityID 
                              AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in 
                          INNER JOIN HumanResources.Department D 
                              ON D.DepartmentID = EDH.DepartmentID 
                          INNER JOIN Person.Person P 
                              ON P.BusinessEntityID = E.BusinessEntityID 
                      WHERE D.DepartmentID IN ('12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2');

Open in new window


When executing that in the Management Studio, it will throw this error:


Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2' to data type smallint.

Doesn't that look familiar?!

The reason for this error is the following.  The DepartmentID field is of type smallint.  Therefore SQL Server tries to convert the list of values to smallint.  In the first SELECT statement, each value gets converted to smallint and all works fine.  In the second SELECT, SQL Server sees just one value, a varchar(1000), and tries to convert that to a smallint.  It fails because the value that the string contains is not convertible to smallint.  If the string would contain only one value, it would actually work.

You can try it out by replacing the last line with this:

WHERE D.DepartmentID IN ('12');

Open in new window


Right Way To Implement The Parameter

We've seen that our first implementation of using the IN operator is not a good idea.  So we need to find another way to get this stored procedure working.

Note: it would actually be possible to use the previous method in combination with dynamic SQL but I'm not going to apply that technique here.  In case you are interested in that method, just construct a long string that contains the whole query as it is in the working SELECT statement above.  For more info on dynamic SQL I'd like to point you to this excellent article by SQL Server MVP Erland Sommarskog: The Curse and Blessings of Dynamic SQL.

The IN operator can take a subquery.  So now we need to find a way to "select" the values out of our comma-separated string of values.  I am not going to re-invent the wheel and use a function that's mentioned in another great article by Erland Sommarskog.  The article is called Arrays and Lists in SQL Server 2005 but is also applicable to 2008 and mentions a function called iter$simple_intlist_to_tbl.

For this article's readability purposes I've renamed the function to list_to_tbl.  Here's the code to create it:

-- from http://www.sommarskog.se/arrays-in-sql-2005.html 
                      -- original name: iter$simple_intlist_to_tbl 
                      CREATE FUNCTION list_to_tbl (@list nvarchar(MAX)) 
                         RETURNS @tbl TABLE (number int NOT NULL) AS 
                      BEGIN 
                         DECLARE @pos        int, 
                                 @nextpos    int, 
                                 @valuelen   int 
                      
                         SELECT @pos = 0, @nextpos = 1 
                      
                         WHILE @nextpos > 0 
                         BEGIN 
                            SELECT @nextpos = charindex(',', @list, @pos + 1) 
                            SELECT @valuelen = CASE WHEN @nextpos > 0 
                                                    THEN @nextpos 
                                                    ELSE len(@list) + 1 
                                               END - @pos - 1 
                            INSERT @tbl (number) 
                               VALUES (convert(int, substring(@list, @pos + 1, @valuelen))) 
                            SELECT @pos = @nextpos 
                         END 
                        RETURN 
                      END

Open in new window


It takes a list of comma-delimited integers and returns a resultset containing integers, just what we need!

When we implement this in our main procedure, this is what it looks like:

ALTER PROCEDURE GetEmployeeData
                          @HireDate date,
                          @DepartmentList varchar(1000)
                      AS
                      BEGIN
                          SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
                              E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
                              D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
                              P.FirstName, P.MiddleName, P.LastName
                          FROM HumanResources.Employee E
                              INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
                                  ON EDH.BusinessEntityID = E.BusinessEntityID
                                  AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
                              INNER JOIN HumanResources.Department D
                                  ON D.DepartmentID = EDH.DepartmentID
                              INNER JOIN Person.Person P
                                  ON P.BusinessEntityID = E.BusinessEntityID
                          WHERE E.HireDate > @HireDate
                              AND D.DepartmentID IN (select * from list_to_tbl(@DepartmentList));
                      END

Open in new window


And indeed, if we now run our report again, it works perfectly!

 Report using multivalue parameter
Note: for another great reference on how to deal with a delimited list as Stored Proc parameter, I'd like to point you to the following article by colleague Expert and SQL Server MVP angelIII: https://www.experts-exchange.com/articles/Database/Miscellaneous/delimited-list-as-parameter-what-are-the-options.html

Displaying The Filter On The Report

Another best practice as far as report readability goes is that it should be clear on your report what data has been filtered.  As the multi-value parameter is on focus here, I'll demonstrate how you can show the selected values on your report.

In fact, it's not really the parameter's values that we are interested in now (those are DepartmentIDs, remember?).  No, it's the labels.  And here's how to get to them.  Add a new textbox above the main report table.  Make it the same width as the table and right-click it to add an Expression.  Enter the following expression:

=Join(Parameters!DepartmentList.Label, ", ") 

Open in new window


It uses the Join function to join all members of the Label collection together into one string, using comma and space as the value separator.  This is what it looks like on the report:

 Showing selected values of a multi-value parameter on the report
In case you would like to see the selected departments under each other instead of in a long string, that's also quite easy to achieve.  The expression is based on Visual Basic, and in Visual Basic there's a constant called vbCrLf - Visual Basic carriage-return line-feed.  Adapt the expression to the following and the values will be shown in a list instead of a long string:

=Join(Parameters!DepartmentList.Label, vbCrLf)

Open in new window


Let's have another look at the effect:

 Showing selected values under each other

3. Conclusion

With this article I believe I've demonstrated that it is possible to pass multi-value parameters from a SQL Server Reporting Services report to a stored procedure, while applying some best practices such as giving the users a nice list of values to select from.  

Happy reporting, thank you for reading my article, and should you feel like it: post a comment!

And if you appreciated reading this, I will surely appreciate you clicking that nice little blue YES button a bit further down, next to my profile description.

Regards,
Valentino.

Originally appeared on my personal website: http://blog.hoegaerden.be/2009/11/21/reporting-on-data-from-stored-procedures-part-2 

References

BOL 2008: the IN operator
The Curse and Blessings of Dynamic SQL by Erland Sommarskog, SQL Server MVP
Arrays and Lists in SQL Server 2005 by Erland Sommarskog, SQL Server MVP
BOL 2008: Expression Examples (Reporting Services)
delimited list as parameter, what are the options? by angelIII, SQL Server MVP
12
12,152 Views
ValentinoVBI Consultant
CERTIFIED EXPERT
1998: C++ - SQL Server 6.5
2000-2007: C++, VB6, C#, java - SQL Server 7.0-2005
2008-...: SQL Server 2005-2016
2014-2017: MVP Data Platform

Comments (8)

Commented:
Its good article.

I Think you can Improve this by modifying the =join() with changing the delimiter to custom delimeter and passing to the stored procedure.

This is needed because suppose the values in the multi value parameter contains like 'Raj, Aryan,Ravi, Kumar' but here the I need it to be like 'Raj, Aryan!@#$$Ravi, Kumar' here delimeter sequence is !@#$$ will be helpful if we have the comma in the parameter values.

Then In the List_to_Tbl Function You have to modify the Delimeter to be only ',' use the some sequence like the Example I given.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Author

Commented:
I noticed that the link to AngelIII's article has been broken.

Working link: Delimited list as parameter: what are the options?
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Excellent work.  Voted yes.
ValentinoVBI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011

Author

Commented:
Thanks Jim, always nice to hear from other experts! :)
Bhagat SBusiness Intelligence Analyst

Commented:
Great article and easy to follow.

Is there anyway the 'select all' functionality can be added into the stored procedure?
I'm referring to the article: http://blog.hoegaerden.be/2013/12/18/the-select-all-parameter-option-ssrs/

I have added an, OR statement at the end of the WHERE clause as follows:

WHERE (E.HireDate > @HireDate
AND D.DepartmentID IN (select * from list_to_tbl(@DepartmentList))) OR @DepartmentList = -1;

But produces the following error:
Conversion failed when converting the varchar value '-1,1' to data type int.

Any suggestions welcome. Thank you

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.