v2.1.0
v2.1.0v2.0.0v1.2.0

Aggregate Functions

To include summary values in a report, you can use aggregate functions described below. All of them have the optional scope argument that determines the set of data to use for the calculation. The scope argument could have one of the following values.

By default, the value of a scope argument is the current scope that depends on a report item that "hosts" the aggregate function. For example, in the Get Started with Tabular Reports demo, the table's group footer has the textbox that shows the {Sum(unitsInStock)} value. This is the call to the Sum aggregate function and the scope argument is omitted. It means that the calculation scope is the group instance. If a textbox with the same value resides in the table header instead, the calculation scope is the entire table.

AggregateIf

Returns a conditional aggregated value of values specified by the expression, calculated in the specified scope.

Syntax

AggregateIf(condition, aggregate, expression, [scope])

Arguments

  • condition - the expression that evaluates to a boolean value indicating whether to include the expression into the calculation
  • aggregate - the name of the aggregate function to use for calculation, for example, Count
  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the sum of the SalesAmount field values within the current scope. The result only includes SalesAmount values that are greater than 1000.

{AggregateIf(SalesAmount > 1000, "Sum", SalesAmount)}

Avg

Returns the average of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

Avg(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the average of SalesCount field values within the current scope.

{Avg(SalesCount)}

Count

Returns a count of non-null values specified by the expression, calculated in the specified scope.

Syntax

Count(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the number of UnitsOnOrder field values within the ProductsTable data region.

{Count(UnitsOnOrder, "ProductsTable")}

CountDistinct

Returns a number of distinct non-null values specified by the expression, calculated in the specified scope.

Syntax

CountDistinct(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the number of distinct CustomerId field values within the current scope.

{CountDistinct(CustomerId)}

CountRows

Returns a number of rows within the specified scope.

Syntax

CountRows([scope])

Arguments

  • scope - the optional calculation scope

Examples

You can use the following expression to return the number of rows within the current scope.

{CountRows()}

DistinctSum

Returns the sum of distinct non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

DistinctSum(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the sum of distinct Size field values within the current scope.

{DistinctSum(Size)}

First

Returns the first value of the specified expression, calculated in the specified scope.

Syntax

First(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the first value of the ProductName field in the ProductsTable data region.

{First(ProductName, "ProductsTable")}

Last

Returns the last value of the specified expression, calculated in the specified scope.

Syntax

Last(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the last value of the SalesAmount field in the current scope.

{Last(SalesAmount)}

Max

Returns the maximum value of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

Max(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the maximum value of Size field values within the current scope.

{Max(Size)}

Median

Returns the median of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

Median(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the median of SalesAmount field values within the Sales data region.

{Median(SalesAmount, "Sales")}

Min

Returns the minimum value of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

Min(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the minimum value of Size field values within the current scope.

{Min(Size)}

Mode

Returns the value that appears most often among values specified by the expression, calculated in the specified scope.

Syntax

Mode(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the most often value among values of the ReturnReason field within the current scope.

{Mode(ReturnReason)}

RunningValue

Returns a running aggregate of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

RunningValue(expression, aggregate, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • aggregate - the name of the aggregate function to use for calculation, for example, Count
  • scope - the optional calculation scope

Examples

You can use the following expression to return the running value of the sum of the SalesAmount field values within the current scope.

{RunningValue(SalesAmount, "Sum")}

StDev

Returns the standard deviation of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

StDev(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the standard deviation of the ReturnAmount field values within the current scope.

{StDev(ReturnAmount)}

StDevP

Returns the population standard deviation of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

StDevP(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the population standard deviation of ReturnAmount field values within the current scope.

{StDevP(ReturnAmount)}

Sum

Returns the sum of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

Sum(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the sum of SalesAmount field values within the current scope.

{Sum(SalesAmount)}

Var

Returns the variance of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

Var(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the variance of the ReturnAmount field values within the current scope.

{Var(ReturnAmount)}

VarP

Returns the population variance of non-null numeric values specified by the expression, calculated in the specified scope.

Syntax

VarP(expression, [scope])

Arguments

  • expression - the expression on which to calculate the summary value
  • scope - the optional calculation scope

Examples

You can use the following expression to return the population variance of ReturnAmount field values within the current scope.

{VarP(ReturnAmount)}