Introduction

In this article I wanted to walk you through a scenario that will require setting up a report to call a stored procedure, and this procedure has parameters to be passed to it. If you are new to SQL Server Report Service (SSRS) and would like to get more background on it I would encourage you to read through the Stairway Series on SSRS as a starter. Microsoft also offers Reporting Services Tutorials that is worth a read as well.

I am going to be using SQL Server 2012 and using some of the basic features that are available to you within SQL Server Data Tools - Business Intelligence (SSDT-BI) for setting up a parameterized report. SSDT-BI is the development tool for Business Intelligene (SSRS, SSAS, and SSIS) from 2012 and on.

First let me provide a few bullet points on my environment:

• SQL Server 2012 (11.0.3128)
• SQL Server 2012 Report Service (11.0.3128)
• SQL Server Data Tools for Visual Studio 2012 (11.0.50727.1)
• AdventureWorks2012, download link
• Stored Procedure: [HumanResources].[usp_GetLeaveReport] (code is available in the resource section of this article)

Although I am using SQL Server 2012 this will work pretty much the same if you are using SSDT-BI with Visual Studio 2010. If you are still on BIDS the concept is the same, there will be minor differences in the way you maneuver around.

Scenario

Your day starts with an email from Pam, a business analyst within the Human Resources department. In the email Pam explains that she has a stored procedure that she has been running to build an Excel report for the department on vaction and sick leave hours. She would like this to become a more formal report that the users can run on their own as needed.

The procedure has filters for the department, number of vacation hours, and number of sick hours. Pam provided two examples of common request she may receive from her department. She would generally run these from SQL Server Management Studio and then copy/paste the results to Excel.

SET NOCOUNT ON;
EXEC [HumanResources].[usp_GetLeaveReport]
 @DepartmentID = NULL
 ,@gtVacationHours = NULL
 ,@gtSickHours = NULL;
GO
EXEC [HumanResources].[usp_GetLeaveReport]
 @DepartmentID = 7
 ,@gtVacationHours = NULL
 ,@gtSickHours = NULL;
GO

Based on the procedure we can see that we will need three parameters. The DepartmentID parameter in the procedure only accepts the ID value for a department. This is common and to make the report more user friendly for those that may not have memorized all the IDs we can provide them a textual view of the IDs instead, but have it pass in the integer value of the associated ID. The only other two parameters required will be for vacation hours and sick hours, these will have the user entering an integer value to be passed.

The Setup

I am creating a Report Server Project named "AdventureWorks":

I have added a new report (not via the report wizard) called "LeaveReport.rdl":

Getting Started

Your report will open in design mode and then you should find a "Report Data" window pane on the left side of your work area. If you don't, go through the View menu in Visual Studio to add it.

Before creating any datasets in a report you have to first create the data source. You can right click on "Data Sources" and select "Add Data Source...".  Create the connection to your instance of SQL Server and the AW database following the screenshot below, it will default with Windows credentials:

Datasets are needed to bring data from your database into your report design, then from there they can be used for various things. The following list are the datasets we will create:

• Lookup_DepartmentNames
• Detail_LeaveReport (stored procedure)

Then we will need the following parameters:

• DeptID
• VactionHours
• SickHours

Now there is a order of precedence you have to consider with parameters. The order they appear in the Report Data window pane is how they are displayed on the report, if they are visible. When you start creating them and select one, you will see two buttons in the Report Data pane that offer the ability to reorder them:

In this example the order of creation is going to be as follows:

1. Dataset - Lookup_DepartmentName
2. Parameter - DeptID
3. Parameter - VactionHours
4. Parameter - SickHours
5. Dataset - Detail_LeaveReport

Lookup_DepartmentName

To create this dataset we will use the query below. You will begin by right clicking on "Datasets" in the Report Data pane, select "Add Dataset...".

SELECT Name, DepartmentID.
 FROM HumanResources.Department
UNION
SELECT '',NULL
ORDER BY Name

Using the query above and match your properties window up to the one below:

Click OK and that dataset is now complete.

DeptID

In the Report Data window pane right click on "Parameters" and select "Add Parameter...", then follow the screenshots below:

In the Available Values window we will "Get values from a query" and select the dataset we just created in the previous steps above.

The "Value Field" is the value being passed by the parameter, here we want the ID to be passed to the procedure. The "Label Field" is going to be the value that is displayed to the user, and we want the textual name of the DepartmentID. This parameter is going to be created as a drop down list and when a user selects "Marketing" from that list, the parameter is going to pass the integer value 4 to our stored procedure.

You will click OK and that parameter is now completed.

VacationHours and SickHours

We are not going to provide any available values to these two parameters. The only thing needed for these two is to specify the properties on the "General" page. So simply create a new parameter for each and match them to the screenshots below:

You have completed all the parameters we need for our final dataset. You can click on the "Preview" button in the design window of your report if you would like to see how the parameters are going to be displayed. 

As a little exercise can you see what the difference would be if you modify the DepartmentNames dataset and remove the "SELECT '',NULL" portion of the query.

Detail_LeaveReport

Now to finish up we will create another dataset, as we did before, but this time we are going to have a query type of "Stored Procedure". This will then offer you a drop list of all the stored procedures in the database. If you have deployed the script located in the resources section you should see that within your list:

If you select the Parameters on the left side of the properties window you should see a list of three parameters. SSDT-BI picked up the metadata of that procedure and populated the Parameter Name with each one it discovered. The drop downs for the "Parameter Value" column should be populated with the parameters that have been created previously. Associate each one to their appropriate Parameter Name as shown below:

Click OK.

We now have all the parameters and datasets completed. Your next steps will be to design the body of your report, which is beyond the scope of this article. I simply added a page header with a title and a few text boxes so I could confirm how many rows were being returned. The body of the report is just a simple table that is tied to the Detail_LeaveReport dataset. If you deploy this to an SSRS instance you will see the end result that the user will be working with below:

Happy designing!