Report Functions
Microsoft Reporting Services provides specific built-in aggregate functions that you can use 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. |