Export (0) Print
Expand All

Using Parameters in a Report

SQL Server 2000

You can add parameters to a report to manipulate data the report contains. Report parameters can be used to pass values to an underlying query, to pass values to a filter, or as variables for calculating data within the report. A report parameter text box is usually presented to the user when they run the report, but a report can also use a default parameter without presenting the choice to the user.

When you create a parameter, you must give the parameter a name. Parameter names are required, and must be unique within the report. You can also set a data type for the parameter. A data type is required, and is set to String by default. You can also specify whether the parameter will take a null value, and whether it will take a blank value. You can also define a prompt for the user, set available values for the user to choose from, and set a default value for the parameter.

Prompt

When the report is run in a browser, the parameter is displayed in a box at the top of the report. To set the text that is displayed next to the box, set a prompt for the parameter. The prompt can be the name of the parameter or directions to the user, for example, "Year" or "Select a year". If the prompt is left blank, and a default parameter value is specified, the default value is used, and the input box for the parameter is not displayed when the user runs the report. If prompt is left blank, and no default parameter value is specified, the report cannot run.

Data Type

By default, the data type for a report parameter is String. If you add a report parameter that requires a data type other than String, you must change the data type of the report parameter. For example, if you create a query that uses a query parameter to filter data in a number field, you must change the report parameter that is created to type Float or Integer.

Available Values

You can define a list of values that the user can choose from when the user runs the report. These are called available values or valid values. An available values list contains a set of value/label pairs. When the report is run, the user sees the label. When the user selects a label, the corresponding value is used as the parameter value .

There are two kinds of available values lists: nonqueried and queried. When you specify a nonqueried list, you can define a static list of value/label pairs. The entire list is contained within the report parameter definition. Each value and label can be a static value or can be generated from an expression.

Specifying a queried available values list causes the report server to retrieve a set of values and labels from a dataset when the report is run. When you specify a queried available values list, you select the dataset, the field to use for value, and the field to use for label. It is recommended that you create a simplified dataset to be used specifically by the parameter, rather than using a more complicated dataset that is also used by data regions within the report. Using the same dataset for both the valid values list and the data regions in the report may produce unexpected results in the valid values list. For information about creating datasets, see Querying a Data Source.

Default Value

You can also define a default value for the parameter. If all parameters in a report have default values, the report will immediately display data when the report is run. If at least one parameter does not have a default value, then the report will only display data after the user enters all parameter values and runs the report.

There are two kinds of default values, nonqueried and queried. A nonqueried default value can be a static value or an expression. When you use a queried default value, you specify the dataset and field from which to retrieve the default value. If the query returns multiple rows, a value from the first row of the returned dataset is used. For information about creating datasets, see Querying a Data Source.

You can also choose to not specify a default value. If you do this, you must specify a prompt.

Adding a Report Parameter

To view instructions about working with parameters, click the following topic:

Query Parameters

When you create a query that contains a query parameter, a report parameter is automatically created based on the name of the query parameter. If you remove or change the name of the query parameter, the corresponding report parameter is not removed or renamed. For more information about queries and query parameters, see Querying a Data Source.

Using Parameters to Sort Data

You can use parameters to add sorting to a report. For more information, see Sorting Data in a Report.

Cascading Parameters

You can define a set of parameters where the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter could present a list of divisions within the company. When the user selects a division, the second parameter is updated with a list of departments within the division. A third parameter could then display a list of employees within the selected department. The value for the employee parameter could then be used to filter the report to a particular employee. This process of filtering a list of parameter values based on a value from another parameter is known as cascading, dependent, or hierarchical parameters.

To create the cascading parameters in the above example, using queried available values lists, do the following:

  1. Create a dataset named Divisions containing a query that retrieves a list of divisions. This should be a simple query, with columns for Division and DivisionID, as in the following SQL query.
    SELECT DivisionID, DivisionName FROM Divisions ORDER BY DivisionName
    
  2. Create a dataset named Departments containing a query that retrieves a list of departments, filtered by division, as in the following SQL query.
    SELECT DepartmentID, DepartmentName FROM Departments WHERE DivisionID = @Division ORDER BY DepartmentName
    
  3. Create a dataset named Employees containing a query that retrieves a list of employees, filtered by department, as in the following SQL query.
    SELECT FirstName + ' ' + LastName AS EmployeeName, EmployeeID FROM Employees WHERE DepartmentID = @Department ORDER BY LastName, FirstName
    
  4. Edit the Division report parameter. The parameter already exists because the @Division query parameter was used in the Departments dataset. Specify a queried available values list that uses the Division dataset, setting the label to DivisionName and the value to DivisionID.
  5. Edit the Department report parameter. The parameter already exists because the @Department query parameter was used in the Employees dataset. Specify a queried available values list that uses the Department dataset, setting the label to DepartmentName and the value to DepartmentID.
  6. Create a new parameter and give it a name of Employee. Set the prompt to "Employee". Specify an available values list that uses the Employees dataset, setting the label to EmployeeName and the value to EmployeeID.
  7. Use the value from the Employee parameter to filter the data in the report to the employee.

For information about creating datasets, For information about creating datasets, see Querying a Data Source. For information about creating and modifying report parameters, see the above section titled "Adding a Report Parameter."

The order of the parameters determines the order in which the parameters are displayed on the report. Order is important for cascading parameters because it also determines the order in which the parameter queries are run. Parameters that are dependent on other parameters must be placed after the parameters on they depend.

Rapidly Changing Valid Values

When you specify available values that change rapidly, the values can become obsolete before the report is run. This can result in a user selecting a value from the list that is no longer valid by the time the user submits the value and runs the report. To avoid this, write queries that return datasets that will not change in the time a typical user takes to select a value and run the report.

Also avoid rapidly changing nonqueried values. For example, if you provide the current date as an available value, write an expression that uses the DateTime.Today property instead of the DateTime.Now property. This eliminates the rapidly changing time portion of the value.

See Also

Adding Interactive Features

Show:
© 2014 Microsoft