Using Parameters to Control Report Data
Report parameters typically filter report data that is retrieved from a data source. Filtering data at the data source can improve performance for processing and viewing a report. For the times when you cannot filter data at the source, you can use parameters to filter report data after it is retrieved. You can also sort and group data in a report based on report parameters.
Filtering Data at the Data Source
To filter data at the data source, use query parameters in your dataset query. For each query parameter, Reporting Services creates a corresponding report parameter. When the user selects values for the report parameter, the values are passed to the query. When the query runs on the data source, only those values specified by the user are retrieved for the report. Properties such as a user prompt and default values are applied to the report parameter.
Query Parameters
Report parameters are automatically created when you define query parameters. In Report Builder, report parameters are automatically created when you set a prompt on a filter clause.
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. When you define the query for each dataset, the query designer identifies variables within the query command text and creates a query parameter for each one. Not every data source and data provider combination support queries with variables. For more information about the query syntax expected by the data source, see Using Query Parameters with Specific Data Sources (Reporting Services).
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.
You can create additional dataset parameters on the Parameters page of the Dataset Properties dialog box. Parameters that you create are not changed when the query changes.
The default value for each query parameter is set to an expression that evaluates to the corresponding report parameter. To change the default, use the Parameters page of the Dataset Properties dialog box. 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 [@MyParameter]. For more information, see Dataset Properties Dialog Box, Parameters. 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 parameters that are automatically created use the following defaults:
Single-value
Data type Text
Prompt set to the name of the parameter
No default values
No available values
You may need to change these properties based on the type of data that the query parameter represents. For more information, see Creating Report Parameters and Setting Report Parameter Properties.
Note
When you remove or change the name of a query parameter, the corresponding report parameter is not automatically removed or changed. If you remove a query parameter and do not need the corresponding report parameter, you must delete it manually. If you change the name of a query parameter, a new report parameter corresponding to the changed name is created when you save the query. You can rename the report parameter to match the new query parameter name, and update the dataset parameter properties to link the query parameter to the report parameter.
Dependent or Cascading Parameters
When you create a query that uses multiple query parameters, you can create a set of cascading parameters. Cascading parameters provide a way of filtering a very large number of parameter values down to a manageable number of values. For example, suppose a query includes the parameters @Category, @Subcategory, and @Product, where the list of subcategories is dependent on @Category, and the list of products is dependent on the @Subcategory. When a user chooses a value for the report parameter Category, the values for Subcategory are limited to valid values for the chosen category. After the user selects a value for Subcategory, the choices for Product have already been filtered by the choice for category and subcategory. Using this technique, you can reduce the valid choices for a parameter down to a reasonable number of values.
To design cascading parameters, you must include the following items in your report:
The main dataset query, which has multiple related query parameters.
An ordered list of report parameters, each bound to a query parameter. Typically, these are automatically created from the main query. Each dependent parameter must follow the parameter it is dependent on. The order of parameters can be changed in the Report Data pane, where you can move parameters up and down in the collection. For more information, see How to: Change the Order of a Report Parameter (Reporting Services).
A separate dataset for each report parameter that supplies its available values. It is important to use the same case-sensitive spelling for each query parameter so that the query parameters and report parameters are linked properly. The query for each set of available values for each report parameter must provide only values that make sense in the context of the main query.
In the example, the report parameter Product is dependent on Subcategory, which is dependent on Category. Category must come first, followed by Subcategory, and then Product. The query for the dataset that provides available values for Category must show all categories that are valid for the main query. The query that provides available values for Subcategory once you have a chosen a category, should provide values that are valid for the given Category, plus any constraints that exist in the main query.
For more information, see How to: Add Cascading Parameters to a Report (Reporting Services).
Filtering Report Data After Running a Query
You can also create report parameters and use them in filter expressions to filter data in a report dataset, a data region, or a Tablix group. For more information, see Filtering Data in a Report and How to: Add a Filter (Reporting Services).
Report parameters are part of a report definition when you author a report but can be managed independently after a report is published. After the report definition is published, you can modify parameter properties using Report Manager. For more information, see Setting Properties on a Published Report.
Working with 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. You can also use a report variable or a group variable as a way to calculate a value once and keep it constant for while the report is processed. For more information, see Using Report and Group Variables Collection References in Expressions (Reporting Services).