[{"id":"bf522b5c-b992-4910-b9a3-7d4c6a711342","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"fd92c740-60f2-42bc-b108-133c0baaec15","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"1809c8ed-488b-4fb9-b402-aabe76f0c6d2","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"8aa13734-ff9b-43f5-b44f-de331adbb3d7","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"8faaa45c-575e-4bf0-9034-634fac906bb8","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"56faca6f-14d3-42b0-9706-7df8e339ef70","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"eb780c12-7575-42ef-916d-9d1d66cd0152","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"3918314b-5267-494d-b520-203125daf2a2","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"97d3eeb1-76c4-4c11-bc62-db5413d6819f","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"e93f9124-e327-4056-9264-f62378c3ebb5","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"90683712-67eb-49c8-96cb-96a228a99d11","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"cf1d42c1-fe4a-4f31-83f7-97c9c7ea9b76","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"e6222e08-3705-44f2-9027-b80b72dd3a85","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"5f8d0a4e-e3c5-4f38-91ee-c0d4aa8f116d","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"1ab019cd-705f-4371-a3f3-f2925cf92826","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"2ebee074-0c53-44bf-89c4-c0deb0d210f7","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"488a368a-fad1-4022-8e64-03442a912d2c","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"9aa35d28-77db-4dbd-ade0-17163a1782be","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"5a8c2832-0b00-47dc-aa9c-2579f4d9e04c","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"c19b5fcc-8f2a-4f44-9eeb-345cbb4815a6","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"92556861-b9b0-454e-a493-4750e9d90d2c","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"5ca1a599-fa29-4d8a-8cb4-80352932149f","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"7a947fc6-0187-4b9d-b9cc-800def67c597","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"b74ce673-ba64-42ea-b124-fc006b6b727e","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"a201d71e-7541-4e64-ac8b-3ed714e41a81","tags":[{"product":null,"links":null,"id":"fe610228-9cf6-417f-813a-dcbfd25406c3","name":"upd","color":"#7e678a","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]},{"id":"9efb31b1-f5f2-4214-a36d-c3415649a7a0","tags":[{"product":null,"links":null,"id":"4d7b6a40-ab32-4c71-a381-58f3ffd2653e","name":"new","color":"#ed7422","productId":"d699a6af-e150-4da3-ab30-25fd97934601"}]}]
DEMO DOCS API
DOWNLOAD
(Showing Draft Content)

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.


Another optional argument of some aggregate functions is recursive that should be set to Recursive if you want to display summaries that include values from child groups in a recursive hierarchy. For example, the Interactive Hierarchy demo uses the {Sum(EmployeeCount, "EntityKey", "Recursive")} expression to display the cumulative Employee Count for cities, regions, and countries.

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

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], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

Examples


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

{VarP(ReturnAmount)}

ToArray

Returns an Array containing the values of the specified expression.


Syntax


ToArray(expression, [scope], [recursive])


Arguments

  • expression - the expression on which to calculate the summary value

  • scope - the optional calculation scope

  • recursive - the optional Recursive value indicating whether to perform the calculation recursively

Examples


You can use the following expression to return the ProductName field values separated by a comma within the current scope.

{Join(ToArray(ProductName), " ,")}