Add Parameters and Filter Data

This tutorial demonstrates how to add parameters to a report, provide them with default values, create multi-value and cascading parameters (i.e., filter parameter values based on the current value of another parameter). The created parameters are used to filter data at the data source level. The last document section describes other uses of report parameters not related to filtering data.

This tutorial consists of the following sections:

Create a Data Source

In the previous tutorials, you bound a report to a data source and constructed the report layout with data fields from this data source. This tutorial demonstrates how to filter data at the level of this data source.

You need to add another data source containing the same queries to provide values for report parameters.

report-server-data-sources-for-parameters

Add a Report Parameter

A report parameter stores one or more values that 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 order selected in Print Preview:

  1. Select the Parameters node in the Field List panel and click the plus button to create a new report parameter.

    report-server-field-list-add-parameter

  2. Click the Edit report-server-parameters-edit-button button for the created parameter to expand the property list. Specify the parameter's Name (by which it can be referred to 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.

    report-server-field-list-parameter-settings

    Set the Look-Up Settings Type property to Dynamic List to supply parameter values from a dedicated data source. This enables the following look-up settings:

    • Filter String (optional)

      Enables you to filter the list of parameter values (e.g., to create cascading parameters that are described further down in this tutorial).

    • Data Source

      Specifies the data source to which the parameter is bound.

    • Data Member

      Specifies the name of a data column storing the parameter values.

    • Display Member (optional)

      Specifies the name of a data field providing parameter value descriptions displayed in Print Preview.

    • Value Member

      Specifies the name of a data field providing the parameter values.

  3. Access a data source providing data for a report and click the Edit query button for the query that you want to filter (Orders query for this step).

    report-server-field-list-edit-query

  4. In the invoked Data Source Wizard page, click the Run Query Builder button.

    report-server-data-surce-wizard-run-query-builder

  5. In the Query Builder, switch to the Query Properties section and click the ellipsis button for the Filter property.

    In the invoked Filter Editor, construct an expression in which the OrderID data field is compared to a query parameter value. Expand the drop-down menu for a value placeholder and select Parameter.

    report-server-query-builder-filter-editor

    This converts the value placeholder into a parameter placeholder. Click this placeholder and select Create new parameter.

    report-server-query-builder-filter-editor-create-parameter

    In the dedicated editor, specify the query parameter name.

    report-server-query-builder-filter-editor-parameter-name

    Click Save to save the filter condition and close the editor.

    Click OK in the Query Builder, and then, click Next on the wizard page to proceed.

  6. On the following wizard page, map the created query parameter to the report parameter. Expand the drop-down list for the parameter's Type property and select Expression. Then, click the ellipsis button for the Value property and specify the report parameter in the invoked Expression Editor.

    report-server-data-surce-wizard-map-parameters

Switch to Print Preview by clicking the Preview report-server-web-preview-button button in the main toolbar. Select a required order in the parameter's lookup editor. Click the Submit button to pass the corresponding value to the filter expression and generate the document.

report-server-add-parameter-result

Create a Multi-Value Parameter

Do the following to enable a report parameter to accept multiple values at once and filter the report against these values:

  1. Go to the Field List and enable the parameter's MultiValue option.

    report-server-parameter-multi-value-property

  2. Once again, run the Query Builder for the Orders query and invoke the Filter Editor. Customize the filter expression so that the OrderID data field is compared to all of the parameter values.

    report-server-multi-valueparameter-filter-editor

Switch to Print Preview and select one or more values in the parameter's lookup editor. Click Submit to pass the corresponding values to the report and generate the document.

report-server-multi-value-parameter-result

Create Cascading Parameters

The following steps describe how to create a new parameter and filter its values depending on the values selected for another parameter:

  1. Click the plus button for the Parameters node in the Report Explorer to create a new parameter.
  2. Specify the parameter's name, description and other options as you did before. Ensure that you correctly provide look-up settings (for this tutorial, set the Data Member property to the detail query and assign the Value Member and Display Member properties to the same ProductName data field).

    report-server-add-cascading-parameter

  3. Click the ellipsis button for the parameter's Filter String property to filter the list of available products according to the selected order. In the invoked editor, construct an expression in which the OrderID data field is compared with another parameter value.

    report-server-cascading-parameter-filter-condition

  4. Click the Edit query button for the detail query to filter data in the detail report.

    report-server-data-source-edit-detail-query

  5. Run the Query Builder and invoke the Filter Editor. Create a new query parameter as described above and specify a filter expression to compare the Product Name field with this parameter.

    report-server-detail-query-filter-condition

    Close the editor and complete the Query Builder.

  6. On the next wizard page, map the query parameter to the corresponding report parameter.

    report-server-detail-query-map-parameters

Switch to Print Preview and select the required orders and products. Click Submit to generate the document.

report-server-cascading-parameters-result

User-Specific Functions

The Report and Dashboard Server provides a particular set of functions that enable you to access information about a current user. Using these functions, you can make a data model return specific subsets of data to different users. This restricts access to sensitive information in your database, and users cannot access and modify these functions unless they have the required permissions.

However, non-privileged users are still able to use these functions at the document level (to further filter the data available to them).

To learn more on this, see the "User-Specific Functions" section in the Manage Data Models and Connect to Data document.

Other Uses of Report Parameters

Besides filtering report data, you can use report parameters to accomplish the following tasks:

  • Data Binding

    You can bind a report control to a parameter and display its value in the report by dropping 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.

    report-server-bind-control-to-parameter

  • Calculated Fields

    Parameters can participate in constructing expressions for calculated fields as well as standard data fields. The only difference is that a parameter is inserted into the expression text using the Parameters. prefix before its name.

    report-server-expression-with-parameter

See the Schedule a Document and Select Subscribers document to learn how to pass report parameters to a scheduled report.

See Also