Building Reports in SQL Server 2005—Setting Report Parameters with Stored Procedures
|Visual C# Tutorials|
|© 2006 R. Landrum, W. J. Voytek|
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. To this point you have been working with a query instead of a stored procedure 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
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:
To create the parameters automatically for your Employee Service Cost report, which is currently using a nonparameterized query, you will simply change the dataset for your report to be the stored procedure.
EmployeeServiceCost_SP report from the project included in the code download.
On the Data tab that has the dataset
Emp_Svc_Cost, you can click the ellipsis button (...) next
to the Dataset field to open the Properties window. In the Command Type drop-down list,
change the value from Text to Stored Procedure. Next, type the name of the stored procedure,
Emp_Svc_Cost, in the Query String window. When you click OK and then execute the query in
the generic query designer, you are prompted to input parameters (see Figure 4-16). Since the
stored procedure is designed to accept
NULL values, change the default input value in the Define
Query Parameters dialog box from
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 string to Int32."
In the Report Parameters Properties box, you can see that the report parameters were automatically
created from the stored procedure. Though SSRS did correctly assign the datatype for
each parameter, integer, and string, it did not automatically set the field to allow
(see Figure 4-17). For the purpose of this report, which expects
NULL values as possible parameters,
it is important that the Allow Null Value checkbox is selected for each parameter so that
when the report is previewed,
NULL will be the default value, and the
NULL checkbox will be
automatically checked so that the report executes without requiring user input.
Default parameter values will also need to be manually configured. If no default parameter
value is assigned to an available parameter, the report, when rendered or previewed, will not process the incoming data until a user supplies a value. Previewing the report without modifying the parameter selection reveals that the user would need to enter a value for each
parameter that has no default value assigned. The user would not be able to choose from a list
of values but would have to enter them manually. This is unacceptable because the user will
not always know the correct values; good examples of this are the
EmployeeTblID field that is
used to select a specific employee and the
BranchID field used to retrieve the branch name.
The first step is to provide valid query-assigned values for the parameter drop-down lists. It would be beneficial to provide a view of the report in preview mode prior to adding descriptive parameter values from a new dataset (see Figure 4-18). Notice that there is a
NULL checkbox selected next to the parameter selections. The
NULL checkboxes appear when the parameter allows
NULL values, as you set earlier, and there are no other available values.
The following list of procedures will add two datasets to populate the Branch and Employee drop-down lists for the parameters:
1. On the Data tab, create two new datasets,
Branch_DS, by dropping down the Dataset list and selecting New Data Set. For both datasets you create, you will add simple queries that will return the IDs and names for the employee and the branch. Notice in the
WHEREclause of the employee query that you are including only a known set of employees for simplicity.--Query for Employee Parameter SELECT EmployeeTblID,rtrim(rtrim(employee.lastname) + ',' + rtrim(employee.firstname)) AS Employee_Name FROM Employee WHERE (Employee.EmployeeTblID IN (32, 15, 34, 44, 129, 146, 159, 155, 26)) --Query for Branch Parameter SELECT BranchID, BranchName FROM Branch
2. After you have created the datasets with the previous queries and verified that they execute properly on the Data tab, go to the Layout tab, and from the menu select Report -> Report Parameters. Select the
BranchIDparameter, and enter Branch for the prompt for clarity, as you will be selecting the branch name in the drop-down list. Do the same for the
EmployeeTblIDparameter, entering Employee for the prompt.
3. In the available values for the branch parameters, select From Query, and then select the
Valuefield will be
BranchID, and the
Labelfield will be
4. Follow the same steps to modify the
Employee_DSand choosing the
Employee_Name, respectively. When finished, select OK.
5. Finally, on the Layout tab you will add a grouping for Branch Name to the table in the report so that as the parameters are selected, you can see that the report is specific to a branch. To do this, right-click on the button to the left of the Table Header row, above Diagnosis, and select Insert Group. This will make the Diagnosis group, formerly the first group, now the second group and will add a new group. Assign the expression value of
=Fields!BranchName.Valueto the new group, and click OK in the Grouping and Sorting dialog box. Next, drag the
BranchNamefield from the Datasets window to the new first column row for the Branch group you just created. Also, make the field bold, and resize the font to 12 points.
The report will now have populated drop-down lists for the available parameter values, as
shown in Figure 4-19. Notice that for the two parameters where you have added available values,
NULL checkbox has disappeared.
You could perform the same steps for the
ServiceLogCtgryID parameter and provide
a valid drop-down list from the table values. However, since you may also be viewing the report in
a custom report viewer that will also accept parameter values, this particular parameter value
is of little use to you now for direct user input. That being the case, it will be beneficial to take
advantage of another new and much needed feature, the ability to hide parameters. This functionality
was added in Service Pack 2 for SSRS for SQL Server 2000 and is available in 2005 as
well. Sometimes a parameter can and should be populated by events other than user input. In
these instances, users will only be confused by seeing these additional parameters. In the Report
Parameters dialog box, select the
ServicesLogCtgryID property, and check the Hidden box. It
will also be beneficial to modify the time-based parameters (
Service Year and
for this report. Time-based values are often tricky to deal with because of the special formatting
needs of the DateTime datatype, which can store years, months, and days as well as hours,
minutes, and seconds. The procedures for setting up the
Service Year and
parameters with valid values is almost identical to the
Employee procedures covered
earlier, with the exception that the
Service Year needs to default to the current year and not
The first step is to create a dataset for the
Service Year and
Month parameters based on the
service date, which is the field
ChargeServiceStartDate in the stored procedure. You will use
DateName functions in the two queries to derive valid values. The valid values
for the dates are contingent upon their existence in the table, so, for example, if your data contained
values for 2003 and 2004, only those two years would show up in the drop-down list.
Populating the date values in this way precludes the user from having to enter a date and also
prevents the report designer from having to hard-code year and month values into the report.
Listing 4-2 shows the two queries that drive the parameter values. Unlike with SSRS for
SQL Server 2000, you don’t need to pass in a
NULL value in the dataset, as SSRS for SQL Server
2005 supplies the
NULL value by default when the parameter allows
NULL values. This was
a limitation with the previous version that has been addressed.
Listing 4-2. Parameter Value Queries
SELECT DISTINCT DatePart(yy,ChargeServiceStartDate) AS Year FROM TRX --Query to Derive Month SELECT DISTINCT DatePart(mm,ChargeServiceStartDate) AS DateNum, DateName(mm,ChargeServiceStartDate) AS Month FROM TRX ORDER BY DatePart(mm,chargeservicestartdate)
To finish the report, add the
Service Year field to the report, formatting it with a distinct
color (in this case, dark salmon), and then resize the field to 12 points. Before you preview the
report, it is important to set the default value for the year so that a valid Service Month selection
is not based on the default
Service Year field of
NULL. This could potentially have undesired
results; in other words, the user might select January and assume that it means January for the
current year, when in fact it would be all occurrences of January.
To make the
Service Year parameter default to the current year, go to the Report Parameters
dialog box and set the Default Value option to the following expression:
You can preview the report and provide parameter values (see Figure 4-20).
■Note Most of the data in the
Pro_SSRS database is from 2003 and 2004. If the current year is defaulted
to a different year, the data you see may not be the same as that shown in Figure 4-20.
EmployeeServiceCost_Parameters.rdl report in the
Pro_SSRS project includes populated