Calculating Totals and Other Aggregates (Report Builder 2.0)
In a tablix data region, you can display aggregate totals for a report dataset, a data region, or a group. You can use the default aggregation supplied by the Add Total command and use the default scope. Alternatively, you can specify a different aggregate function from the built-in functions or specify a different scope.
Understanding the Add Totals Command
To display tablix data region grand totals and group subtotals, use the shortcut Add Total command. The Add Total command is context-sensitive. For a cell in the row group area, the Add Total command automatically adds a row outside the group for totals. For a cell in the column group area, the Add Total command automatically adds a column outside the group for totals. For a cell in the body area that contains a numeric field expression, the Add Total command provides a choice whether to add a total to a row or column. For more information about how to use the Add Total command, see How to: Add a Total to a Group or Tablix Data Region (Report Builder 2.0).
By default, the Add Total command uses the Sum function. However, you can change the Sum function to a different built-in function and perform a different aggregation for a scope. You can also write your own expressions to calculate aggregate values for one scope relative to another scope. For example, you might want to calculate the percentage of a group value relative to the complete dataset. For more information about how to use other built-in functions to perform aggregations, see Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0). For the list of built-in functions, see Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0).
Understanding Default Scope
As a tablix data region is processed and rendered, Report Builder 2.0 evaluates the value of each tablix cell in the context of the cell's scope. When a cell is in a row that is associated with a row group or column group, a cell can belong to multiple groups. The set of groups to which a cell belongs, the data region, or the dataset for which an expression is evaluated is called scope. The default scope is defined by the innermost row and column group to which the cell belongs. Visual cues help you identify the groups for which a selected cell is a member. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns (Report Builder 2.0).
For an example of a default scope, the following figure has a parent row group that is based on date, a child row group that is based on order, and a details group.
.gif "Design view: Grand total in basic table")
This figure shows a field expression for Qty and LineTotal in tablix body cells in multiple rows. The following list describes the default scope for these field expressions in each row:
Row 1. This row displays column labels that are static text. The default scope is the data region. The row repeats only once and is sometimes called the header row.
Row 2. This row displays detail data, indicated by the row handle for details (
.gif "Row handle with 3 parallel lines for details row")
). The default scope for [Qty] is the details group, which is the data in the dataset bound to this data region after all dataset, data region, and group filters are applied. This row repeats once per row in the dataset query result set. The value [Qty] displays the detail data from the dataset.Row 3. This row displays aggregate values. The default scope for [Sum(Qty)] is the child row group Order. The row repeats once per unique order number value. The value [Sum(Qty)] displays the aggregate sum for each order number.
Row 4. This row displays aggregate values. The default scope for [Sum(Qty)] is the parent row group Date. The row repeats once per unique date value. The value [Sum(Qty)] displays the aggregate sum for each date.
Row 5. This row displays aggregate values. The default scope for [Sum(Qty)] is the data region. The row repeats only once and is sometimes called the footer row. The value [Sum(Qty)] displays the aggregate sum for the data region.
For more information about context and scope, see Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0). For more information about interpreting visual cues on tablix data regions, see Understanding Tablix Data Region Cells, Rows, and Columns (Report Builder 2.0).
Specifying Aggregate Calculations with Named Scopes
To specify a scope other than the default scope, specify a named scope. A named scope includes the name of the dataset, the data region, or the group. Because expressions are context-sensitive, the named scope that you specify must be valid for the build-in function. Each built-in function specifies which scopes are valid as a parameter. For more information, see the specific function reference topic in Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0).
For an example of how to change the default scope to a named scope, suppose a report has the following data structures:
A report dataset that is named Sales.
A table data region that is named Tablix1 and that has the following groups:
A parent row group that is named Category with a child group that is named Subcat.
A parent column group that is named Geography with a child group that is named CountryRegion.
To this report, you use the Add Total command to add totals to various cells. This resulting report could resemble the following figure.
.gif "Matrix, nested row and column groups with totals")
In this figure, one of the cells has been selected. For this selected cell, the orange group indicator brackets show the innermost group memberships: the Category group and the CountryRegion group. These innermost group memberships define the default scope for this cell. (For the selected cell, there are also two gray group brackets that show groups: the Subcat row group and the Geography column group. Groups that are marked with a gray group bracket are either outer groups for the selected inner group or are not in the scope for the selected cell.)
This selected cell has the expression: [Sum(LineTotal)]. Based on the cell's scope and expression, the rendered report will display the value for the text box as the [LineTotal] sum for each specific Subcat value within each specific CountryRegion value.
Without replacing the Sum function in the expression, you could change the scope for this cell in the following ways:
Change the scope to be the Sales dataset. For example, to specify the dataset total, you would change the expression to =Sum(Fields!LineTotal.Value, "Sales").
Change the scope to be the whole Tablix1 data region. For example, to calculate the percent that a group contributes to the total data region, you would change the expression to =Sum(Fields!LineTotal.Value)/Sum((Fields! LineTotal.Value),"Tablix1").
In this new expression, the numerator, Sum(Fields! LineTotal.Value), is the sum of the line totals for each category. The denominator, Sum((Fields! LineTotal.Value), "Tablix1"), is the sum of the line totals for the data region. The result is the percentage that each category represents of the total data region.
In the selected cell, you can specify one of the following group scopes for the Sum function: CountryRegion, Geography, or Category. You cannot specify the group scope Subcat, because the cell is not in a row associated with the Subcat group.
Note
You can only specify a single scope for an aggregate function. You cannot specify both "Category" and "Geography" at the same time as scopes for the denominator expression. Specifying multiple scopes is not supported.
For the list of built-in functions, see Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0).
Examples of Specifying Different Scopes
The following table provides examples of expressions that use the default scope, the data region scope, and the dataset scope. The examples assume that there is a tablix data region named "Tablix1", a dataset named "Sales" with a field named "LineTotal" that contains the total for a line item in a sales order.
Description |
Expression |
|---|---|
Sum of line item totals for the current scope. |
=Sum(Field!LineTotal.Value) |
Percent of (the sum of line item totals for the current scope)/(the sum of line totals for data region). This value is formatted using the default display format for the text box. |
=Sum(Field!LineTotal.Value)/Sum(Field!LineTotal.Value,"Tablix1") |
When placed in a row associated with the group CountryRegion: Percent of (the sum of line item totals for the CountryRegion group)/(the sum of line totals for the Geography group). This value is formatted using the default display format for the text box, which must be set to P1 for the form #.#%. |
=Sum(Field!LineTotal.Value)/Sum(Field!LineTotal.Value,"Geography") |
Percent of (the sum of line item totals for the current scope)/(the sum of line totals for the data region). This value is formatted using IIF so that the result is blank if there are no line item totals in the current group. |
=IIF(Sum(Field!LineTotal.Value)<=0,"", FormatPercent(Sum(Field!LineTotal.Value)/Sum(Field!LineTotal.Value,"Tablix1"),1) |
Percent of (the sum of line item totals for the current scope)/(the sum of line item totals for the dataset). This value is formatted using FormatPercent specifying one decimal place. |
="Percentage contributing to all sales: " & FormatPercent(Sum(Field!LineTotal.Value)/Sum(Field!LineTotal.Value,"Sales"),1) |
Suppressing Null or Zero Values in a Cell
For many reports, calculations that are scoped to groups can create many cells that have zero (0) or null values. To reduce clutter in your report, add an expression to return blanks if the aggregate value is 0. For more information, see "Examples that Suppress Null or Zero Values" in Expression Examples (Report Builder 2.0).