Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Using Parameters to Control Report Appearance (Report Builder 2.0)

SQL Server 2008

You can change the appearance of a report and the items in a report by changing the values of their properties. Because many properties in a report are expression-based, and because expressions can include references to parameters, you have great flexibility in providing parameters that let the user vary report appearance. For example, expression-based properties for a text box include font color, style, decoration, background color, border color, padding, and visibility. As each expression-based report item property is processed, the report processor first substitutes any parameter values into the expression, and then evaluates the expression.

Another reason to create parameters for expression-based properties is to provide a customized report for different users. You can write one report definition and use parameters to create a series of linked reports to change way the report appears to different audiences. You can also create a drilldown report with a parameter that controls whether the report opens with expanded or collapsed nodes. Users can choose their preferred way of viewing the report.

Other reasons why you might want to create report parameters that control report appearance include:

  • Setting thresholds for color-coding values in a data region. For example, displaying currency values below a certain adjustable threshold in red text.

  • Letting the user customize images used in a table.

  • Letting the user control the sort order in a data region. For more information, see Sorting Data in a Report (Report Builder 2.0).

  • Conditionally controlling the visibility of report items. For example, you may want to implement your own summary and detail views for a report by initially hiding some report items, providing a parameter so the user can choose to show all items in the report.

Use the Report Parameter Properties dialog box to create report parameters. Provide a list of valid values and a user prompt. Changes you make to parameters are not reflected in the report until you run it. Create default values if you want the report to run automatically.

You can show and hide items in a report by using an expression that includes a parameter in the visibility properties Hidden, InitialToggleState, and ToggleItem for the report item. Set these report item properties on the Visibility page of the appropriate Properties dialog box or in the Hidden property in the Properties pane for the selected report item on the design surface.

For example, you can set the visibility for text boxes, column headers, table rows, or group headers based on a Boolean parameter named Show by using the following expression:

=Not Parameters!Show.Value

When the parameter Show is true, the expression in the Hidden property evaluates to false at run time, so the report item is visible. For more information, see Hiding Report Items Conditionally (Report Builder 2.0).

For rules about preserving white space when a report item is initially hidden, see Rendering Report Items (Report Builder 2.0).

For more information about how to control row and column visibility, see Tutorial: Adding Parameters to Filter Report Data (Report Builder 2.0).

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 can author a sales report showing sales profits, and define a parameter that enables users to specify 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 font color conditional, use an expression for the Color property of the text box that shows the sales profit that refers to the threshold parameter instead of a static value. For this example, create a calculated field called Profit that contains the sum of the Sales field, and add a report parameter called Threshold that contains the Profit field. In the Properties pane for the text box, enter the following expression in the Color property:

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

You can provide a parameter to let the user choose an image in a report. For example, you can create a parameter named ImageName and provide a dataset of available values that provides the names of valid images. Add an image to the design surface and set the image properties as needed to specify where to find the image sources. Then set the Value property of the image report item to the following expression:

=Parameters!ImageName.Value

When the user selects a value for the parameter @ImageName and runs the report, the appropriate image appears for the image report item.

You can create a parameter so that a user can control the way data is grouped for a data region. For example, to let the user control the field that is used to group data in a table, create a parameter named GroupValue. You must provide a set of valid field names to group on for the available values for the parameter. Create a row group for the table and set the group expression to the following expression: =Fields(Parameters!GroupValue.Value).Value. When the report is processed, the group expression evaluates to the selected field.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.