Aggregate Functions Reference (Report Builder and SSRS)

 

Applies To: SQL Server 2016

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:

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).

System_CAPS_ICON_note.jpg Note


You can create and modify paginated report definition (.rdl) files 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.

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

FunctionDescription
AvgReturns the average of all non-null numeric values specified by the expression, evaluated in the given scope.
CountReturns a count of non-null values specified by the expression, evaluated in the context of the given scope.
CountDistinctReturns a count of all distinct non-null values specified by the expression, evaluated in the context of the given scope.
MaxReturns 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.
MinReturns 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.
StDevReturns the standard deviation of all non-null numeric values specified by the expression, evaluated in the given scope.
StDevPReturns the population standard deviation of all non-null numeric values specified by the expression, evaluated in the context of the given scope.
SumReturns the sum of all the non-null numeric values specified by the expression, evaluated in the given scope.
UnionReturns 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.
VarReturns the variance of all non-null numeric values specified by the expression, evaluated in the given scope.
VarPReturns 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 linkBack to Top

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

Location in ReportFieldsParametersReportItemsPageNumber

TotalPages
DataSource

DataSet
VariablesRenderFormat
Page Header

Page Footer
YesYesAt most one

Note 1
YesYesYesYes
BodyYes

Note 2
YesOnly items in the currnet scope or a containing scope

Note 3
NoYesYesYes
Report ParameterNoOnly parameters earlier in the list

Note 4
NoNoNoNoNo
FieldYesYesNoNoNoNoNo
Query ParameterNoYesNoNoNoNoNo
Group ExpressionYesYesNoNoYesNoNo
Sort ExpressionYesYesNoNoYesYes

Note 5
No
Filter ExpressionYesYesNoNoYesYes

Note 6
No
CodeNoYes

Note 7
NoNoNoNoNo
Report.LanguageNoYesNoNoNoNoNo
VariablesYesYesNoNoYesCurrent or containing scopeNo
AggregatesYesYesOnly in page header/page footerOnly in report item aggregatesYesNoNo
Lookup functionsYesYesYesNoYesNoNo
  • 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 linkBack to Top

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

ContextRunningValueRowNumberFirst

Last
PreviousSum and Other Presort functionsReportItem aggregatesLookup functionsAggregate Function
Running ValueNoNoNoNoYesNoYesNo
First

Last
NoNoNoNoYesNoNoNo
PreviousYesYesYesNoYesNoYesNo
Sum and other Presort functionsNoNoNoNoYesNoYesNo
ReportItem aggregatesNoNoNoNoNoNoNoNo
Lookup functionsYesYes

Note 1
Yes

Note 1
Yes

Note 1
Yes

Note 1
Yes

Note 1
NoNo
Aggregate FunctionNoNoNoNoNoNoNoNo
  • 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 linkBack to Top

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.

FunctionDescription
RowNumberReturns a running count of the number of rows for the specified scope. The RowNumber function restarts counting at 1, not 0.
RunningValueReturns 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 linkBack to Top

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.

FunctionDescription
CountRowsReturns the number of rows in the specified scope, including rows with null values.

Arrow icon used with Back to Top linkBack to Top

The following lookup functions retrieve values from a specified dataset.

FunctionDescription
Lookup FunctionReturns a value from a dataset for a specified expression.
LookupSet FunctionReturns a set of values from a dataset for a specified expression.
Multilookup FunctionReturns 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 linkBack to Top

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.

FunctionDescription
FirstReturns the first value in the given scope of the specified expression.
LastReturns the last value in the given scope of the specified expression.
PreviousReturns 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 linkBack to Top

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.

FunctionDescription
AggregateReturns a custom aggregate of the specified expression, as defined by the data provider.

Arrow icon used with Back to Top linkBack to Top

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

FunctionDescription
InScopeIndicates whether the current instance of an item is within the specified scope.

Arrow icon used with Back to Top linkBack to Top

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).

FunctionDescription
LevelReturns the current level of depth in a recursive hierarchy.

Arrow icon used with Back to Top linkBack to Top

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

Community Additions

ADD
Show: