Using Report and Group Variables Collection References in Expressions (Reporting Services)

When you have a complex calculation that is used more than once in expressions in a report, you might want to create a variable. You can create a report variable or a group variable. A report variable is set once and can be used in expressions throughout a report. A group variable is set once per unique group value and can be used in expressions at the current level or below in the group hierarchy.

Use a report variable to hold a value for time-dependent calculations, such as currency rates or time stamps, or for a complex calculation that is evaluated once. Because expressions in text boxes are evaluated on-demand as a user pages through a report, dynamic values (for example, an expression that includes Now(), a function that returns the time of day) can return different values if you view a page, view the next page, and then return to the first page using the Back button. By setting a the value of a report variable to the expression =Now(), and then adding the variable to your expression, you ensure the same value is used throughout report processing. A report variable can be referenced in any expression in a report.

To add a report variable, open the ReportProperties dialog box, click Variables, and provide a name and a value. The value for a report variable is set once and then remains unchanged during report processing.

To refer to the variable in an expression, use the global collection syntax, for example, =Variables!CustomTimeStamp.Value. On the design surface, the value appears in a text box as <<Expr>>.

Use a group variable to calculate a value in the scope of a group. A group variable is valid only in the scope of the group and its child groups.

For example, suppose a data region displays inventory data for items that are in different tax categories and you want to apply different tax rates for each category. You would group the data on Category and define a Tax variable on the parent group. Then you would define a group variable for ItemTax for each tax category and assign each of the different Category subgroups to the correct group variable. For example:

  • For the parent group based on [Category], define the variable Tax with a value [Tax]. Assume the category values are Food and Clothing.

  • For the child group based on [Subcategory], define the variable ItemsTax as =Variables!Tax.Value * Sum(Fields!Price.Value). Assume the subcategory values for the category Food are Beverages and Bread. Assume the subcategory values for Clothing are Shirts and Hats.

  • For a text box in a row in the child group, add the expression =Variables!ItemsTax.Value.

    The text box displays the total tax for Beverages and Bread using the Food tax and for Shirts and Hats using the Clothing tax.

To add a group variable, open the Tablix Group Properties dialog box, click Variables, and provide a name and a value. The group variable is calculated once per unique group value.

To refer to the variable in an expression, use the global collection syntax, for example, =Variables!GroupDescription.Value. On the design surface, the value appears in a text box as <<Expr>>.

You can also use a group variable in combination with custom code to customize an aggregate calculation. For more information, see Using Group Variables in Reporting Services 2008 for Custom Aggregation and Calculating Totals and Other Aggregates (Reporting Services).

Updated content

Added link to example for adding custom aggregation by using group variables and custom code.

Community Additions

ADD
Show: