Using Parameters to Control Report Data (Report Builder 2.0)
Report parameters can be used in two ways: to filter data at the source of data or to filter data in the report. Filtering data at the data source can improve performance for processing and viewing a report. If you cannot filter data at the source, you can use parameters to filter report data in the report. You can also use parameters to sort and organize data in a report.
For an overview of filtering data, see Filtering Data in a Report (Report Builder 2.0).
Use this topic to learn about filtering data by using parameters.
Filtering Data at the Data Source
To filter data at the data source, use query parameters in your dataset query. In some query designers, you can add fields to a Filter pane and select a parameter option for each field that you want as a report parameter. In other query designers, you can add query parameters to the query command. When you save the query, the command text is analyzed. For each query parameter, a corresponding report parameter is created. Use the Parameters node in the Report Data pane to view the list of report parameters.
When a report runs, the user selects values for each report parameter, and 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. You can also specify the prompt that labels the report parameter on the report toolbar.
Query Parameters
Report parameters are automatically created when you define query parameters.
When you define a dataset, you specify a particular type of data source, for example, Microsoft SQL Server. When you define the query for each dataset, the associated 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 (Report Builder 2.0).
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. The relational query designer automatically builds a query for you and provides an option to create a query variable for each filter that you set. For more information, see Relational Query Designer User Interface (Report Builder 2.0).
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 (Report Builder 2.0). 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 Builder 2.0).
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 (Report Builder 2.0).
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.
In Report Builder 1.0, report parameters are automatically created when you set a prompt on a filter clause.
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 (Report Builder 2.0).
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 (Report Builder 2.0).
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 (Report Builder 2.0) and How to: Add a Filter (Report Builder 2.0).
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" in the Reporting Services documentation in SQL Server Books Online.
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 Collections References in Expressions (Report Builder 2.0).
See Also