Using Report Functions in Expressions (Reporting Services)
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.
| Function | Description |
|---|---|
Returns a custom aggregate of the specified expression, as defined by the data provider. |
|
Returns the average of all non-null values from the specified expression. |
|
Returns a count of the non-null values from the specified expression. |
|
Returns a count of all non-null distinct values from the specified expression. |
|
Returns a count of rows within the specified scope. |
|
Returns the first value from the specified expression. |
|
Returns the last value from the specified expression. |
|
Returns the maximum value from all non-null values of the specified expression. |
|
Returns the minimum value from all non-null values of the specified expression. |
|
Returns a running count of all rows in the specified scope. |
|
Uses a specified function to return a running aggregate of the specified expression. |
|
Returns the standard deviation of all non-null values of the specified expression. |
|
Returns the population standard deviation of all non-null values of the specified expression. |
|
Returns a sum of the values of the specified expression. |
|
Returns the variance of all non-null values of the specified expression. |
|
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 |
|---|---|
Indicates whether the current instance of an item is within the specified scope. |
|
Returns the current level of depth in a recursive hierarchy. |
|
Returns the previous instance from the specified scope. |
See Also
Concepts
Creating Expressions in Reporting Services
Other Resources
Using Expressions in Reporting Services