Using Built-in Report and Aggregate Functions in Expressions (Reporting Services)

Reporting Services provides built-in functions for use in expressions to calculate aggregate data in datasets, data regions, and groups, and to retrieve other data values, such as the first or last value on a report page. The default aggregate function for numeric fields is SUM. In the Expression dialog box, select the category Aggregate in Common Functions to choose a different function.

As the report data and report layout is combined at run-time, the report processor determines the default scope for each function reference in an expression. The default scope can be a dataset, a data region, a Tablix group (parent, child, adjacent, or recursive), the intersection of a Tablix row and column group, or a chart group (series, category, value). For most functions, you can also specify a scope in your function call that is different than the default scope. To determine the valid scopes for a function, see the individual function reference topic. For examples, see Calculating Totals and Other Aggregates (Reporting Services).

The following table lists categories of built-in functions that are supported by Reporting Services:

• Calculating Aggregates

• Calculating Running Values

• Retrieving Row Counts

• Looking up values from another dataset

• Retrieving Sort-Dependent Values

• Retrieving Server Aggregates

• Retrieving Recursive Level

• Testing for Scope

Understanding Scope

In Reporting Services, the term scope can specify different concepts depending on the context. The following list describes the different concepts:

• Scope for report items in report processing. When the report data and the report layout are combined at run time, each report item is processed. A Tablix data region is processed from the outside into ever more restrictive sets of data as the Tablix row groups and column groups are processed. In this sense, a group is contained by a data region. A child group and its siblings are contained by its parent group. For example, a toggle item for a group must be a text box in the same group scope or in any containing group scope. For more information, see Understanding Containing Scopes later in this topic.

• Scope for aggregate functions. The report processor evaluates each aggregate expression in a named scope or the default scope, as described in the following list.

  • A named scope can be the name of a dataset, a data region, or a group.

  • The default scope depends on the report item property that the report processor is evaluating. For example, the default scope for a Tablix cell in a data region with row and column groups is the innermost row group and column group to which the cell belongs. The default scope for a cell in a table with no groups is the details group. On the design surface, a Tablix data region provides visual elements that you can use to help determine a cell's available scopes. Group bars appear above and beside the Tablix data region to show which rows or columns belong to a group. When a cell is selected, group indicators show the active, innermost groups to which the cell belongs. For more information, see Working with Tablix Data Regions.

    For a text box on the design surface, there is no default scope. You must specify the name of the dataset to use, for example, =First(Fields!Sales.Value,"Dataset1").

    When you call a built-in function and specify a named scope, check the function reference to determine which scopes are valid. For example, for Sum, you can specify the default scope or a containing scope.

Understanding Containing Scopes

The following scopes list the containing order from outermost (higher) to innermost (lower) and describe the data they represent:

• Report dataset   Specifies the report dataset linked to the data region or to a report item in the report body. The data used for aggregation is from the report dataset after dataset filter expressions are applied.

• Data region   Specifies data from the data region after data region filter and sort expressions are applied. Group filters are not used when calculating aggregates for data regions.

• Row and column groups   Specifies the data after the group expressions and group filters are applied for the parent group and child groups. For the purposes of identifying scope containment, every parent group contains its child groups.

• Nested data regions   Specifies the data for the nested data region in the context of the cell to which it has been added, and after the nested data region filter and sort expressions have been applied.

• Row and column groups for the nested data regions   Specifies the data after the nested data region's group expressions and group filters have been applied.

When a built-in function states that you must specify the current scope or a containing scope, you cannot specify a scope that is lower or at the same level in containment order than the current scope. For example, from a row in a row group that has a child group, you cannot specify the name of the child group as a scope, nor can you specify a sibling row group. You must use the default scope or specify a scope higher in the containment order.

Filter Expressions

Dataset and data region filter expressions cannot contain aggregate functions. Group filters are ignored when calculating aggregates for data regions.

Calculating Aggregates

The following built-in functions calculate summary values for a set of non-null numeric data in the default scope or the named scope. All data used for an aggregate calculation must be the same data type. To convert data that has multiple numeric data types to the same data type, use conversion functions like CInt, CDbl or CDec. For more information, see Type Conversion Functions.

To use these functions to calculate summaries for a recursive hierarchy group, you must specify the Recursive parameter.

Calculating Running Values

The following built-in functions calculate running values for a set of data. RowNumber is like RunningValue in that it returns the running value of a count that increments for each row within the containing scope. The scope parameter for these functions must specify a containing scope, which controls when the count restarts.

Retrieving Row Counts

The following built-in function calculates the number of rows in the given scope. Use this function to count all rows, including rows with null values.

Looking up values from another dataset

The following lookup functions retrieve values from a specified dataset.

Retrieving Sort-Dependent Values

The following built-in functions return the first, last, or previous value within a given scope. These functions depend on the sort order of the data values. Use these functions, for example, to find the first and last values on a page to create a dictionary-style page header. Use Previous to compare a value in one row to the previous row's value within a specific scope, for example, to find percentage year over year values in a table.

Retrieving Server Aggregates

The following built-in function retrieves custom aggregates from the data provider. For example, using an Analysis Services data source type, you can retrieve aggregates calculated on the data source server for use in a group header.

Testing for Scope

The following built-in function tests the current context of a report item to see if it is a member of a specific scope.

Retrieving Recursive Level

The following built-in function retrieves the current level when a recursive hierarchy is processed. Use the result of this function with the Padding property in a text box to control the indent level of a visual hierarchy for a recursive group. For more information, see Creating Recursive Hierarchy Groups (Reporting Services).

See Also

Concepts

Using Expressions (Reporting Services)

Calculating Totals and Other Aggregates (Reporting Services)

Other Resources

Working with Report Expressions