Aggregate Functions Reference (Report Builder and SSRS)

To include aggregated values in your report, you can use built-in aggregate functions in expressions. The default aggregate function for numeric fields is SUM. You can edit the expression and use a different built-in aggregate function or specify a different scope. Scope identifies which set of data to use for the calculation.

As the report processor combines report data and the report layout, the expressions for each report item are evaluated. As you view each page of the report, you see the results for each expression in the rendered report items.

The following table lists categories of built-in functions that you can include in an expression:

  • Built-in Aggregate Functions

  • Restrictions on Built-in Fields, Collections, and Aggregate Functions

  • Restrictions on Nested 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

To determine the valid scopes for a function, see the individual function reference topic. For more information and for examples, see Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder and SSRS).

Note

You can create and modify report definitions (.rdl) in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com.

Built-in Aggregate Functions

The following built-in functions calculate summary values for a set of non-null numeric data in the default scope or the named scope.

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.

Arrow icon used with Back to Top link Back to Top

Restrictions on Built-in Fields, Collections, and Aggregate Functions

The following table summarizes restrictions in report locations on where you can add expressions that contain references to global built-in collections.

Location in Report

Fields

Parameters

ReportItems

PageNumber

TotalPages

DataSource

DataSet

Variables

RenderFormat

Page Header

Page Footer

Yes

Yes

At most one

Note 1

Yes

Yes

Yes

Yes

Body

Yes

Note 2

Yes

Only items in the currnet scope or a containing scope

Note 3

No

Yes

Yes

Yes

Report Parameter

No

Only parameters earlier in the list

Note 4

No

No

No

No

No

Field

Yes

Yes

No

No

No

No

No

Query Parameter

No

Yes

No

No

No

No

No

Group Expression

Yes

Yes

No

No

Yes

No

No

Sort Expression

Yes

Yes

No

No

Yes

Yes

Note 5

No

Filter Expression

Yes

Yes

No

No

Yes

Yes

Note 6

No

Code

No

Yes

Note 7

No

No

No

No

No

Report.Language

No

Yes

No

No

No

No

No

Variables

Yes

Yes

No

No

Yes

Current or containing scope

No

Aggregates

Yes

Yes

Only in page header/page footer

Only in report item aggregates

Yes

No

No

Lookup functions

Yes

Yes

Yes

No

Yes

No

No

  • Note 1. ReportItems must exist in the rendered report page, or their value is Null. If the visibility of a report item depends on an expression that evaluates to False, the report item does not exist on the page.

  • Note 2. If a field reference is used in a group scope, and the field reference is not included in the group expression, then the value for the field is undefined, unless there is only one value in the scope. To specify a value, use First or Last and the group scope.

  • Note 3. Expressions that include a reference to ReportItems can specify values for other ReportItems in the same group scope or in a containing group scope.

  • Note 4. Property values for earlier parameters might be null.

  • Note 5. In Member sorts only. Cannot use in data region sort expressions.

  • Note 6. In Member filters only. Cannot use in data region or dataset filter expressions.

  • Note 7. The Parameters collection is not initialized until after the Code block is processed, so methods cannot be used to control parameters on initialization.

  • Note 8. Data type for all aggregates except Count and CountDistinct must be the same data type, or null, for all values.

Arrow icon used with Back to Top link Back to Top

Restrictions on Nested Aggregates

The following table summarizes restrictions on which aggregates functions can specify other aggregate functions as nested aggregates.

Context

RunningValue

RowNumber

First

Last

Previous

Sum and Other Presort functions

ReportItem aggregates

Lookup functions

Aggregate Function

Running Value

No

No

No

No

Yes

No

Yes

No

First

Last

No

No

No

No

Yes

No

No

No

Previous

Yes

Yes

Yes

No

Yes

No

Yes

No

Sum and other Presort functions

No

No

No

No

Yes

No

Yes

No

ReportItem aggregates

No

No

No

No

No

No

No

No

Lookup functions

Yes

Yes

Note 1

Yes

Note 1

Yes

Note 1

Yes

Note 1

Yes

Note 1

No

No

Aggregate Function

No

No

No

No

No

No

No

No

  • Note 1. Aggregate functions are only allowed inside the Source expression of a Lookup function if the Lookup function is not contained in an aggregate. Aggregate functions are not allowed inside the Destination or Result expressions of a Lookup function.

Arrow icon used with Back to Top link Back to Top

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.

Arrow icon used with Back to Top link Back to Top

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.

Arrow icon used with Back to Top link Back to Top

Looking Up Values from Another Dataset

The following lookup functions retrieve values from a specified dataset.

Function

Description

Lookup Function

Returns a value from a dataset for a specified expression.

LookupSet Function

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.

Arrow icon used with Back to Top link Back to Top

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.

Arrow icon used with Back to Top link Back to Top

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.

Arrow icon used with Back to Top link Back to Top

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.

Arrow icon used with Back to Top link Back to Top

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 (Report Builder and SSRS).

Function

Description

Level

Returns the current level of depth in a recursive hierarchy.

Arrow icon used with Back to Top link Back to Top

See Also

Reference

Expression Examples (Report Builder and SSRS)

Concepts

Expression Uses in Reports (Report Builder and SSRS)

Expression Scope for Totals, Aggregates, and Built-in Collections (Report Builder and SSRS)