Aggregate Functions
Total
Count
VariousCount
Maximum
Minimum
Average
Array
ValueTable
GroupBy
GetPart
Order
JoinStrings
GroupProcessing
Every
Any
Stddev_Pop
Stddev_Samp
Var_Samp
Var_Pop
Covar_Pop
Covar_Samp
Corr
Regr_Slope
Regr_Intercept
Regr_Count
Regr_R2
Regr_AvgX
Regr_AvgY
Regr_SXX
Regr_SYY
Regr_SXY
Rank
ClassificationABC
Aggregate functions perform certain actions on a set of data.
Important! If function option is String
and specifies field name with spaces, such name should be enclosed
in square brackets.
Example: "[Quantity Turnover]".
Total
The Total aggregate function calculates a total of expression values passed as an argument for all detailed records. You can pass Array as a parameter. In this case the function will be applied to the array content.
Example:
Total(Sales.SumTurnover)
Count
The Count function calculates the number of nonNULL values. You can pass Array as a parameter. In this case the function will be applied to the array content.
Example:
Count(Sales.Contractor)
VariousCount
This function calculates the number of different values. In order to get different values, before an option of the Count method specify (Distinct). You can pass Array as a parameter. In this case the function will be applied to the array content.
Example:
Count(Distinct Sales.Contractor)
Maximum
This function gets the maximum value. You can pass Array as a parameter. In this case the function will be applied to the array content.
Example:
Maximum(Balance.Quantity)
Minimum
This function gets the minimum value. You can pass Array as a parameter. In this case the function will be applied to the array content.
Example:
Minimum(Balances.Quantity)
Average
This function gets the mean value for nonNULL values. You can pass Array as a parameter. In this case the function will be applied to the array content.
Example:
Average(Balances.Quantity)
Array
This function creates an array that includes a parameter value for each detailed record.
Syntax:
Array([Distinct] Expression)
You can use a value table as a parameter. Note that in this case the function results in an array containing the values of the first column of the value table passed as a parameter.
If an expression includes the Array function, this expression is considered to be an aggregate expression.
If the Distinct keyword is specified, the retrieved array will not include duplicate values.
Example:
Array(Contractor)
ValueTable
The function generates a table of values with the number of columns equal to the number of function parameters. Detailed records are retrieved from data sets that are required to retrieve all the fields that are used in function parameter expressions.
Syntax:
ValueTable([Distinct] Expression1 [AS ColumnName1][, Expression2 [AS ColumnName2],...])
If fields of residual fields are used as function parameters, the resulting value table will include values for the entries by unique measurement combinations from other periods. Note that values are only obtained for residual fields, measurements, accounts, period fields and their attributes. Values of other fields in the entries from other periods are considered NULL.
If an expression includes the ValueTable function, this expression is considered to be an aggregate expression.
If the Distinct keyword is specified, the retrieved value table will not include rows with duplicate data.
Every parameter may be followed by the optional AS keyword and a name that will be applied to a column of the value table.
Example:
ValueTable(Distinct Products, ProductCharacteristic AS Characteristic)
GroupBy
This function removes duplicates from an array.
Syntax:
GroupBy(Expression, ColumnNumbers)
Parameters:

Expression  Array or ValueTable type expression that should have its item values grouped.

ColumnNumbers  (if the expression is ValueTable) String type. Numbers or names (commaseparated) of the value table columns where duplicates should be searched for. By default this includes all columns.
Example:
GroupBy(ValueTable(PhoneNumber, Address) ,"PhoneNumber").
GetPart
The function gets a value table that contains specific columns of the original value table.
Syntax:
GetPart(Expression, ColumnNumbers)
Parameters:

Expression  ValueTable type. A table of values the columns should be retrieved from.

ColumnNumbers  String type. Numbers or names (comma separated) of the value table columns that should be retrieved.
Returned value: ValueTable with only those columns that are specified in the parameter.
Example:
GetPart(GroupBy(ValueTable(PhoneNumber, Address) ,"PhoneNumber"),"PhoneNumber").
Order
Intended to order items of an array and value table.
Syntax:
Order(Expression, ColumnNumbers)
Parameters:

Expression  Array or ValueTable the columns should be retrieved from.

ColumnNumbers  (if the expression is of ValueTable type) numbers or names (comma separated) of the value table columns that should be used to order by. It may include the order direction and specify whether automatic ordering is required: Desc/Asc + AutoOrder.
Returned value: Array or ValueTable with ordered items.
Example:
Order(ValueTable(PhoneNumber, Address, CallDate),"CallDate Desc").
JoinStrings
Intended to join multiple strings into a single string.
Syntax:
JoinStrings (Value, ItemSeparator, ColumnSeparators)
Parameters:

Value  the expressions that should be joined into a single string. If it is an Array, array items will be joined into a string. If it is a ValueTable, all the table columns and rows will be joined into a string.

ItemSeparator  a string with a text that should be used as a separator between array items and rows of a value table. By default, it is a line feed character.

ColumnSeparators  a string with a text that should be used as a separator between columns of a value table. By default it is "; ".
Example:
JoinStrings(ValueTable(PhoneNumber, Address)).
GroupProcessing
Returns the DataCompositionGroupProcessingData
object. In the Data property the object will
have a value table with the grouping values for every expression
specified in the parameter of the Expressions
function. If hierarchical grouping is used, each hierarchy level
is processed separately. Values of hierarchical records are also
included in data.
The CurrentItem property of the object will hold
the row of the value table the function is currently calculated
for.
Syntax:
GroupProcessing(Expressions, HierarchyExpressions, GroupName)
Parameters:

Expressions. The expressions to be calculated. The string listing comma separated expressions to be calculated. Each expression may be followed by the optional AS keyword and a column name of the resulting value table. Each expression generates a column of the value table for the Data property of the DataCompositionGroupProcessingData object.

HierarchyExpressions. The expressions to be calculated for hierarchical records. Similar to the Expressions parameter except that the HierarchyExpressions parameter is used for hierarchical records. If the parameter is omitted, the expressions specified in the Expression parameter are used to calculate the values for hierarchical records.

GroupName. Name of the group to calculate processing grouping in. String. If omitted, calculations are carried out in the current grouping. If calculation is performed on a table and the parameter includes a null string or is blank, the value is calculated for the rows grouping. When the data composition template is generated, the template composer replaces this name with the grouping name in the resulting template. If the grouping is not available, the function will be replaced with NULL.
Every
If any record is False,
the result is False. Or True.
Syntax:
Every(Expression)
Parameter:
 Expression  Boolean
type.
Example:
Every()
Every(Expression)
Every()
Any
If any record is True, the result is True. Or False
Syntax:
Any(Expression)
Parameter:
 Expression  Boolean type.
Example:
Any()
Stddev_Pop
Standard deviation for an aggregate is calculated. The following formula is used: SQRT(Var_Pop(X))
Syntax:
Stddev_Pop(Expression)
Parameter:
 Expression  Number type.
Retrieved value type: Number.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Var_Samp(Y, X) FROM Table
Result: 805.694444
Stddev_Samp
Calculates total type standard deviation. The following formula is used: SQRT(Var_Samp(X))
Syntax:
Stddev_Samp(Expression)
Parameter:
 Expression  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT SelectionStdDeviation(Y) FROM Table
Result: 28.3847573
Var_Samp
Calculates typical variation of a number
sequence without taking NULL values into
account in this set. The following formula
is used: (Total(X^2)  Total(X)^2 / Count(X))
/ (Count(X)  1)
If Count(X) = 1, NULL is
retrieved.
Syntax:
Var_Samp(Expression)
Parameter:
 Expression  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Var_Samp(Y, X) FROM Table
Result: 805.694444
Var_Pop
Calculates variation of a number sequence aggregate without taking NULL values into account in this set. The following formula is used: (Total(X ^2)  Total(X)^2 / Count(X)) / Count(X)
Syntax:
Var_Pop(Expression)
Parameter:
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Var_Pop(Y, X) FROM Table
Result: 716.17284
Covar_Pop
Calculates covariation of a range of number pairs. The following formula is used: (Total(Y * X)  Total(X) * Total(Y) / n) / n, when n is the number of pairs (Y, X) where neither Y nor X are NULL.
Syntax:
Covar_Pop(Y, X)
Parameters:
 Y  Number type;
 X  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Covar_Pop(Y, X) FROM Table
Result: 59.4444444
Covar_Samp
Calculates typical variation of a number sequence without taking NULL values into account in this set. The following formula is used: (Total(Y * X)  Total(Y) * Total(X) / n) / (n1), where n is the number of pairs (Y, X) where neither Y nor X is NULL
Syntax:
Covar_Samp(Y, X)
Parameters:
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Covar_Samp(Y, X) FROM Table
Result: 66.875
Corr
Calculates correlation index of a range of number pairs. The following formula is used: Covar_Pop(Y, X) / (Stddev_Pop(Y) * Stddev_Pop(X)) The pairs where Y or X is NULL.
Syntax:
Corr(Y, X)
Parameters:
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Corr(X, Y) FROM Table
Result: 0.860296149
Regr_Slope
Calculates line slope. The following formula is used: Covar_Pop(Y, X) / Var_Pop(X). Calculated without the pairs with NULL.
Syntax:
Regr_Slope(Y, X)
Parameters:
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_Slope(Y, X) FROM Table
Result: 8.91666667
Regr_Intercept
Calculates Y cross point of regression line. The following formula is used: Average(Y)  Regr_Slope(Y, X) * Average(X). Calculated without the pairs with NULL.
Syntax:
Regr_Intercept(Y, X)
Parameters:
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_Intercept(Y, X) FROM Table
Result: 20.361111
Regr_Count
Calculates the number of pairs without NULL.
Syntax:
Regr_Count(Y, X)
Parameters:
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_Count(Y, X) FROM Table
Result: 9
Regr_R2
Calculates coefficient of determination. Calculated without the pairs with NULL.
Syntax:
Regr_R2(Y, X)
Parameters:
1 if Var_Pop(Y)=0 AND Var_Pop(X)<>0;
POW(Corr(Y,X),2)  if Var_Pop(Y)>0 AND Var_Pop(X)<>0
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_R2(Y, X) FROM Table
Result: 0.740109464
Regr_AvgX
Calculates average X after excluding pairs of X and Y where either X or Y is blank. Average(X) is calculated without the pairs with NULL.
Syntax:
Regr_AvgX(Y, X)
Parameters:
 Y  Number type;
 X  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_AvgX(Y, X) FROM Table
Result: 5
Regr_AvgY
Calculates average Y after excluding pairs of X and Y where either X or Y is blank. Average(Y) is calculated without the pairs with NULL.
Syntax:
Regr_AvgY(Y, X)
Parameters:
 Y  Number type;
 X  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_AvgY(Y, X) FROM Table
Result: 24.2222222
Regr_SXX
The following formula is used: Regr_Count(Y, X) * Var_Pop(X). Calculated without the pairs with NULL.
Syntax:
Regr_SXX (Y, X)
Parameters:
 Y  Number type;
 X  Number type.
Retrieves the total of square roots of independent expressions used in regression linear model. The function may be used to evaluate statistical validity of regression model.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_SXX(Y, X) FROM Table
Result: 60
Regr_SYY
The following formula is used: Regr_Count(Y, X) * Var_Pop(Y). Calculated without the pairs with NULL.
Syntax:
Regr_SYY (Y, X)
Parameters:
 Y  Number type;
 X  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_SYY(Y, X) FROM Table
Result: 6445.55556
Regr_SXY
The following formula is used: Regr_Count(Y, X) * Covar_Pop(Y, X) Calculated without the pairs with NULL.
Syntax:
Regr_SXY (Y, X)
Parameters:
 Y  Number type;
 X  Number type.
Example:
Table:
X Y 1 7 2 1 3 2 4 5 5 7 6 34 7 32 8 43 9 87 SELECT Regr_SXY(Y, X) FROM Table
Result: 535
Rank
Syntax:
Rank(Order, HierarchyOrder, GroupName)
Parameters:
 Order  String type. Contains statements that should have their comma separated group records ordered in their sequence. The ordering direction is managed by Asc, Desc words. The field may also be followed by AutoOrder string which means that ordering fields specified for the referenced object should be used for reference ordering. If the sequence is not specified, the value is calculated in the grouping sequence;
 HierarchyOrder  String type. Contains ordering statements for hierarchical records;
 GroupName  String type. Name of the group to calculate processing grouping in. If omitted, calculations are carried out in the current grouping. If calculation is performed on a table and the parameter includes a null string or is blank, the value is calculated for the rows grouping. When the data composition template is generated, the template composer replaces this name with the grouping name in the resulting template. If the grouping is not available, the function will be replaced with NULL.
If a sequence includes multiple records with identical values of ordering fields, the function will return identical values for all such records.
Example:
Rank("[Quantity Turnover]")
ClassificationABC
Syntax:
ClassificationABC(Value, GroupCount, PercentageForGroups, GroupName)
Parameters:
 Value  String type. Used to calculate classification by. String that specifies the expression;
 GroupCount  Number type; Specifies the number of groups to divide into;
 PercentageForGroups  String type; The number of groups to divide into minus 1. Comma separated. If omitted, automatically;
 GroupName  String type. Name of the group to calculate processing grouping in. If omitted, calculations are carried out in the current grouping. If calculation is performed on a table and the parameter includes a null string or is blank, the value is calculated for the rows grouping. When the data composition template is generated, the template composer replaces this name with the grouping name in the resulting template. If the grouping is not available, the function will be replaced with NULL.
The function results in class number beginning with 1 which corresponds to class A.
Example:
ClassificationABC("Total(Quantity Turnover)", 3, "60, 90")
See also:
Data Composition System Expression Language
Bilingual Presentation of Data Composition System Keywords