Reporting Services 2: parameterized reports

This article describes the parameterized Report of reporting services ).

According to the variability of report content, reports are classified into static reports and parameterized reports. data-driven parameterized reports are more in line with people's needs and habits.

First, let's take a look at how to create a parameterized report using reporting services:

1. Right-click the project awreport and choose "add"> "new item"> "report" to add a new report rptpr. RDL;

2. In the report rptpr. on the "data" tab of RDL, click the dataset to go down to the list and create the dataset dataemployee (1). Click "Universal Query designer" to switch to the query design status and add the table HumanResources. and select all columns as output.

Figure 1 create a dataset

3. In the report rptpr. the design status of RDL (under the "data" or "layout" tab). Choose "report"> "report Parameters" to start the "Report Parameters" dialog box, add parameters to the report and specify the corresponding "data type" and "prompt" for the parameters, as shown in table 1. Parameters of different data types are displayed as different controls in the report. For example, Boolean parameters are displayed as two check boxes marked as true and false, while datetime parameters are displayed as a datetimepicker; the "prompt" indicates the labels in front of these controls.

parameter name	data type	prompt
ptitle	string	title
pmstatus	string	marital status
pgender	string	gender
psflag	Boolean	paid or not
pbdate	datetime	birthday

Table 1 parameter name, data type, and prompt settings

4. Switch to the "data" tab of the report and add the filter conditions shown in Figure 2 to the corresponding fields.

Figure 2 Add a parameter to a field as a filter Condition

5. Switch to the "layout" tab of the report and select the layout shown in Design 3 of the report.

Figure 3 report layout

6. In the preview status, specify different values for the parameters in the parameter items displayed in the report, and you can see the parameterized report shown in Figure 4.

Figure 4 parameterized reports in preview state

The actual values of "Marital Status" include M (married, married) and S (single or sole, unmarried). For users, it is obviously inappropriate to use a text box to accept the input value. For example, the full spelling of the input word or the first letter U of the unmarried cannot be found, in the "available values" Area of the "Report Parameters" dialog box, you can set the parameters shown in figure 5, so that the "Marital Status" parameter can be entered using the drop-down list. The "gender" parameter should be set similarly.

Figure 5 set available values for "Marital Status"

If you are unfamiliar with the title of a company employee or have different names for the title, the above problems will also arise, and the value of the "title" field is not fixed, system Maintenance may increase or decrease. In this case, you can specify the "available value" for this parameter in the query ". Note that if the title field in the same dataset dataemployee is used as the source of available values and the title field is the first parameter, a forward dependency error occurs, the value of the title field is determined by the following parameters. If the title field is used as the last parameter, every time you change the value of other parameters, the data in the title parameter drop-down list will be refreshed and changed (this can be used as a linkage between the province, city, and county ). In this case, you should consider using the method shown in figure 1 to create another data set datatitle and specify its data source:

Select distinct title from HumanResources. Employee

Then, specify the available value of the "title" parameter according to the settings shown in figure 6.

Figure 6 set available values for "title"

In addition, you can specify the "Default Value" for each parameter to reduce the data input for common queries.

The report is displayed in 7 of a web project.

Figure 7 parameterized reports displayed in the WEB Project (click the thumbnail to view the large chart)

To a certain extent, reporting services meets users' requirements for parameterized reports, but there are also some improvements. For example, once a parameter is defined, the value of the parameter cannot be ignored to view the report.