Report Parameters (Report Builder and Report Designer)
Applies To: SQL Server 2016
This topic describes the common uses for Reporting Services report parameters, the properties you can set, and much more. Report parameters enable you to control report data, connect related reports together, and vary report presentation. You can use report parameters in paginated reports you create in Report Builder and Report Designer, and also in mobile reports you create in SQL Server Mobile Report Publisher. Read more about Report Parameters Concepts.
|Applies to: Report Builder, Reporting Services SharePoint mode and Native mode|
To try adding a parameter to a report yourself, see Tutorial: Add a Parameter to Your Report (Report Builder).
The following information is covered in this topic.
Here are some of the most common ways to use parameters.
Control Paginated and Mobile Report Data
Filter paginated report data at the data source by writing dataset queries that contain variables.
Filter data from a shared dataset. When you add a shared dataset to a paginated 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.
Filter data from a shared dataset in a SQL Server mobile report. See Create mobile reports with SQL Server Mobile Report Publisher for more information.
Enable users to specify values to customize the data in a paginated report. For example, provide two parameters for the start date and end date for sales data.
Connect Related Reports
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.
Vary Report Presentation
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.
Enable users to customize report data and appearance by including parameters in an expression.
For more information, see Parameters Collection References (Report Builder and SSRS).
When you view a report that has parameters, the report viewer toolbar displays each parameter so you can interactively specify values. The following illustration shows the parameter area for a report withparameters @ReportMonth, @ReportYear, @EmployeeID, @ShowAll, @ExpandTableRows, @CategoryQuota, and @SalesDate.
Parameters pane The report viewer toolbar displays a prompt and default value for each parameter. You can customize the layout of parameters in the parameters pane. For more information, see Customize the Parameters Pane in a Report (Report Builder).
@SalesDate parameter The parameter @SalesDate is data type DateTime. The prompt Select the Date appears next to the text box. To modify the date, type a new date in the text box or use the calendar control.
@ShowAll parameter The parameter @ShowAll is data type Boolean. Use the radio buttons to specify True or False.
Show or Hide Parameter Area handle On the report viewer toolbar, click this arrow to show or hide the parameters pane.
@CategoryQuota parameter The parameter @CategoryQuota is data type Float, so it takes a numeric value. @CategoryQuota is set to allow multiple values.
View Report After you enter parameter values, click View Report to run the report. If all parameters have default values, the report runs automatically on first view.
You can create report parameters in a few different ways.
Dataset query or stored procedure with parameters
Add a dataset query that contains variables or a dataset stored procedure that contains input parameters. A dataset parameter is created for each variable or input parameter, and a report parameter is created for each dataset parameter.
This image from Report Builder shows:
The report parameters in the Report Data pane.
The dataset with the parameters.
The Parameters pane.
The parameters listed in the Dataset Properties dialog box.
The dataset can be embedded or shared. 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.
For more information, see Dataset Query in this topic.
Create a parameter manually
Create a parameter manually from the Report Data pane. 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 part with a parameter
Add a report part that contains references to a parameter or to a shared dataset that contains variables.
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).
The following are options for selecting parameter values in the report.
Select a single parameter value from a drop-down list.
Select multiple parameter values from a drop-down list.
Select a value from a drop-down list for one parameter, which determines the values that are available in the drop-down list for another parameter. These are cascading parameters. Cascading parameters enables you to successively filter parameter values from thousands of values to a manageable number.
For more information, see Add Cascading Parameters to a Report (Report Builder and SSRS).
Run the report without having to first select a parameter value because a default value has been created for the parameter.
You can change the report parameter properties by using the Report Properties dialog box. The following table summarizes the properties that you can set for each parameter:
|Name||Type a case-sensitive name for the parameter. The name must begin with a letter and can have letters, numbers, an underscore (_). The name cannot have spaces. For automatically-generated parameters, the name matches the 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.
Note that 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 ** 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.
Choose this option when the report parameter is used directly or indirectly in a dataset query or parameter expression. This option sets UsedInQuery to True.
Choose this option when the report parameter is not used directly or indirectly in a dataset query or parameter expression. This option sets UsedInQuery to False.
** 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).
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 for the 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 Report Datasets (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.
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 URL Access (SSRS).
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 dataset query, that value cannot be modified for a published report and cannot be modified on the report server. 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.
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.
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 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).
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.
If a report parameter is not tied to a dataset 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.
This section lists procedures that show you, step by step, how to work with parameters and filters.