Setting Parameter Properties for a Published Report
A parameterized report is a report that accepts input values used in report processing. Parameters are defined in Report Designer when you create the report. For more information about defining parameters, see Working with Parameters in Reporting Services.
After a report is published, you can modify parameter properties using Report Manager or SQL Server Management Studio to do the following:
- Run a report with preset parameter values that a user never sees.
- Run a report using a default parameter value or a valid value select by the user.
- Run a report with no parameter value specified. You can specify a null value to return an unfiltered result set.
- Run a personalized report that gets user-specific information based on a value that a user provides (for example, a personal identification number to obtain a balance on an account or payroll stub).
Parameter properties that you set in Report Manager or Management Studio for a published report are generally preserved if you republish the report definition from Report Designer. 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 in Report Manager. Default parameter values that are designated as "Query-based" are defined in the report definition and cannot be modified in Report Manager. 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. For more information about query parameters, see Parameterized Reports.
Security Concerns for Parameterized Reports
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. For more information, see Using Global Collections in Expressions (Reporting Services).
|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.|
Setting Parameter Properties
The parameter properties that you can modify on a published report are determined by the parameter that is specified in the original report definition. You can always change the display text and the Hide property, which determines whether the parameter is visible to users. However, you cannot always change the default value if the value is derived from a query. In this case, the text string "QueryBased" appears next to the parameter.
To modify parameter properties, open the Parameters Properties page of the report. You can modify the following properties:
- The Has Default property determines whether the report can be processed without input from the user. If a parameter does not have a default value specified, a user must provide one before the report can be processed.
- The Default Value property can be a constant or null (if the parameter accepts null values). It cannot be set to an expression.
- The Hide property determines whether the display and input fields are visible to users. Select this check box to hide the parameter.
- The Prompt User property determines whether the user can type or select a different valid value for the parameter. This property is enabled when Hide is disabled.
- The Display Text property specifies text that appears next to the parameter. This text can be a label or a longer text string that provides usage instructions. This property is enabled when you select Prompt User.
You cannot delete, rename, re-order, or change the data type of parameters in a published report. In addition, you cannot change the parameter name. To change either of these properties, you must modify the report definition.
You can hide the parameter fields and labels that are used with a report. When all the parameters are hidden, the parameter area for an on-demand report is hidden. If you hide a parameter, you must provide a default value either in the report definition or on the Parameter Properties page. Depending on the report, you can specify a null value if nulls are permitted.
Hiding a parameter does not make it invisible in all situations. Parameters that you hide on the report toolbar can still be set in subscriptions and through URLs. To hide a parameter everywhere but the report definition, select Internal from the Report Parameters dialog box.
To hide a parameter:
- Select Has Default.
- Specify Default Value.
- Select Hide.
Validating Parameter Values
To verify whether a value you specified is valid, run the report. Errors that are related to parameter processing appear in the Report Manager Error page. To investigate the problem further, you can view the logs files. For more information, see Reporting Services Log Files.
Setting Parameters in Subscriptions
In addition to using the Parameters Properties page, you can also set parameter values when you create a subscription. 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.
17 July 2006