Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Report Parameters (Report Builder 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. After you publish a report, you can manage parameters independently from the report definition. By creating linked reports, you can even create multiple sets of parameters for the same report.

If you’re interested in testing your knowledge on how to create and configure report parameters, check out this Configuring SSRS Report Parameters quiz.

When you view a report, 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.

rs_ParameterStory
  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: Add a Parameter to Your Report (Report Builder) or Report Samples (Report Builder and SSRS)

Note Note

You can create and modify report definitions (.rdl) in Report Builder and in Report Designer in SQL Server Data Tools. 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 (SSRS) on the Web at microsoft.com.

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

  • Filter report data at the data source by writing dataset queries that include query variables.

  • 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.

  • 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.

  • Use parameters to relate main reports to drillthrough reports, to subreports, and to linked reports. When you design a set of reports, you can design each report to answer certain questions. Each report can provide a different view or a different level of detail for related information. To provide a set of interrelated reports, create parameters for the related data on target reports.

  • 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.

If you plan to create report snapshots, histories, or subscriptions to a published report, you must understand how report parameters affect the design requirements for the report.

Arrow icon used with Back to Top link Back to Top

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. For more information, see Understanding Report Datasets and Queries.

  • Create a parameter manually from the Report Data pane. For more information, see Add, Change, or Delete a Report Parameter (Report Builder and SSRS).

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

    Note 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 and SSRS) and Report Parts in Report Designer (SSRS).

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 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 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 and SSRS).

Arrow icon used with Back to Top link Back to Top

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.

Note Note

Because parameters are managed independently on the server, republishing a main report with new parameter settings does not overwrite the existing parameters settings on the report.

Report parameters are created in the following ways:

  • Automatically, when you define dataset query that contains query variables. For each query variable, a corresponding dataset query parameter and report parameter with the same names are created. A query parameter can be a reference to a query variable or to an input parameter for a stored procedure.

  • Automatically, when you add a reference to a shared dataset that contains query parameters.

  • Manually, when you create report parameters in the Report Data pane. Parameters are one of the built-in collections that you can include in an expression in a report. Because expressions are used to define values throughout a report definition, you can use parameters to control report appearance or to pass values to related subreports or reports that also use 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:

  • Boolean. The user selects True or False from a radio button.

  • DateTime. The user selects a date from a calendar control.

  • Integer. The user types values in a text box.

  • Float. The user types values in a text box.

  • Text. The user types values in a text box.

When available values are defined for a parameter, the user chooses values from a drop-down list, even when the data type is DateTime.

For more information about report data types, see RDL Data Types.

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 published report. The report parameter values can still be set on a report URL, in a subscription definition, or on the report server.

Internal

Select this option to hide the report parameter. In the published report, 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 note Security 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).

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 the report definition attribute UsedInQuery, a value that indicates whether this parameter directly or indirectly affects the data in a report.

Automatically determine when to refresh

Choose this option when you want the report processor to determine a setting for this value. The value is True if the report processor detects a dataset query with a direct or indirect reference to this parameter, or if the report has subreports.

Always refresh

Choose this option when the report parameter is used directly or indirectly in a dataset query or query parameter expression. This option sets UsedInQuery to True.

Never refresh

Choose this option when the report parameter is not used directly or indirectly in a dataset query or query parameter expression. This option sets UsedInQuery to False.

Caution note Caution

Use Never Refresh with caution. On the report server, UsedInQuery is used to help control cache options for report data and for rendered reports, and parameter options for snapshot reports. If you set Never Refresh incorrectly, you could cause incorrect report data or reports to be cached, or cause a snapshot report to have inconsistent data. For more information, see Report Definition Language (SSRS).

Arrow icon used with Back to Top link Back to Top

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 query parameters. For those times when you cannot filter data at the source, you can use report parameters to provide a user 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 Add Data to a Report (Report Builder 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 User Interface (Report Builder).

  • 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) that corresponds to the data extension.

Arrow icon used with Back to Top link Back to Top

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 Add Cascading Parameters to a Report (Report Builder and SSRS).

Arrow icon used with Back to Top link Back to Top

Report parameters are one of the built-in collections you can reference from an expression. Expression syntax varies depending on whether the report parameter accepts single or multiple values. For more information, see Commonly Used Filters (Report Builder and SSRS) and Parameters Collection References (Report Builder and SSRS).

Drillthrough actions are set on the property pages for any text box for a report item, or on parts of a chart or gauge. 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. How you set the name of the target report for the drillthrough action depends on whether you have an active connection to a report server. For more information, see Specifying Paths to External Items (Report Builder and SSRS).

You can also use URL Access for any URL-addressable report and include report parameter values as part of the URL syntax. For more information, see URL Access (SSRS).

To set parameters for a subreport, use the Parameters page of the Subreport Properties dialog box. How you set the name of the subreport depends on whether you have an active connection to a report server. For more information, see Specifying Paths to External Items (Report Builder and SSRS).

Note Note

If you use a subreport in a tablix data region, the subreport and its parameters will be processed for every row. If there are many rows, consider whether a drillthrough report is more appropriate.

For more information, see Add a Subreport and Parameters (Report Builder and SSRS).

When you design a report, report parameters are saved in the report definition. When you publish a report, report parameters are saved and managed separately from the report definition.

For a published report, you can use the following:

  • Report parameter properties. Change report parameter values directly on the report server independently from the report definition.

  • 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 Create a Linked Report.

  • Report subscriptions.   You can specify parameter values to filter data and deliver reports through subscriptions. For more information, see Subscriptions 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 Set Parameters on a Report URL.

Parameter properties for a published report are generally preserved if you republish the report definition. If the report definition is republished as the same report, and parameter names and data types remain the same, your property settings are retained. If you add or delete parameters in the report definition, or change the data type or name of an existing parameter, you may need to change the parameter properties in the published report.

Not all parameters can be modified in all cases. If a report parameter gets a default value from a query, that value cannot be modified for a published report. Default parameter values that are designated as "Query-based" are defined in the report definition and cannot be modified on the report server. A query-based parameter value is not a constant value. The value that is used at run time is determined when the query runs, or in the case of expression-based parameters, when the expression is evaluated.

Report execution options can affect how parameters are processed. A report that runs as a snapshot cannot use parameters that are derived from a query unless the query includes default values for the parameters.

Arrow icon used with Back to Top link Back to Top

Another way to run a parameterized report is through a URL. You can specify one or more parameters in encoded name-value pairs, using the ampersand character (&) to separate them.

  • Null parameters. Null parameters use special syntax; you must specify isnull for a null value. If you specify a null value for a parameter, all other values specified for that same parameter are ignored. The following example illustrates multiple parameters for null and constant values:

    http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder&region=west&sales:isnull=true

  • Parameters with multiple values. Specify multiple values by repeating the parameter name. For example:

    http://exampleWebServerName/reportserver?%2ffoldercontainingreports%2forders&region=east&region=west

  • Boolean. Use the value 0 for false or 1 for true.

  • Float. Include the decimal separator of the server locale.

  • DateTime. Use the format YYYY-MM-DDTHH:MM:SS, which is based on the International Organization for Standardization (ISO) 8601 standard.

Setting report parameter values is just one way to use URL access syntax to view a report. Another example is to specify a URL parameter to view a report starting at a specific page number. The following example opens the AdventureWorks2012 sample Product Catalog 2008 report to page 30:

http://exampleWebServerName/reportserver?%2fAdventureWorks2012+Sample+Reports%2fProduct+Catalog+2008&rs:Command=Render&rc:Section=30

For more information, see URL Access Parameter Reference.

Arrow icon used with Back to Top link Back to Top

You can define a subscription for an on demand or for a snapshot and specify parameter values to use during subscription processing.

  • On demand report. For an on demand report, you can specify a different parameter value than the published value for each parameter listed for the report. For example, suppose you have a Call Service report that uses a Time Period parameter to return customer service requests for the current day, week, or month. If the default parameter value for the report is set to today, your subscription can use a different parameter value (such as week or month) to produce a report that contains weekly or monthly figures.

  • Snapshot. For a snapshot, your subscription must use the parameter values defined for the snapshot. Your subscription cannot override a parameter value that is defined for a snapshot. For example, suppose you are subscribing to a Western regional sales report that runs as a report snapshot, and the snapshot specifies Western as a regional parameter value. In this case, if you create a subscription to this report, you must use the parameter value Western in your subscription. To provide a visual indication that the parameter is ignored, the parameter fields on the subscription page are set to read-only fields.

Note Note

Data-driven subscriptions can use parameter values that are obtained from a subscriber data source. For more information, see Use an External Data Source for Subscriber Data (Data-Driven Subscription).

If a parameter value changes in the report snapshot after the subscription is defined, the report server deactivates the subscription. Deactivating the subscription indicates that the report has been modified. To activate the subscription, open and then save the subscription.

For more information, see Subscriptions and Delivery (Reporting Services).

Arrow icon used with Back to Top link Back to Top

Report execution options can affect how parameters are processed. Parameterized reports that run as report snapshots use the parameter values defined for the report snapshot. Parameter values are defined in the parameter properties page of the report. A report that runs as a snapshot cannot use parameters that are derived from a query unless the query includes default values for the parameters.

If you change a parameter value in the report snapshot, subscriptions for the snapshot are deactivated. To activate the subscription, open and then save the subscription.

For more information, see Performance, Snapshots, Caching (Reporting Services).

Arrow icon used with Back to Top link Back to Top

Use caution when distributing parameterized reports that contain confidential or sensitive information. A user can easily replace a report parameter with a different value, resulting in information disclosure that you did not intend.

A secure alternative to using parameters for employee or personal data is to select data based on expressions that include the UserID field from the Users collection. The Users collection provides a way to get the identity of the user running the report, and use that identity to retrieve user-specific data.

Security note Security Note

In any report that includes a parameter of type String, 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. When you define a parameter of type String, the user is presented with a text box that can take any value. An available values list limits the values that can be entered. If the report parameter is tied to a query parameter and you do not use an available values list, it is possible for a report user to type SQL syntax into the text box, potentially opening the report and your server to a SQL injection attack. If the user has sufficient permissions to execute the new SQL statement, it may produce unwanted results on the server.

If a report parameter is not tied to a query parameter and the parameter values are included in the report, it is possible for a report user to type expression syntax or a URL into the parameter value, and render the report to Excel or HTML. If another user then views the report and clicks the rendered parameter contents, the user may inadvertently execute the malicious script or link.

To mitigate the risk of inadvertently running malicious scripts, open rendered reports only from trusted sources. For more information about securing reports, see Secure Reports and Resources.

Arrow icon used with Back to Top link Back to Top

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.