Working with Parameters in Reporting Services

Article
12/03/2008

In a Reporting Services, parameters are used to specify the data to use in a report, connect related reports together, and vary report presentation. There are two types of parameters in a report: query parameters and report parameters.

When you define a dataset query that includes variables, Reporting Services creates corresponding query parameters. Query parameters are used to limit data retrieved from the data source to just the data needed for the report.
Report parameters appear on the report toolbar and allow report readers to select or enter values that are used when a report is processed. In Report Designer, report parameters are created automatically from query parameters. In Report Builder, report parameters are created when you set a prompt on a filter clause. You can also define report parameters that are not tied to query parameters. In expressions, you can include references to report parameters using the global Parameters collection. Because expressions can be used throughout a report definition to control content or appearance, report parameters provide a powerful way to customize a report.

The following list briefly summarizes the key aspects of parameters and the way they are used in Reporting Services:

Report parameters are processed by the report server, while query parameters are processed on the data source server.
Each query parameter value, by default, is mapped to the corresponding report parameter value. This allows users to select values for report parameters and have them passed in the query to limit the data retrieved from the data source.
Report parameters that are not bound to query parameters are used to vary presentation, filter data, control sorting and grouping, and vary text box properties. You do this by writing expressions that include parameters. When the report is processed, the expressions are evaluated using the current parameter settings. In this way, you can use report parameters to affect any aspect of a report that allows you to specify an expression.
You can set report parameter properties that determine whether parameters are single-valued or multivalued, are visible at run time, use a static or query-based valid values list, have default values, and accept null or blank values. Multivalued parameters provide support for selecting more than one value.
You can create cascading parameters that define the valid values list for other parameters in the report (for example, choosing a particular product category determines the list of available products).
You can use parameters to connect a parent report to a drillthrough report or to an embedded subreport.
After a report is published, you can manage parameter properties in the published report separately from the original parameter definition. Depending on how you configure the parameter in the report at design time, report server administrators can modify many parameter properties in the published report. For more information, see Setting Parameter Properties for a Published Report.
Parameters can be set in subscriptions. Each user who subscribes to a report can specify parameter values that are stored with and used by the subscription. For more information, see Setting Parameters in a Subscription.
Parameters can be set via URL access. For more information, see Using URL Access Parameters.

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 Securing Reports and Resources.

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 Securing Reports and Resources.

In This Section

Using Parameters to Control Report Data
Discusses how to use parameters to control the data in a report.

Using Parameters to Control Report Appearance
Discusses how to use parameters to control report appearance, including visibility, sort order, filtering, color, and text box properties.

Using Parameters to Connect to Other Reports
Discusses how to use parameters to link to drillthrough reports, subreports, and linked reports.

Using Single-Valued and Multivalued Parameters
Discusses how to use single valued and multivalued parameters.

Creating Report Parameters and Setting Report Parameter Properties
Discusses how to create and set properties on a report parameter.

Using Parameters in Expressions
Discusses how to include parameters in an expression, including syntax for single-valued and multivalued parameters.

Working with Parameters in Reporting Services

In This Section

See Also

Tasks

Concepts

Other Resources

Help and Information

Additional resources