Report Functions

Using Report Functions in Expressions (Reporting Services)

SQL Server 2005

11 out of 38 rated this helpful - Rate this topic

Updated: 15 September 2007

You can use built-in functions within expressions to manipulate the data within report items, properties, and other areas in the report. Built-in functions are used to aggregate data in datasets, data regions, and groups, and return other data.

Aggregate Functions

---

The following table describes the aggregate functions that are supported by Reporting Services. You can use aggregate functions in expressions for any report item. All data used for an aggregate calculation must be the same data type. To convert data that has multiple numeric data types to the same data type, use conversion functions like CInt, CDbl or CDec. For more information, see Type Conversion Functions.

CInt Example

The following example uses the CInt function to convert a value to Integer.

VB

Dim aDbl As Double 
Dim anInt As Integer
aDbl = 2345.5678
' The following line of code sets anInt to 2346.
anInt = CInt(aDbl)

Function	Description
Aggregate	Returns a custom aggregate of the specified expression, as defined by the data provider.
Avg	Returns the average of all non-null values from the specified expression.
Count	Returns a count of the non-null values from the specified expression.
CountDistinct	Returns a count of all non-null 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.
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.
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.

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.

Other Functions

---

Reporting Services provides the following additional aggregate functions that you can use within expressions. For a complete list of the types of functions available when expressions are evaluated, see Creating Expressions in Reporting Services.

Function	Description
InScope	Indicates whether the current instance of an item is within the specified scope.
Level	Returns the current level of depth in a recursive hierarchy.
Previous	Returns the previous instance from the specified scope.