Tutorial

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.

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….

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:

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

This is what our query returns:

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.

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.

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

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:

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:

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

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); 

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'); 

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'); 

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

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

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

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: http://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:

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:

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:

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!

Valentino.

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

Introduction

The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The dataset can use a SELECT query, which is the most common way of retrieving data and one that you’re probably already familiar with.  But it can also use a Stored Procedure (aka stored proc or SP).

The purpose of this article is to demonstrate how data can be retrieved from a SQL Server database through Stored Procedures defined in that same database, and then displayed in a SQL Server 2008 Reporting Services report.

I’ll be using the AdventureWorks2008 sample database available for download at CodePlex.

What Are Stored Procedures?

There are actually different types of stored procedure in the context of SQL Server.  The type that I am using in this article is called a “Transact-SQL Stored Procedure”.  According to the Books Online, this type of stored procedure is:

“A saved collection of Transact-SQL statements that can take and return user-supplied parameters.”

If you have experience using a regular programming language such as Visual Basic or C#, I’m sure this sounds familiar.  You can think of a stored procedure as a method that takes any number of parameters, depending on its definition, and that possibly returns a result, again depending on its definition.  Instead of being stored in a compiled .exe or .dll, it is stored in the database.

In this article I will be using some stored procedures that return a dataset as result.  How stored procedures are written is not the purpose of this article.  For that I’d like to refer you to this Books Online page: Implementing Stored Procedures.

Reasons For Using A Stored Procedure

There are several reasons why it is more interesting to use datasets based on stored procedures as opposed to SELECT statements.

Performance: stored procedures perform faster than SELECT statements.  The reason for this is because they are compiled when they’re created and their execution plan gets stored by SQL Server so that it can be reused for each procedure call.

Maintenance: when a database needs to undergo some changes to its schema, the changes can be handled in the stored procedures.  This makes it transparent for the reports that are using these stored procedures.  The reports will keep functioning as expected without any modifications to them.

Reuse: imagine a situation where several reports are reporting on the same set of data.  If you wouldn’t use stored procedures, you may have to repeat a possibly complex query in each report.  With stored procedures you just need to define the query in the stored procedure and then call it in each report’s dataset.

Security: in environments where DBAs are responsible for the SQL Server databases, the report developers will possibly not get sufficient rights to retrieve data from the tables directly.  One of the ways to prevent this is to give them access to a bunch of stored procedures and views instead.

Simple Procedure Call

Okay, enough theory, time to show you how it’s done!

Setting Up The Stored Procedure

Our first procedure queries the AdventureWorks database to return a list of employees with their corresponding department.  Here’s the code for the SP:

CREATE PROCEDURE GetEmployeeData
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;
END

Creating The Report

Creating A Shared Data Source

Creating The Data Source

Creating The Dataset

I’m calling the Dataset dsEmployeeData.  The Data Source edit box is already prefilled with the right data source because we’ve right-clicked the data source to which we want to add a dataset, how easy can it be?

To query a stored procedure from a dataset is really very straightforward.  All you need to do now is select the Stored Procedure radio button.  Doing that replaces the bottom part of the window.  Instead of an edit box that expects a query, we now get a simple dropdown where we need to select our SP.

Once you’ve done that, select the Fields page on the left.

As you can see, the list of fields has been pre-filled.  The BIDS queries the stored procedure’s metadata so we do not need to manually specify the fields returned by our SP, saving us quite some time!

Once you click OK to close the Dataset Properties window, you’ll see that the Report Data pane gets populated with our list of fields.

Show Me The Data

To prove that everything is working as expected, let’s set up a quick report using a Table.  From the Toolbox pane, drag a Table object onto the report canvas:

Next, from the Report Data pane, drag some fields into the cells in the Data part of the Table.  As we’re dealing with employee data, it would be interesting to see their names.  I’ve also added their job and department.

Resize the columns a bit so that everything will fit nicely, and color the Header cells different from the Data cells to easily distinguish them.

Now we’re ready to run the report, so click the Report’s Preview tab.

So, that was our first procedure.  Wasn’t too complicated, was it?  So, ready for another one?

Parameterized Procedure Call

This time we’ll make it a little more complicated.  As you know, stored procedures can take parameters.  And SSRS knows how to pass them into a stored proc.  So let’s do that!

Setting Up Stored Proc Version 2.0

The stored procedure shown below is based on our previous one.  Except now it takes one parameter: @HireDate.  The SP will only return employees that have been hired after given HireDate.

ALTER PROCEDURE GetEmployeeData
    @HireDate date
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;
END

As the HireDate field in the HumanResources.Employee table is of type date, I chose to make the parameter that same data type.

Please note that the above script uses ALTER PROCEDURE instead of CREATE PROCEDURE.  This will only work if you’ve already created the previous procedure.  If not, just replace the word ALTER with CREATE.

Modify Report To Pass Parameter To Stored Procedure

The next step is to modify our existing Dataset so that it passes a date into our stored procedure.

Open up the Dataset’s properties by double-clicking it in the Report Data pane.  Then select the Parameters page:

As you can see, it is still empty.

Now switch to the Query page and click the Refresh Fields… button:

Then switch back to the Parameters page:

The parameter has been automatically added when we clicked the Refresh Fields… button!

The BIDS again used the stored proc’s metadata to do this.  No need for manual intervention.  For now, leave the Parameter Value box as it is, empty.  Click OK to close the properties window.

Clicking OK caused another action in our report!  If you open up the Parameters folder in the Report Data pane, you’ll see that it has gotten an item as well:

You can double-click the @HireDate parameter to get its properties:

The BIDS has created a report parameter and linked it to the parameter in our Dataset.  It automatically chooses the correct data type for the report parameter, Date/Time in this case.  (Unlike in T-SQL, there are no separate Date and Time data types in SSRS 2008.)

To confirm that the report parameter is linked to the parameter in the Dataset, close the Report Parameter Properties window and open up the Dataset Properties again, selecting the Parameters page:

The Parameter Value for our parameter has now gotten a value, more precisely it’s referring to the Report Parameter called @HireDate.  If you don’t believe that it is actually the report parameter (I admit, it looks similar to the parameter in the Dataset, except for the square brackets surrounding the report parameter’s name.), click the fx button to see the formula:

Indeed, it is referring to the parameter on the report.

Test The Changes

Now that the parameter has been set up, it’s time to test our report again.

As the report is now filtering on the HireDate, it would be interesting to actually show this field in the table.  Add an extra column and drag this field into the data cell.

Our newly added field’s type is datetime, but we’re only interested in the date part.  So we’ll set up a format code.  Select the HireDate data cell and locate the Format property in the Properties pane.  Enter a small d into it:

This instructs the BIDS to show the field using the “short date pattern”.  A list of possible codes is available on MSDN: Standard Date and Time Format Strings.  As you notice, SSRS is using the regular formatting strings as they are known in .NET.

Note also that the format used to display the data depends on the report’s Language property.  I’ll leave it at its default: en-US.

Right, time to have a look at the report.  Select the Preview tab.  You can now see the Hire Date parameter which is expecting a value.  You can either type a value or select it from the calendar which is shown when you click the button on the right of the textbox:

To be sure that you’re using the correct format for the date, I suggest to select a date using the control.  Then you can always modify it to whatever day you’d like to select.

For our test I know that some employees started before 2000 and others later, so I’ll select a date somewhere in the year 2000.

And indeed, our report shows only employees that started after our selected HireDate.

Conclusion

With this article I hope to have shown you that it’s fairly straightforward to report on data coming from stored procedures.

In case the above hasn’t fulfilled your appetite on this subject yet, watch out for part 2.  In that sequel I will build further on the example used in this article to show you how you can use multi-value parameters to filter your report’s data.

In the meantime: happy reporting, and thank you for reading my article!

References

BOL 2008 – How to: Create an Embedded or Shared Data Source

BOL 2008 – How to: Create a Dataset (Reporting Services)

BOL 2008 – How to: Refresh Fields for a Dataset

BOL 2008 – Implementing Stored Procedures

BOL 2008 – Data Sources Supported by Reporting Services