Export (0) Print
Expand All

Aggregate Functions

SQL Server 2000

Aggregate Functions

You can use functions within expressions to provide aggregate data. For example, you can calculate a sum of all values in a particular field by using the Sum function. You can use aggregate functions in expressions for any report item.

Standard Aggregates

The following table describes the standard aggregate functions that are supported by Reporting Services.

Function Description
Avg Returns the average of all non-null values from the specified expression.
Count Returns a count of the values from the specified expression.
CountDistinct Returns a count of all distinct values from the specified expression.
CountRows Returns a count of rows within the specified scope.
First Returns the first value from the specified expression.
Last Returns the last value from the specified expression.
Max Returns the maximum value from all non-null values of the specified expression.
Min Returns the minimum value from all non-null values of the specified expression.
StDev Returns the standard deviation of all non-null values of the specified expression.
StDevP Returns the population standard deviation of all non-null values of the specified expression.
Sum Returns a sum of the values of the specified expression.
Var Returns the variance of all non-null values of the specified expression.
VarP Returns the population variance of all non-null values of the specified expression.

Running Aggregates

The following table describes the running aggregate functions that are supported by Reporting Services.

Function Description
RowNumber Returns a running count of all rows in the specified scope.
RunningValue Uses a specified function to return a running aggregate of the specified expression.

Custom Aggregates

The following table describes the custom aggregate functions that are supported by Reporting Services.

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

Scope

Each aggregate function uses the Scope parameter, which defines the scope in which the aggregate function is performed. A valid scope is the name of a grouping, dataset, or data region. Only groupings or data regions that directly or indirectly contain the expression can be used as a scope. For expressions within data regions, Scope is optional for all aggregate functions. If you omit the Scope parameter, the scope of the aggregate is the innermost data region or grouping to which the report item belongs. Specifying a scope of Nothing sets the scope to the outermost data region to which the report item belongs.

For expressions outside of data regions, Scope refers to a dataset. If a report contains more than one dataset, Scope is required. If a report contains only one dataset and Scope is omitted, the scope is set to the dataset. You cannot specify the Nothing keyword for report items outside of a data region.

You cannot use the Scope parameter in page headers or footers.

See Also

Using Expressions

Show:
© 2014 Microsoft