Report Functions

Microsoft Reporting Services provides specific built-in aggregate functions.

You can use these in expressions as an alternative to using the SunSystems Reporting Services Summary Fields dialog to define an aggregate. These include:

  • Standard aggregate functions like Sum, Min, Max, and Count
  • Running aggregate functions like RowNumber and RunningValue

Standard Aggregate Functions

These are the standard Microsoft Reporting Services aggregate functions:
Microsoft 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 Aggregate Functions

You can use any of these running aggregate functions in expressions:

  • RowNumber
  • RunningValue
  • Previous
  • InScope
  • Level
For running aggregate functions like RowNumber and RunningValue, use this syntax: 
=AggregateFunction(Fields!fieldname.Value[, Scope])
The scope parameter is optional and if you do not include this, the current scope is used.
Example 1: 
=Avg(Fields!LedgerLine_baseAmount_amount.Value,"LedgerLine_accountCode")
Example 2: 
=Avg(Fields!LedgerLine_baseAmount_amount.Value)
Running Aggregate Function Description
RowNumber(Scope) This returns the current row number of the specified scope.
RunningValue(Expression, Function, Scope) This returns a running aggregate of the expression determined by the function and scope specified.
Previous(Field Expression) The running value is reset for each new instance of the data region specified by the scope parameter.
InScope(Scope) This returns a Boolean.
Level(Scope) This returns the value of the group level of a recursive hierarchy. You can omit the scope.