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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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)