CHAPTER 6 BUILDING REPORTS
Setting Report Parameters with Stored Procedures
In Chapter 3, we introduced parameters and explained how you can use them within reports and queries
to limit the results returned from the data source. Up to this point, you have been working with different
kinds of datasets, queries, and stored procedures to build reports, but we have only touched the surface
of how you can use parameters within SSRS. Parameters get their values primarily from user input and
are most often associated with a dataset; they are used to limit the amount of data returned. When a
parameter is used in this way, it is called a query parameter. Query parameters that are part of a dataset,
such as a SQL query or stored procedure, automatically generate report parameters within SSRS.
In this section, you will modify the dataset of your Employee Service Cost report to use a
parameterized stored procedure instead of a query. By default, report parameters generated from stored
procedures do not have populated drop-down lists of data for users to select, so in this section you will
also populate the report parameter lists with valid data for user-selectable input. Finally, you will see
how SSRS works with NULL parameter values and how to generate a NULL value for the parameter. This
will become especially important when retrieving data for your SSRS report, as we will explain later in
this section.
You will return to the stored procedure you have already created, called Emp_Svc_Cost, which, as
you might recall, will deliver the same dataset as the SQL query you have been using. The stored
procedure has the added benefit of accepting all the parameters you want to use in the report. SSRS will
automatically create the report parameters from the stored procedure. Let’s quickly review the
parameters that will be passed into the report from the stored procedure:- BranchID
- EmployeeTbllD
- ServiceMonth
- ServiceYear
- ServiceLogCtgrylD
To create the parameters automatically for your Employee Service Cost report, which is currently
using a non-parameterized query, you will simply change the dataset for your report to the stored
procedure.
Open the EmployeeServiceCost_SP report from the project included in the code download. In the
Report Data window that has the dataset Emp_Svc_Cost, you can right-click the Emp_Svc_Cost dataset
and select Dataset Properties to open the Properties window. In the Dataset Properties dialog, change
the Query type from Text to Stored Procedure. Next, select or type the name of the stored procedure,
Emp_Svc_Cost, in the Query String window, and click OK. When you click OK, the parameters will be
created automatically for you. Next, right-click the Emp_Svc_Cost dataset again and select Query. You
should see that the query will execute the Emp_Svc_Cost stored procedure. Click the Run button, which
will prompt you to input the parameter values, as shown in Figure 6-25. Since the stored procedure is
designed to accept NULL values, change the default input value in the Define Query Parameters dialog
box from Blank to NULL, and click OK to complete the execution. If you do not select NULL instead of
Blank, the query will fail with an error message, “Failed to convert parameter value from a string to
Int32.”