Udostępnij za pośrednictwem


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

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.

See Also

Concepts

Creating Expressions in Reporting Services

Other Resources

Using Expressions in Reporting Services

Help and Information

Getting SQL Server 2005 Assistance