Share via

  • Article

Parameters (Report Builder 3.0 and SSRS)

Report parameters enable you to control report data, connect related reports together, and vary report presentation. To design a report that uses parameters effectively, you must understand how parameters and dataset queries are related, how to include parameters in expressions, and how to manage parameters independently from a report definition on the report server or SharePoint site.

You can create report parameters in the following ways:

  • Create or add a dataset, embedded or shared, that has a query that contains query variables.

  • Create a parameter manually from the Report Data pane.

  • Add a report part that contains references to a parameter or to a shared dataset that contains variables.

After you publish a report, you can manage parameters independently from the report definition. You can even create multiple sets of parameters for the same report. For more information, see Setting Parameter Properties for a Published Report and Adding, Modifying, and Deleting Linked Reports.

The report viewer toolbar displays each parameter so that a user can interactively specify values. The following illustration shows the parameter area for a report that has parameters @StartDate, @EndDate, @Subcategory, and @ShowAllRows.

Parameters on report viewer toolbar

  1. Parameters pane   The report viewer toolbar displays a prompt and default value for each parameter. Parameter layout on the toolbar is formatted automatically. The order is determined by the order that parameters appear in the Report Data pane.

  2. @StartDate and @EndDate parameters   The parameter @StartDate is data type DateTime. The prompt Start Date appears next to the text box. To modify the date, type a new date in the text box or use the calendar control.

    The parameter @EndDate appears next to @StartDate.

  3. @Subcategory parameter   The parameter @Subcategory is data type Text. Because @Subcategory has an available values list, valid values appear in a drop-down list. You must choose values from this list. Because @Subcategory is multivalued, a Select All option appears that enables you to clear all and select all values in the list.

  4. @ShowAllRows parameter   The parameter @ShowAllRows is data type Boolean. Use the radio buttons to specify True or False.

  5. Show or Hide Parameter Area handle   On the report viewer toolbar, click this arrow to show or hide the parameters pane.

  6. Parameters button   In Report Builder preview, on the Ribbon, click the Parameters button to show or hide the parameters pane.

  7. View Report button   On the report viewer toolbar, click View Report to run the report after you enter parameter values. If all parameters have default values, the report runs automatically on first view.

For more information about using parameters right away, see Tutorial: Adding a Parameter to Your Report (Report Builder 3.0), Tutorial: Adding Parameters to a Report (SSRS), or Report Samples (Report Builder 3.0 and SSRS)

Note

Parameters can be published as a separate report part for data regions that have dependent datasets with parameters. Although parameters are listed as a report part, you cannot add a report part parameter directly to a report. Instead, add the report part, and any necessary report parameters are automatically generated from dataset queries that are contained or referenced by the report part. For more information about report parts, see Report Parts (Report Builder 3.0) and Report Parts in Report Designer (SSRS).

Note

You can create and modify report definitions (.rdl) in Report Builder 3.0 and in Report Designer in Business Intelligence Development Studio. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder 3.0 (SSRS) on the Web at microsoft.com.

In This Article

Common Uses for Parameters

Types of Parameters

Working with Report Parameters

Working with Dataset Parameters

Cascading Parameters

Passing Parameters to Drillthrough or Subreports

Managing Parameters on a Report Server of SharePoint Site

In This Section

Related Topics

Common Uses for Parameters

Here are some of the most common ways to use parameters:

  • Enable users to specify values to customize the data in a report. For example, provide two parameters for the start date and end date for sales data.

  • Enable users to specify values to help customize the appearance of a report. For example, provide a Boolean parameter to indicate whether to expand or collapse all nested row groups in a table.

  • Create a drop-down list of values to choose from, so that a user can only select a valid value.

  • Create a default value for each parameter, so that the report runs automatically on first view.

  • Create cascading values, so that the drop-down list for one parameter is the set of values that are valid based on the selection for a previous parameter. This enables you to successively filter parameter values from thousands of values to a manageable number.

  • Customize sets of parameters for multiple users. Create two linked reports based on a sales report on the report server. One linked report uses predefined parameter values for sales persons and the second linked report uses predefined parameter values for sales managers. Both reports use the same report definition.

  • Enable users to filter data from a shared dataset. When you add a shared dataset to a report, you cannot change the query. In the report, you can add a dataset filter that includes a reference to a report parameter that you create.

Arrow icon used with Back to Top linkBack to Top

Types of Parameters

Reports include two types of parameters: dataset parameters and report parameters.

  • Dataset parameter   A dataset parameter for an embedded dataset or a shared dataset is created automatically for each query variable in the dataset query command. Some query designers provide an area for adding applied filters based on field names and an option to add a dataset parameter based on the field name.

    Dataset parameters are configured after you define the dataset query. You can specify default values independently for each dataset parameter. When you add a shared dataset to a report, dataset parameters that are marked internal cannot be overridden in the report. You can override dataset parameters that are not marked internal. You can also create additional parameters. For more information, see Working with Dataset Parameters in this topic.

  • Report parameter   A report parameter is created automatically for each dataset parameter. You can also create report parameters manually. You can configure report parameters so that a user can interactively enter values to help customize the contents or appearance of a report. You can also configure report parameters so that a user cannot change preconfigured values. Report parameters are managed independently from a published report on the report server. For more information, see Working with Report Parameters in this topic.

    You can also save a report parameter separately from the report as a report part. Report parts are stored on the report server and available for others to use in their reports. Report parts that are parameters cannot be managed from the report server. You can search for parameters in the Report Part Gallery and after you add them, configure them in your report. For more information, see Report Parts (Report Builder 3.0).

Most of the time, you will be working with report parameters.

Arrow icon used with Back to Top linkBack to Top

Working with Report Parameters

The most common use of parameters is to vary report data retrieved by dataset queries. In this scenario, users are prompted for a value or values when they run the report, and the dataset query retrieves only the data that is requested. The dataset query includes query parameters and the report processor automatically creates report parameters that are indirectly linked to the query parameters.

By default, parameters that are created automatically use an autogenerated name, prompt, and data type. You can change the report parameter properties from the Report Data pane by using the Report Properties dialog box. The following table summarizes the properties that you can set for each parameter:

Property

Description

Name

Type a case-sensitive name for the parameter. The name must begin with a letter and have letters, numbers, an underscore (_), and no spaces. For automatically-generated parameters, the name matches the query parameter in the dataset query. By default, manually-created parameters are similar to ReportParameter1.

Prompt

The text that appears next to the parameter on the report viewer toolbar.

Data type

A report parameter must be one of the following data types: Text, Integer, Float, DateTime, Boolean.

The data type determines the way that a report parameter appears on the report viewer toolbar. The data type can also be important when you write expressions that include a reference to the parameter.

Allow blank value

Select this option if the value of the parameter can be an empty string or a blank.

If you specify valid values for a parameter, and you want a blank value to be one of the valid values, you must include it as one of the values that you specify. Selecting this option does not automatically include a blank for available values.

Allow null value

Select this option if the value of the parameter can be a null.

If you specify valid values for a parameter, and you want null to be one of the valid values, you must include null as one of the values that you specify. Selecting this option does not automatically include a null for available values.

Allow multiple values

Provide available values to create a drop-down list that your users can choose from. This is a good way to ensure that only valid values are submitted in the dataset query.

Select this option if the value for the parameter can be multiple values that are displayed in a drop-down list. Null values are not allowed. When this option is selected, check boxes are added to the list of available values in a parameter drop-down list. The top of the list includes a check box for Select All. Users can check the values that they want.

If the data that provides values changes rapidly, the list the user sees might not be the most current.

Visible

Select this option to display the report parameter at the top of the report when it is run. This option allows users to select parameter values at run time.

Hidden

Select this option to hide the report parameter in the report. The report parameter values can be set on a report URL, in a subscription definition, or on the report server using Report Manager.

Internal

Select this option to hide the report parameter. The report parameter can only be viewed in the report definition.

Available values

If you have specified available values for a parameter, the valid values always appear as a drop-down list. For example, if you provide available values for a DateTime parameter, a drop-down list for dates appears in the parameter pane instead of a calendar control.

To ensure that a list of values is consistent among a report and subreports, you can set an option on the data source to use a single transaction for all queries in the datasets that are associated with a data source.

Security noteSecurity Note
In any report that includes a parameter of data type Text, be sure to use an available values list (also known as a valid values list) and ensure that any user running the report has only the permissions necessary to view the data in the report. For more information, see Security (Report Builder 3.0).

Default values

Set default values from a query or from a static list.

When each parameter has a default value, the report runs automatically on first view.

Advanced

Set a value that indicates whether this parameter directly or indirectly affects the data in a report.

On the report server, this value is used to determine cache options for reports and report data.

For more information, see Report Parameter Properties Dialog Box, Advanced (Report Builder 3.0).

Arrow icon used with Back to Top linkBack to Top

Working with Dataset Parameters

Query parameters are defined when you build a query in a query designer. Some data extensions provide a way to select which fields to include as parameters. Other data extensions identify query variables in the query command. A query parameter is automatically created for every query variable.

Not all data sources support queries parameters. For those times when you cannot filter data at the source, you can use report parameters to provide a users with the ability to filter data after it has been retrieved for the report. While all query parameters have corresponding report parameters, you can define report parameters independently. Report parameters can also be used to change the appearance or organization of a report after the data has been retrieved.

To filter data in the dataset query, you can include a restriction clause that limits the retrieved data by specifying values to include or exclude from the result set.

Use the query designer associated with a data source to help build a parameterized query.

  • For Transact-SQL queries, different data sources support different syntax for parameters. Support ranges from parameters that are identified in the query by position or by name. For more information, see topics for specific external data source types in Adding Data to a Report (Report Builder 3.0 and SSRS). In the relational query designer, you must select the parameter option for a filter to create a parameterized query. For more information, see Relational Query Designer (Report Builder 3.0).

  • For queries that are based on a multidimensional data source such as Microsoft SQL Server Analysis Services, SAP NetWeaver BI, or Hyperion Essbase, you can specify whether to create a parameter based on a filter that you specify in the query designer. For more information, see the query designer topic in Query Designers (Report Builder 3.0) that corresponds to the data extension.

Arrow icon used with Back to Top linkBack to Top

Cascading Parameters

You can create cascading parameters, where the user chooses a value for each parameter in a specific order, and each choice successively limits the values for the next parameter. Cascading parameters can reduce potentially thousands of choices to a manageable number. For example, a user chooses a product category, then chooses from the list of available products in that category, then chooses a size, and then a color.

For more information, see How to: Add Cascading Parameters to a Report (Report Builder 3.0 and SSRS).

Arrow icon used with Back to Top linkBack to Top

Passing Parameters to Drillthrough and Subreports

Parameters are used to connect reports to subreports and drillthrough reports. These report parameters might be hidden, using values in the main report to select the correct data for the connected report, or they might be visible to the user at run time.

For more information, see Using Parameters to Connect to Other Reports (Report Builder 3.0 and SSRS).

Managing Parameters on a Report Server or SharePoint Site

After a report is published, parameters can be managed independently from the report definition. Depending on how you configure the parameter in the report at design time, report server administrators can modify many parameter properties for a report or shared dataset. If you are designing a report that you intend to schedule for caching or subscriptions, there might additional requirements that apply to parameters. For more information, see the following topics in the Reporting Services documentation in SQL Server Books Online.

  • **Cached reports.   **To create a cache plan for a report, each parameter must have a default value. For more information, see Caching Reports (SSRS).

  • **Cached shared datasets.   **To create a cache plan for a shared dataset, each parameter must have a default value. For more information, see Caching Reports (SSRS).

  • Linked reports.   You can create linked reports with preset parameter values to filter data for different audiences. For more information, see Adding, Modifying, and Deleting Linked Reports.

  • Report subscriptions.   You can specify parameter values to filter data and deliver reports through subscriptions. For more information, see Setting Parameters in a Subscription and Subscription and Delivery (Reporting Services).

  • URL access.   You can specify parameter values in a URL to a report. You can also run reports and specify parameter values using URL access. For more information, see URL Access.

Arrow icon used with Back to Top linkBack to Top