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:
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. |
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.
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.
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 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. |
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 (Report Builder and SSRS).
| Function | Description |
|---|---|
| Level | Returns the current level of depth in a recursive hierarchy. |
See Also
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)