Creating Report Parameters and Setting Report Parameter Properties
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, Change, or Delete a Report Parameter (Reporting Services).
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
Report parameters are automatically created for query parameters when a dataset query is saved, or you can create a report parameter manually. After you create a report parameter, you may need to adjust the default values for report parameter properties to get the functionality you want. For example, you may need to change the data type, create a dataset to provide an available values list, create a dataset to for default values, or set whether the values can be blank or null.
If you want a parameter to allow multiple values, you must change the dataset query to also allow multiple values. For more information, see Using Single-Value and Multivalue Parameters. In addition, if you include a multivalue parameter in an expression, you must understand how to write the expression to access the values you want. For more information, see Using Parameters Collection References in Expressions (Reporting Services).
To create or modify a report parameter, right-click the Parameters node in the Data pane and open the Report Parameter Properties dialog box.
Note
When you remove or change the name of a query parameter, the corresponding report parameter is not automatically removed or changed. If you change the name of a query parameter, when you save the query, a new report parameter corresponding to the changed name is created. You can rename the report parameter to match the new query parameter name, and update the dataset parameter properties to link the query parameter to the report parameter. If you remove a query parameter and do not need the corresponding report parameter, you must delete it manually.
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, see 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, select 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, it is not exposed in any way except in the report definition. An internal parameter must have a default value, which can be null if the Allow null value option has been selected.
Single and Multivalue Parameters
You can define a multivalue 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 or use a query to retrieve available values from a data source. A multivalue 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 Reporting Services, 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, in the query designer, choose the MDX All member list instead.
Allow Blank
When you create a list of available values, if you want <Blank> to be allowed as 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 multivalue parameter. When you select Allow Null, you are allowing the parameter to have a null value. By default, some 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 multivalue parameter and the query returns more than one row, all rows are used for the default.
See Also