Building Reports in SQL Server 2005—Setting Report Parameters with Stored Procedures

Visual C# Tutorials

Database Tutorials

Building Reports Report Wizard Build Report from Scratch Format the Output Add Subtotals Interactivity § Set Report Parameters Apply a Filter Add a Chart Final Touches Multivalued Parameters Summary

© 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 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

• EmployeeTblID

• ServiceMonth

• ServiceYear

• ServiceLogCtgryID

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.

Open the 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 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 string to Int32."

Figure 4-16. Parameters required for the stored procedure Emp_Svc_Cost

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 NULL values (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.

Figure 4-17. Report Properties dialog box, showing the Allow Null Value field

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.

Figure 4-18. No parameter values available via drop-down list

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, Employee_DS and 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 WHERE clause 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 BranchID parameter, 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 EmployeeTblID parameter, entering Employee for the prompt.

3. In the available values for the branch parameters, select From Query, and then select the Branch_DS dataset. The Value field will be BranchID, and the Label field will be BranchName.

4. Follow the same steps to modify the Employee parameters, assigning Employee_DS and choosing the Value and Label fields as EmployeeTblID and 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.Value to the new group, and click OK in the Grouping and Sorting dialog box. Next, drag the BranchName field 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, the NULL checkbox has disappeared.

Figure 4-19. Report with populated parameter selections

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 Service Month) 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 Service Month parameters with valid values is almost identical to the Branch and Employee procedures covered earlier, with the exception that the Service Year needs to default to the current year and not NULL.

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 the DatePart and 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:

=cint(DatePart("yyyy",Now()))

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.

The EmployeeServiceCost_Parameters.rdl report in the Pro_SSRS project includes populated parameters.

Figure 4-20. Report with valid year and month values