Using Parameters to Control Report Appearance

New: 17 July 2006

You can use parameters to control the visibility of items on a report. To do this, you set properties on text boxes, including font, color, decoration, and style, by using parameters in expressions. When the report is processed, the expression is evaluated with the current parameter settings and text box properties.

Controlling Visibility

You can show and hide items in a report by conditionally setting the Visibility properties for the report item by using an expression that includes a parameter. You can set the visibility for column headers, table rows, or group headers—anywhere the Visibility property on a text box or report item property page accepts an expression. The Visibility node includes the properties Hidden and ToggleItem. You can also control the initial state of drilldown items.

Changes you make to parameters are not reflected in the report until you reprocess it.

Examples showing how to control row and column visibility can be found in Tutorial: Adding Parameters to a Basic Tabular Report. Examples showing how to control the initial drill-down state of a toggle item can be found in Tutorial: Advanced Features Using Parameters.

Controlling Formatting

Text boxes have font and style properties that apply to all text within a single text box. You can set many of these properties using expressions. For example, you could author a sales report showing sales profits, and define a parameter that allows a user to choose a threshold value. Values above the threshold appear using a red font and values below the threshold appear using a black font.

To make the style conditional, you use an expression that refers to the threshold parameter instead of a static value for the font property of the text box that shows the sales profit. In the Properties window for the text box, enter the following expression in the Color property:

=IIF(Fields!Profit.Value < Parameters!Threshold.Value, "Red", "Black")

Controlling Grouping, Sorting, Filtering

You can change the way a report looks by allowing a user to select how they want data regions filtered, sorted, or grouped. All of these features can be set using expressions that refer to report parameters.

Examples showing how to change sorting and filtering based on a parameter can be found in Tutorial: Adding Parameters to a Basic Tabular Report. Examples showing how to change grouping can be found in Tutorial: Advanced Features Using Parameters.

See Also

Reference

Expression Examples in Reporting Services

Concepts

Using Parameters in Expressions
Hiding Report Items

Other Resources

Tutorial: Adding Parameters to a Basic Tabular Report
Tutorial: Advanced Features Using Parameters

Help and Information

Getting SQL Server 2005 Assistance