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

Article
07/19/2010

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.

Note

You cannot aggregate values across both a row group and a column group in a single expression. Writing an expression that includes an aggregate with a row group scope and another aggregate with a column group scope is not supported.

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.

Function	Description
Avg	Returns the average of all non-null numeric values specified by the expression, evaluated in the given scope.
Count	Returns a count of non-null values specified by the expression, evaluated in the context of the given scope.
CountDistinct	Returns a count of all distinct non-null values specified by the expression, evaluated in the context of the given scope.
Max	Returns the maximum value of all non-null numeric values specified by the expression, in the context of the given scope. You can use this for specifying a chart axis maximum value to control the scale.
Min	Returns the minimum value of all non-null numeric values specified by the expression, in the context of the given scope. You can use this for specifying a chart axis minimum value to control the scale.
StDev	Returns the standard deviation of all non-null numeric values specified by the expression, evaluated in the given scope.
StDevP	Returns the population standard deviation of all non-null numeric values specified by the expression, evaluated in the context of the given scope.
Sum	Returns the sum of all the non-null numeric values specified by the expression, evaluated in the given scope.
Union	Returns the union of all the non-null spatial data values of type SqlGeometry or SqlGeography that are specified by the expression, evaluated in the given scope.
Var	Returns the variance of all non-null numeric values specified by the expression, evaluated in the given scope.
VarP	Returns the population variance of all non-null numeric values specified by the expression, evaluated in the context of the given scope.

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.

Function	Description
RowNumber	Returns a running count of the number of rows for the specified scope. The RowNumber function restarts counting at 1, not 0.
RunningValue	Returns a running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope.

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.

Function	Description
CountRows	Returns the number of rows in the specified scope, including rows with null values.

Looking up values from another dataset

The following lookup functions retrieve values from a specified dataset.

Function	Description
Lookup	Returns a value from a dataset for a specified expression.
LookupSet	Returns a set of values from a dataset for a specified expression.
Multilookup Function	Returns the set of first-match values for a set of names from a dataset that contains name/value pairs.

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.

Function	Description
First	Returns the first value in the given scope of the specified expression.
Last	Returns the last value in the given scope of the specified expression.
Previous	Returns the value or the specified aggregate value for the previous instance of an item within the specified scope.

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.

Function	Description
Aggregate	Returns a custom aggregate of the specified expression, as defined by the data provider.

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.

Function	Description
InScope	Indicates whether the current instance of an item is within the specified 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).

Function	Description
Level	Returns the current level of depth in a recursive hierarchy.

Share via