This tutorial demonstrates how to add parameters to a report, provide them with default values, create multi-value and cascading parameters (filter parameter values based on another parameter's current value). The created parameters are used to filter data at the data source level. The last document section describes other report parameter uses not related to filtering data.
Open an existing report or create a new one as described in the Create and Customize Reports document.
Bind a report to the required data source and prepare the report layout containing data fields from this data source. Add one more data source to provide values for report parameters.
This tutorial uses a report that is bound to the Northwind database's Products by Category table (Northwind1 data source) and grouped against the CategoryName data field. This document demonstrates how to filter data at this data source's level.
Report parameters obtain their values from another data source (Northwind2) containing the same table.
A report parameter stores values which can be modified in Print Preview and passed to a report before its creation.
The following steps illustrate how to make a report display data corresponding to a specific product category selected in Print Preview:
Right-click the Parameters node in the Field List and select Add Parameter in the context menu to create a new report parameter that lists product categories.
In the invoked Add New Parameter dialog, specify the parameter's Name (for reference in the filter expression) and Description (to display in Print Preview).
Set the Type property corresponding to the type of a data field against which this parameter should be compared in the filter expression.
Enable the Supports the collection of standard values option to supply parameter values from a data source. This enables the following options on the Dynamic values tab:
Specifies the data source to which the parameter is bound.
Specifies the name of a data column storing the parameter values.
Specifies the name of a data field providing the parameter values.
Display Member (optional)
Specifies the name of a data field providing parameter value descriptions displayed in Print Preview.
Filter String (optional)
Enables you to filter the list of parameter values (for example, to create cascading parameters that are described further down in this tutorial).
In the Report Explorer, select a data source providing data for a report (Northwind1 in this example). Switch to the Property Grid, access the query you want to filter and click the Filter String property's ellipsis button.
In the invoked Filter Editor, construct an expression in which the CategoryName data field is compared to the created parameter value. You can access the parameter by clicking the icon on the right until it turns into a question mark.
Switch to the Print Preview tab and select a required category in the parameter's lookup editor. Click Submit to pass the corresponding value to the filter expression and generate the document.
Do the following to enable a report parameter to accept multiple values at once and filter the report against these values:
Switch to the Field List and right-click the previously created parameter. Select Edit Parameters in the invoked context menu.
Set the parameter's Multi-Value property to Yes in the Parameter Collection Editor.
Once again, access the dedicated data source in the Properties window and invoke the Filter Editor. Customize the expression so that the CategoryName data field is compared to all parameter values.
Switch to Print Preview and select one or more categories in the parameter's lookup editor. Clicking Submit passes the corresponding values to the report and generates the document.
The following steps describe how to filter the list of parameter values depending on the values selected for another parameter:
Add a new parameter that lists available products using the Field List.
In the invoked Add New Parameter dialog, specify the parameter's settings and click the ellipsis button for its Filter String property to filter the list of available products according to the selected category.
Construct a filter expression in which the CategoryName data field is compared to another parameter value.
Invoke the Filter Editor for the dedicated data source. Update the filter expression to compare the CategoryName and ProductName data fields with the Category and Product parameters, respectively.
Switch to Print Preview and select the required categories and products. Click Submit to generate the document.
Besides filtering report data, you can use report parameters to solve the following tasks:
You can bind a report control to a parameter and display its value in the report by dragging the parameter from the Field List onto the required band. This creates a Label control bound to the parameter as with an ordinary data field.
Calculated Fields and Conditional Formatting
Parameters can participate in constructing expressions for calculated fields and formatting rules as standard data fields. The only difference is that a parameter is inserted into the expression text using the Parameters. prefix before its name.
See the Schedule a Document and Select Subscribers topic to learn how to pass report parameters to a scheduled report.