Using Parameters to Control Report Data

  • Article

New: 17 July 2006

You can use parameters to control which data gets retrieved from a data source when a report is processed, and you can use parameters to filter the data after it has been retrieved. Good practice recommends that you limit the data you retrieve from the data source to that which is specifically needed for the report. However, when you use stored procedures to retrieve data, you cannot control what is retrieved from the query and therefore need to filter the report data after it is retrieved.

Filtering Data at the Data Source

You use query parameters to help specify exactly which data you want to retrieve from the data source. They allow you to filter data at the server, before it is sent to the report.

When you define a dataset with a query that includes variables, the query designer component of Reporting Services creates a query parameter for each variable. A corresponding report parameter is created for each query parameter so that users or report authors can select values for the report parameters and have them passed in the query to the data source.

Query Parameters

Query parameters are defined as part of the query definition for a dataset. Because each report dataset defines a single query, query parameters are defined as properties on the dataset.

When you define a dataset, you specify a particular type of data source, for example, Microsoft SQL Server. Report Designer opens a query designer designed to work with that data source type, or the generic query designer that can work with any type of data source. When you define the query for this data source, the query designer identifies variables within the query command text and creates a query parameter for each one. For more information about the query syntax expected by the data source, see Using Query Parameters with Specific Data Sources.

For a SQL Server data source, queries typically include variables in the WHERE clause of a Transact-SQL statement to limit the scope of the data returned when a query runs. In a similar way, an Analysis Services data source query typically includes MDX variables used in a FILTER clause. Queries can also include variables passed as inputs to stored procedures or user-defined functions.

Every time you modify the query for a dataset, the query is reprocessed. If you change a query by removing or renaming a variable, the query parameters will reflect those changes. Only those variables that exist in the query command text will be query parameters in the dataset query definition.

The default value for each query parameter is set to an expression that evaluates to the corresponding report parameter. This parameter binding is defined on the Parameters tab of the Dataset properties. For example, for a SQL Server data source, if the query parameter is @MyParameter, the report parameter is MyParameter and the value for @MyParameter is set to the expression =Parameters!MyParameter.Value. For more information, see Dataset (Parameters Tab, Report Designer). You can manually edit the query parameters and set their default values. For more information, see How to: Associate a Query Parameter with a Report Parameter (Report Designer).

Note

When you remove or change the name of a query parameter, the corresponding report parameter is not automatically removed or changed.

Report Parameters

In Report Designer, report parameters are automatically created when you define a dataset query that includes variables. In Report Builder, report parameters are automatically created when you set a prompt on a filter clause. You can also create report parameters manually in the Report Parameters dialog box that are not bound to query parameters.

  • The report parameter data type and other report parameter properties affect the presentation of the parameter on the report toolbar. Depending on the data type of the parameter, you can set report parameter properties using radio buttons, text boxes, drop-down lists, calendar controls, or multiple check boxes.
  • A report parameter can be single-valued or multivalued. Multivalued parameters allow a user to select more than one value for the parameter.
  • A report parameter can be dependent on another report parameter. The order of report parameters is significant. A parameter later in the list of parameters can be dependent on a parameter earlier in the list. This allows you to define a set of parameters known as cascading parameters, where the list of values for one parameter depends on the value chosen in another parameter.
  • A report parameter can be used in an expression. Expressions that include parameters can be used anywhere an expression can be used. When the report runs, the value in each parameter is substituted in the expression. In this way, user selections of parameters can conditionally control many aspects of the appearance and content of a report, including hiding rows and columns, sorting and filtering data, and handling null data.

Report parameters are part of a report definition when you author a report but can be managed independently after a report is published. In Report Designer data or layout view, you can edit the parameters defined for the report. For more information, see How to: Add, Edit, or Delete a Report Parameter (Report Designer). After the report definition is published, you can modify parameter properties using Report Manager. For more information, see Setting Parameter Properties for a Published Report.

Rapidly Changing Valid Values for a Parameter

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 for valid values lists 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.

Filtering Report Data

You can filter data after it has been retrieved from the data source by defining a filter expression on a dataset that includes a parameter reference. In this way, when a report reader selects values for a parameter, when the report is processed, only data passing through the filter is displayed in the report.

See Also

Tasks

How to: Associate a Query Parameter with a Report Parameter (Report Designer)

Concepts

Defining Report Datasets

Other Resources

Tutorial: Adding Parameters to a Basic Tabular Report
Tutorial: Advanced Features Using Parameters
Defining Report Data Sources
Data View (Report Designer)

Help and Information

Getting SQL Server 2005 Assistance