Share via


Built-in Functions for Reports (Visual Studio Report Designer)

ReportViewer provides built-in functions that you can use in report expressions. Built-in functions can be used in expressions that you include in client report definition (.rdlc) files and in report definition (.rdl) files that are processed on a SQL Server 2005 Reporting Services report server. Support for the functions is provided by the ReportViewer controls and Reporting Services.

Built-in functions are organized into two categories: aggregates and miscellaneous functions. Examples of aggregating data include calculating a sum of all values in a particular field by using the Sum function. You can use aggregate functions in expressions for any report item.

In addition to built-in functions, the expressions that you include in reports can use any Visual Basic function. For more information about Visual Basic functions, see Functions (Visual Basic).

Miscellaneous Functions

The following table describes general purpose functions that you can use.

Function Description

InScope Function (Visual Studio Report Designer)

Indicates whether the current instance of an item is within the specified scope.

Level Function ((Visual Studio Report Designer)

Returns the current level of depth in a recursive hierarchy.

Previous Function (Visual Studio Report Designer)

Returns the previous instance from the specified scope.

Standard Aggregates

The following table describes the standard aggregate functions that you can use.

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 Aggregates

The following table describes the running aggregate functions that you can use.

Function Description

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.

Custom Aggregates

The following table describes the custom aggregate function.

Function Description

Aggregate

Returns a custom aggregate of the specified expression, as defined by the data provider.

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 data table or Business object. 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.

See Also

Concepts

Creating Data Sources for a Report
Using Expressions in a Report (Visual Studio Report Designer)
ReportViewer Controls (Visual Studio)
Report Overview (Visual Studio)