Adding Parameters to Your Report (Report Builder 2.0)
In Report Builder 2.0, parameters are used to specify the data to use in a report, connect related reports together, and vary report presentation. To design a report that uses parameters effectively, you must understand how parameters and dataset queries work together, how parameters and expressions work together, how parameters can be managed on the report server for a published report, and what questions a report is designed to answer. What you choose to parameterize can influence the report design and layout.
Query parameters are added to a dataset query by way of the query designers or the Dataset Properties dialog box. After you create a query with parameters, Reporting Services automatically links query parameters to report parameters with the same name. New report parameters are added and modified by way of the Report Data pane.
Using Parameters in Reporting Services
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 corresponding report parameters that are indirectly linked to the query parameters. You can change the report parameter properties in the report design tools to include a valid values list and a user prompt that displays at run time.
You can also create cascading parameters, which retrieve hierarchical data from a data source. For example, the first parameter might filter on product category, and the second parameter might filter on product. Cascading parameters are used to organize and limit the number of available values for the user.
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 in the report designer tools. Report parameters can also be used to change the appearance or organization of a report after the data has been retrieved.
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.
After a report parameter is created, you can modify the default values and other parameter properties, such as visibility. For example, report parameters can use built-in variables like UserID, so you might want to create a report with a hidden parameter that selects data specific to the user running the report. Parameters can contain single values or multiple values, use a static or query-based valid values list, and accept null or blank values. Multivalue parameters enable users to select more than one value at run time.
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 in the published report. They can also create sets of parameters linked to the same report definition that customize the report for multiple audiences.
You can also run reports and specify parameter values using URL access. For more information, see "Using URL Access Parameters" in the Reporting Services documentation in SQL Server Books Online.
.gif "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 "Securing Reports and Resources" in the Reporting Services documentation in SQL Server Books Online. |
In This Section
Using Parameters to Control Report Data (Report Builder 2.0)
Discusses how to use parameters to filter report data, to change the sort order for detail and group data, and to modify queries to work with specific data providers.Using Query Parameters with Specific Data Sources (Report Builder 2.0)
Describes the query syntax expected by the various data sources.Using Parameters to Control Report Appearance (Report Builder 2.0)
Discusses how to use parameters to change report appearance using expression-based properties, including conditionally hiding report items and conditionally changing text color based on the values of a text box.Using Parameters to Connect to Other Reports (Report Builder 2.0)
Discusses how to use parameters to link to drillthrough reports, subreports, and linked reports.Using Single-Value and Multivalue Parameters (Report Builder 2.0)
Discusses how to use single-value and multivalue parameters.Creating Report Parameters and Setting Report Parameter Properties (Report Builder 2.0)
Discusses how to create and set properties on a report parameter when you author the report, including data type, default values, parameter prompts, available values, multiple values, and whether parameters are internal, hidden, or visible to the user at run time.
See Also