Creating Report Parameters and Setting Report Parameter Properties

Article
12/03/2008

New: 17 July 2006

Report parameters are defined globally for the report. Report parameters are created automatically if the report query includes parameters, or they can be created manually. After a report parameter is created, you must set properties that identify it and that control how it is used in the report.

To work with parameters before a report is published, use the Report Parameters dialog box in Report Designer. For more information, see How to: Add, Edit, or Delete a Report Parameter (Report Designer).

After the report is published, you can modify many report parameter properties in Report Manager or SQL Server Management Studio. For more information about modifying parameter properties and security considerations, see Setting Parameter Properties for a Published Report.

Report Parameters

Although report parameters are automatically created for query parameters, report parameters are never automatically deleted when a query parameter is deleted.

To open the Report Properties dialog box, switch to Data or Layout view in Report Designer and, from the Report menu, select Report Properties. You will see a list of report parameters in the left pane. Select the parameter you want to modify.

Name and Data Type

Parameter names are required, and must be unique within the report. A parameter data type is set to String by default. You can choose a different data type from the drop-down list. If the report parameter is based on a query parameter, make sure that the report parameter data type matches the query parameter data type. Reporting Services does not detect query parameter data types so you must adjust the data type yourself.

Names must comply with the characters and casing standards of the Common Language Specification. For more information, search for "Common Language Specification" at https://msdn.microsoft.com.

Prompt

In a published report, parameters typically appear as text boxes in a parameter input area at the top of the report. The prompt string defines the label that identifies the parameter in the input area. The prompt can be the name of the parameter or directions to the user, for example, "Year" or "Select a year". If the prompt is left blank and a default parameter value is specified, the default value is used, and the input box for the parameter is not displayed when the user runs the report. If prompt is left blank, and no default parameter value is specified, the report cannot run. If you do not want to prompt the user for parameter values, mark a parameter as Hidden or Internal.

Hidden and Internal Parameters

You can set options that control parameter visibility in the published report. Setting the Hidden and Internal options provides different levels of visibility. You can hide the parameter on the parameter input area of the published report, yet set values for it on a report URL or in a subscription definition. If you set a parameter to internal-only, it is not exposed in any way except in the report definition.

Single and Multivalued Parameters

You can define a multivalued parameter by selecting the Multivalued option in the Report Parameters dialog box. If this check box is not selected, the parameter is single-valued. Specifying the Multivalued option adds check boxes to the available values in a parameter drop-down list in the published report. Users can select the values they want; for example, in the Sales Reason Comparisons sample report, users can select multiple products to view the combined sales data for those products.

When specifying the available values, you can provide a static list of strings or use a query to retrieve available values from a data source. A multivalued parameter must include at least one value. Null values are not allowed. The Select All option is created automatically and displayed only if there is more than one value.

Note

In SQL Server 2005, if you are using an Analysis Services data source, do not choose the Select All option. The Select All option can produce poor performance when processing an MDX query. If you want to select all of the values, choose the All member list instead.

Allow Blank

When you create a list of available values, if you want <Blank> to be allowed as a valid, you must include it in the list of valid values.

Allow Null

Allow Null is an option for single-valued parameters. You cannot select this value for a multivalued parameter. When you select Allow Null, you are allowing the parameter to have a null value. By default, many query languages do not return Null values in a result set.

Available Values

When you define a report parameter, you must define the values that can be used with the report. These are called available values or valid values. An available values list contains a set of value/label pairs. When the report is run, the user sees the label. When the user selects a label, the corresponding value is used as the parameter value. Available values can be predefined as a static list or expression, or they can be retrieved from a dataset:

A nonqueried list is a static list of value/label pairs. The entire list is contained within the report parameter definition. Each value and label can be a static value or can be generated from an expression.
A queried list causes the report server to retrieve a set of values and labels from a dataset when the report is run. When you specify a queried available values list, you select the dataset, the field to use for value, and the field to use for label.

When specifying a queried available values list, it is recommended that you create a simplified dataset to be used specifically by the parameter, rather than using a more complicated dataset that is also used by data regions within the report. Using the same dataset for both the valid values list and the data regions in the report might produce unexpected results in the valid values list.

Default Value

You can define a default value for the parameter. If all parameters in a report have default values, the report will process immediately when the report is opened by the user or previewed in Report Designer. If at least one parameter does not have a default value, then the report will only display data after the user enters all parameter values and runs the report.

You can use nonqueried or queried value as the default. A nonqueried default value is a static value or an expression. A queried default value is an expression that points to a field in a dataset. If the query returns multiple rows, the value from the first row of the returned dataset is used.

You can also choose to not specify a default value. If you do this, you must specify a prompt.

Note

If you are using a multivalued parameter and the query returns more than one row, all rows are used for the default.