The data composition system expression language is intended for recording expressions used in several 1C:Enterprise system features. In particular, it is used in the data composition system for describing custom field expressions.
Literals
The expression may contain literals. The following types of literals are allowed:
- String
- Number
- Date
- Boolean.
String
Enclose string literals in double quotation marks (").
"String literal"
If you need to include a double quotation mark (") in a string literal, use two double quotation marks.
"Literal ""with a quoted text"""
Number
Write numbers without spaces, in decimal format. Use period (.) as a decimal separator.
10.5 200
Date
For Date literals, use the DATETIME key literal. After the keyword, specify the year, month, day, hours, minutes, and seconds in parentheses, separated by commas. Specifying the time part is optional.
DATETIME(1975, 1, 06) stands for January 6, 1975 DATETIME(2006, 12, 2, 23, 56, 57) stands for December 2, 2006, 11:56:57 PM
Boolean
For Boolean values, use True and False literals.
VALUE
For literals of other types (system enumerations or predefined data), use the VALUE keyword followed by the literal name in parentheses.
VALUE(AccountType.Active)
Fields
Expressions can include fields of data sets. A field is identified by data path. Parts of a data path are separated by periods (.) Field names are not case-sensitive.
Item.SKU Sales.AmountTurnover
Parameters
Expressions can include parameters. To include a parameter in an expression, precede the parameter name with an ampersand (&).
&Counterparty &StartDate
1.1. Operations with numbers
Unary –
This operation negates a number.
–Sales.Quantity
Unary +
This operation does not change a number.
+Sales.Quantity
Binary –
This operation calculates a difference of two numbers.
BalanceAndTurnovers.OpeningBalance – BalanceAndTurnovers.ClosingBalance BalanceAndTurnovers.OpeningBalance – 100 400 – 357
Binary +
This operation calculates a sum of two numbers.
BalanceAndTurnovers.OpeningBalance + BalanceAndTurnovers.Turnover BalanceAndTurnovers.OpeningBalance +100 400 + 357
Multiplication
This operation calculates a product of two numbers.
Item.Price * 1.2 2 * 3.14
Division
This operation calculates a quotient of two numbers.
Item.Price / 1.2 2 / 3.14
Remainder
This operation calculates a remainder in division of two numbers.
Item.Price % 1.2 2 % 3.14
1.2. Operations with strings
Concatenation (Binary +)
This operation concatenates two strings. It does not support strings of unlimited length. Strings longer than 1024 characters are treated as strings of unlimited length.
Item.SKU + ": "+ Item.Description
LIKE
This operation checks whether a string matches the specified template.
The value of the LIKE operator is True if the expression value matches the template, and False otherwise.
The following characters in a template have a special meaning:
- % (percent sign). A sequence containing zero or more custom characters.
- _ (underscore). Any single character.
- [...] (one or more characters in brackets). Any of the characters listed inside the brackets. A character set can contain ranges, such as a–z, meaning any character within the range, including the limits of the range.
- [^...] (a negation sign followed by one or more characters, in brackets). Any character except the characters enclosed in brackets.
Any other character represents itself and does not have any additional use. If you need to use one of the special characters as itself, precede it with the ESCAPE keyword.
For example, the following template stands for a substring including the following sequence of characters: the letter A; the letter B; the letter C; a digit; one of the letters a, b, c, or d; an underscore; the letter a; the letter b; the letter c. This sequence can begin anywhere in the string.
"%ABC[0–9][абвг]\_abc%" ESCAPE"\"
1.3. Comparison operations
EQUAL TO
This operation compares two operands.
Sales.Counterparty = Sales.MainItemSupplier
NOT EQUAL
This operation checks whether the first operand is not equal to the second operand.
Sales.Counterparty <> Sales.MainItemSupplier
LESS THAN
This operation checks whether the first operand is less than the second operand.
CurrentSales.Amount < PreviousSales.Amount
GREATER THAN
This operation checks whether the first operand is greater than the second operand.
CurrentSales.Amount > PreviousSales.Amount
LESS OR EQUAL
This operation checks whether the first operand is less than or equal to the second operand.
CurrentSales.Amount <= PreviousSales.Amount
GREATER OR EQUAL
This operation checks whether the first operand is greater than or equal to the second operand.
CurrentSales.Amount >= PreviousSales.Amount
IN (for values)
This operation checks whether a value is present in the specified list of values. The operation result is True if the value is found, or False otherwise.
Item IN (&Product1, &Product2)
IN (for data sets)
This operation checks whether a value is present in the specified data set. The data set must contain a single field.
Sales.Counterparty IN Counterparties
IS NULL
This operation returns True if the specified value is NULL.
Sales.Counterparty IS NULL
IS NOT NULL
This operation returns True if the specified value is not NULL.
Sales.Counterparty IS NOT NULL
1.4. Logical operations
Logical expressions use Boolean values as operands.
NOT
This operation returns True if its operand has the False value, or False if its operand has the True value.
NOT Document.Consignee = Document.Shipper
AND
This operation returns True if both operands have the True value, or False if one of the operands has the False value.
Document.Consignee = Document.Shipper AND Document.Consignee = &Counterparty
OR
This operation returns True if one of the operands has the True value, or False if both operands have the False value.
Document.Consignee = Document.Shipper OR Document.Consignee = &Counterparty
1.5. Aggregate functions
Aggregate functions perform certain actions on data sets.
SUM
This function calculates a sum of values for expressions specified in the parameter, for all detailed records. You can use a result of the Array function as a parameter.
SUM(Sales.AmountTurnover)
COUNT
This function calculates the number of values that are different from the NULL value. You can use a result of the Array function as a parameter. Example:
COUNT(Sales.Counterparty)
COUNT (DISTINCT)
This function calculates the number of distinct values. You can use a result of the Array function as a parameter.
COUNT(DISTINCT Sales.Counterparty)
MAX
This function gets the maximum value. You can use a result of the Array function as a parameter.
MAX(Balances.Quantity)
MIN
This function gets the minimum value. You can use a result of the Array function as a parameter.
MIN(Balances.Quantity)
AVG
This function gets the mean value for values other than NULL. You can use a result of the Array function as a parameter.
AVG(Balances.Quantity)
ARRAY
This function returns an array that contains a parameter value for each detailed record. If the expression specified in the parameter contains the Array function, it is considered an aggregate expression. The function parameter can have arbitrary type.
You can use an expression that returns a value table as a parameter. In this case, the Array function returns an array that contains values from the first column of the source value table.
ARRAY(OfferCount)
VALUESTABLE
This function returns a value table that contains the number of columns that matches the number of function parameters. Detailed records are produced from data sets that are required for getting all the fields used in the function parameter expressions. If an expression contains a ValuesTable function, it is considered an aggregate expression.
The function can have one or several parameters of arbitrary types. Each parameter can be followed by an optional AS keyword and a name to be assigned to a value table column.
ValueTable(Distinct Products, ProductCharacteristic AS Characteristic)
GROUPPROCESSING
This function returns a DataCompositionGroupProcessingData object with the following properties:
- Data. Type: a value table. The function populates this property with a value table containing the calculation results for the expression specified in the first parameter of the function for each group record of a grouping. If the grouping is hierarchical, each hierarchy level is processed separately, and the values of hierarchical records are also included in the data.
- CurrentItem. A row in the table of current values. When called for the overall, the CurrentItem contains the Undefined value. The function populates this property with a value table row for which the result is being calculated.
- ProcessingTempData. A structure that can store data subtotals. It is recommended that you assign different names to properties of different functions, since a single DataCompositionGroupProcessingData object can be passed to several functions.
This function has the following parameters:
- Expressions. A string containing comma-separated expressions to be calculated. Each expression can be followed by an optional AS keyword and a column name in the resulting value table.
- HierarchyExpressions. Expressions to be calculated for hierarchical records. It is similar to the Expressions parameter, but HierarchyExpressions is used for hierarchical records, while Expressions is used for nonhierarchical records. If this parameter is not set, expressions specified in the Expression parameter are used to calculate values for hierarchical records.
- GroupName. Name of the grouping where the processing grouping is calculated. String. If this parameter is not specified, the calculation is performed in the current grouping. If the calculation is performed in a table and the parameter is a blank string or is not specified, the value is calculated for the row grouping. The template composer replaces this name with the grouping name in a generated data composition template. If the grouping is unavailable, the function is replaced with the NULL value.
GroupProcessing ("Sum(AmountTurnover)")
GROUPBY
This function deletes duplicates from an array. It returns an array or a value table without duplicates.
Parameters:
- An Array or a value table.
- Value table column numbers or names to search for duplicates (comma-separated). By default, the search is performed in all columns.
GETPART
This function returns a value table that contains specific columns from the source value table.
Parameters:
- A source value table.
- Column names or numbers (comma-separated).
Returns: a value table that contains only columns specified in the Column numbers/names parameter.
ORDER
This function orders array elements.
Parameters:
- An Array or a ValueTable.
- A name or a number of a value table column to order by. For arrays, a number is not required. Order direction: Autoorder required. Desc/Asc + Autoorder.
Returns: an array or a value table with the ordered elements.
JOINSTRINGS
This is an aggregate function that joins strings.
Parameters:
- Values to join into a string. If it is an array, array elements are joined into a string. If it is a value table, all columns and rows are joined into a string.
- Element separator. A string that contains the text to be used as a separator between array elements and value table rows. The default value is the line feed character.
- Column separators. A string that contains the text to be used as a separator between value table columns. The default value is semicolon (;).
Every
If at least one record has the False value, this function returns False. Otherwise it returns True.
Syntax:
Every(Expression)
Parameter:
Expression. Type: Boolean.
Every()
Any
When at least one record has the True value, this function returns True. Otherwise it returns False.
Syntax:
Any(Expression)
Parameter:
Expression. Type: Boolean.
Any()
Stddev_Pop
This function calculates the population standard deviation. It uses the following formula: SQRT(Var_Pop(X)).
Syntax: Stddev_Pop(Expression)
Parameter:
Expression. Type: Number.
Return value: Number.
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
This function calculates the cumulative sample standard deviation. It uses the following formula: SQRT(Var_Samp(X)).
Syntax:
Stddev_Samp(Expression)
Parameter:
Expression. Type: Number.
Return value: Number.
X |
Y |
1 |
7 |
2 |
1 |
3 |
2 |
4 |
5 |
5 |
7 |
6 |
34 |
7 |
32 |
8 |
43 |
9 |
87 |
SELECT Stddev_Samp(Y) FROM Table
Result:
28.3847573
Var_Samp
This function calculates the sample variance of a set of numbers after discarding NULL values from this set. It uses the following formula: (Sum(X^2) - Sum(X)^2/Quantity(X))/(Quantity(X) - 1).
If Quantity(X) = 1, the return value is NULL.
Syntax:
Var_Samp(Expression)
Parameter:
Expression. Type: Number.
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
This function calculates the population variance of a set of numbers after discarding NULL values from this set. It uses the following formula: (Sum(X^2) - Sum(X)^2/Quantity(X))/Quantity(X).
Syntax:
Var_Pop(Expression)
Parameter:
Expression. Type: Number.
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
This function calculates the covariance of a set of number pairs. It uses the following formula: (Sum(Y * X) - Sum(X) * Sum(Y)/n)/n, where n stands for the number of pairs (Y, X) where neither Y nor X is NULL.
Syntax:
Covar_Pop(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the sample variance of a set of numbers after discarding NULL values from this set. It uses the following formula: (Sum(Y * X) - Sum(Y) * Sum(X)/n)/(n-1), where n stands for the number of pairs (Y, X) where neither Y nor X is NULL.
Syntax:
Covar_Samp(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the coefficient of correlation of a set of number pairs. It uses the following formula: Covar_Pop(Y, X)/(Stddev_Pop(Y) * Stddev_Pop(X)). The pairs where Y or X is NULL are discarded.
Syntax:
Corr(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the slope of the regression line. It uses the following formula: Covar_Pop(Y, X)/Var_Pop(X). The value is calculated after discarding all pairs with NULL.
Syntax:
Regr_Slope(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the Y-intercept of the regression line. It uses the following formula: AVG(Y) - Regr_Slope(Y, X) * AVG(X). Pairs with NULL values are discarded.
Syntax:
Regr_Intercept(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the number of non-NULL number pairs.
Syntax:
Regr_Count(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the coefficient of determination. The value is calculated after discarding all pairs with NULL.
Syntax:
Regr_R2(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
Returns:
NULL if Var_Pop(X)=0; 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.
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
This function calculates the average Y after excluding X and Y pairs where either X or Y is blank. The average (X) is calculated after discarding pairs with NULL.
Syntax:
Regr_AvgX(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
Example:
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
This function calculates the average X after excluding X and Y pairs where either X or Y is blank. The average (Y) is calculated after discarding pairs with NULL.
Syntax:
Regr_AvgY(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
This function calculates the sum of squares of independent expressions used in a linear regression model. You can use this function to evaluate the statistical validity of a regression model.
It uses the following formula: Regr_Count(Y, X) * Var_Pop(X). The value is calculated after discarding all pairs with NULL.
Syntax:
Regr_SXX(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
It uses the following formula: Regr_Count(Y, X) * Var_Pop(X).
The value is calculated after discarding all pairs with NULL.
Syntax:
Regr_SYY(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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
It uses the following formula: Regr_Count(Y, X) *Covar_Pop(Y, X). The value is calculated after discarding all pairs with NULL.
Syntax:
Regr_SXY(Y, X)
Parameters:
- Y. Type: Number.
- X. Type: Number.
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. Type: String. Contains expressions for ordering comma-separated group records. You can set the order direction by using the words Asc and Desc. You can add AutoOrder after the field, which means that the ordering fields defined for the referred object are used for ordering references. If the order is not specified, the value is calculated based on the grouping.
- HierarchyOrder. Type: String. Contains expressions for ordering hierarchical records.
- GroupName. Type: String. The name of the grouping where the group processing is calculated. If this parameter is not specified, the calculation is performed in the current grouping. If the calculation is performed in a table and the parameter is a blank string or is not specified, the value is calculated for the row 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 unavailable, the function is replaced with NULL.
Rank("[Quantity Turnover]")
ClassificationABC
This function returns the class number, starting with 1 (which represents Class A).
Syntax:
ClassificationABC(Value, GroupCount, PercentageForGroups, GroupName)
Parameters:
- Value. Type: String. A value for calculating the classification. It is a string that contains an expression.
- GroupCount. Type: Number. The number of groups for splitting.
- PercentageForGroups. Type: Number.The volume (in %) of each splitting group, except the last group. Listed in a string, separated by commas.
- GroupName. Type: String. The name of the grouping for group processing calculation. If this parameter is not specified, the calculation is performed in the current grouping. If the calculation is performed in a table and the parameter is a blank string or is not specified, the value is calculated for the row 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 unavailable, the function is replaced with NULL.
ClassificationABC("AmountTurnover, 3, "15, 25")
1.6. Other operations
CASE operation
This operation selects one of several values based on the specified conditions.
CASE When Amount > 1000 Then Amount Else 0 End
Rules for comparing two values
If types of values being compared are not identical, the relationship between the values is defined based on the type priority:
- NULL (lowest)
- Boolean
- Number
- Date
- String
- reference types
The relationship between different reference types is defined based on table reference numbers corresponding to one type or another.
If the data types are identical, their values are compared according to the following rules:
- For Boolean type, TRUE is greater than FALSE.
- For Number type, common number comparison rules are applied.
- For Date type, earlier dates are less than later dates.
- For String type, the comparison is based on the regional database settings.
- Reference types are compared according to their values (for example, record numbers).
Operations with NULL value
Any operation where one of the operands has the NULL value returns NULL.
The exceptions are:
- The AND operation returns NULL only if none of the operands has the False value.
- The OR operation returns NULL only if none of the operands has the True value.
Operation priorities
Operations have the following priorities (the first operation in the list has the lowest priority):
- OR
- And;
- NOT
- IN, IS NULL, IS NOT NULL
- =, <>, <=, <, >=, >;
- Binary +, Binary –
- *, /, %;
- Unary +, Unary –
1.7. Functions
EVAL
This function calculates an expression within a certain grouping. This is a legacy function, it is available for compatibility with the earlier platform versions. We recommend that you use the EvalExpression function instead.
This function has the following parameters:
- Expression. A string that contains an expression to be calculated.
- Grouping. A string that contains a name of a grouping where an expression is calculated. If the parameter value is a blank string, the calculation is performed in the context of the current grouping. If the parameter value is Overall, the calculation is performed in the context of the grand total. In other cases the calculation is performed in the context of the parent grouping with this name.
- Calculation type. A string that contains the calculation type. If the parameter value is Overall, the expression is calculated for all the records of the grouping. If the parameter value is Grouping, the values are calculated for the current group record of the grouping.
SUM(Sales.AmountTurnover)/EVAL("Sum(Sales.AmountTurnover)", "Overall")
In this example the result is the ratio of the sum of the grouping record by the Sales.AmountTurnover field to the sum of all composition records by this field.
EVALEXPRESSION
This function returns the expression for the record of the specified grouping. This function has the following parameters:
- Calculated expression. A string that contains an expression to be calculated.
- Grouping. A string that contains a name of a grouping where an expression is calculated. If the parameter value is a blank string, the calculation is performed in the context of the current grouping. If the parameter value is Overall, the calculation is performed in the context of the grand total. If the calculation is performed in a table and the parameter value is a name of a row grouping, the calculation is performed for the grouping formed by the intersection of the calculation area (see the third parameter description) and the current row of the column grouping. If the parameter value is a column grouping name, the calculation is performed for the grouping formed by the intersection of the column grouping calculation area and the current row grouping record.
- Calculation area. A string that contains a calculation area. If the parameter is not specified, the calculation is performed for the current record of the grouping specified in the first parameter. The parameter can take values:
- Overall. The expression will be evaluated for all the grouping records.
- Hierarchy. The expression will be calculated for the parent hierarchical record, if any, or for the entire grouping if there is no parent hierarchical record.
- Grouping. The expression will be calculated for the current group record of the grouping.
- NonResourceGrouping. When calculating the function for group record by resources, the expression will be calculated for the first group record of the original grouping.
When calculating the EvaluateExpression function result with the NonResourceGroup value for group records that are not grouped by resources, the function result is calculated in the same way as if with the Group parameter value.
When the data composition template composer generates a data composition template, it outputs an expression calculated using the EvaluateExpression function with the NonResourceGroup parameter as a grouping resource field. For other grouping resources, it outputs standard resource expressions.
When generating an expression for the % in hierarchy group field, the template composer generates an expression that contains a ratio between the resource expression and the EvaluateExpression function result for the resource expression calculated for the current grouping with the Hierarchy calculation type.
- Start. Specifies the first record of a fragment where the aggregate functions of the expression are calculated and the record for retrieving field values outside aggregate functions. A string that contains one of the following:
- First. Get the first grouping record. The expression whose result will define the shift from the beginning of the grouping may be specified in parentheses after this word. The resulting value must be a positive integer. For example, use First(3) to get the third record from the beginning of the grouping. If the first record is outside the bounds of the grouping, it is accepted that there are no records. For example, if there are 3 records and First(4) is specified, it is considered that there are no records.
- Last. Get the last grouping record. The expression whose result will define the shift from the end of the grouping may be specified in parentheses after this word. The resulting value must be a positive integer. For example, use Last(3) to get the third record from the end of the grouping. If the last record is outside the bounds of the grouping, it is accepted that there are no records. For example, if there are 3 records and Last(4) is specified, it is considered that there are no records.
- Previous. Get the previous grouping record. The expression whose result will define the shift back from the current grouping record may be specified in parentheses after this word. For example, use Previous(2) to get the previous to the previous record. If the previous record is beyond the grouping (for example, you get Previous(3) for the second record of the grouping), the result is the first grouping record. If the previous record is retrieved for a grouping total, the result is the first record.
- Next. Get the next grouping record. The expression whose result will define the shift forward from the current grouping record may be specified in parentheses after this word. For example, with Next(2) the next from the next record is retrieved. If the next record is beyond the grouping, it is considered that there are no records. For example, if there are 3 records and Next is specified for the third record, it is considered that there are no records. If the next record is retrieved for a grouping total, it is considered that there are no records.
- Current. Get the current record.
If a record is retrieved for a grouping total, the result is the first record.
- BoundaryValue. Get a record by the specified value. The BoundaryValue keyword must be followed by an expression of the first order field that begins the fragment, in parentheses.
The first record with an order field value greater than or equal to the specified value is retrieved. For example, if the Period field is used as an order field, this field has the values 01/01/2010, 02/01/2010, and 03/01/2010, and you get BoundaryValue(DateTime(2010, 1, 15)), a record with the date 02/01/2010 is retrieved.
- End. Specifies the last record of a fragment where the aggregate functions are calculated. A string that contains one of the following:
- First. Get the first grouping record. The expression whose result will define the shift from the beginning of the grouping may be specified in parentheses after this word. The resulting value must be a positive integer. For example, with First(3) the third record from the beginning of the grouping is retrieved.
If the first record is beyond the grouping, it is considered that there are no records. For example, if there are 3 records and First(4) is specified, it is considered that there are no records.
- Last. Get the last grouping record. The expression whose result will define the shift from the end of the grouping may be specified in parentheses after this word. The resulting value must be a positive integer. For example, with Last(3) the third record from the end of the grouping is retrieved.
If the last record is beyond the grouping, it is considered that there are no records. For example, if there are 3 records and Last(4) is specified, it is considered that there are no records.
- Previous. Get the previous grouping record. The expression whose result will define the shift back from the current grouping record may be specified in parentheses after this word. For example, with Previous(2) the previous from the previous record is retrieved.
If the previous record is beyond the grouping (for example, you get Previous(3) for the second record of the grouping), it is considered that there are no records.
If the previous record is retrieved for a grouping total, the result is the last record.
- Next. Get the next grouping record. The expression whose result will define the shift forward from the current grouping record may be specified in parentheses after this word. For example, with Next(2) the next from the next record is retrieved.
If the next record is beyond the grouping, the result is the last record. For example, if there are 3 records and Next is specified for the third record, the result is the third record.
If the next record is retrieved for a grouping total, it is considered that there are no records.
- Current. Get the current record.
If a record is retrieved for a grouping total, the result is the first record.
- BoundaryValue. Get a record by the specified value. The BoundaryValue keyword must be followed by an expression of the first order field that begins the fragment, in parentheses.
The last record with an order field value less than or equal to the specified value is retrieved. For example, if the Period field is used as an order field, this field has the values 01/01/2010, 02/01/2010, and 03/01/2010, and you get BoundaryValue(DateTime(2010, 1, 15)), a record with the date 01/01/2010 is retrieved.
- First. Get the first grouping record. The expression whose result will define the shift from the beginning of the grouping may be specified in parentheses after this word. The resulting value must be a positive integer. For example, with First(3) the third record from the beginning of the grouping is retrieved.
- Sorting. A string that contains comma-separated expressions to order the sequence by. If no string is specified, the values are ordered similarly to the grouping for which an expression is calculated. Each expression may be followed by the Asc keyword to group in ascending order or the Desc keyword to group in descending order, or Autoorder to order reference fields by fields to order the referenced object by. The Autoorder keyword can be used together with the Asc or Desc keyword.
- HierarchicalSorting. A string that contains comma-separated expressions to order the sequence by. It is used for ordering hierarchical records. If this parameter is not specified, the records are ordered as described in the Sorting parameter.
- IdenticalOrderValuesProcessing. A string that contains one of the following:
- Together. Define the previous and next records based on the order expression values.
- Separately. Define the previous and next records based on a sequence of ordered records.
For example, a sequence is ordered by date:
1. January 1, 2001 M. Ivanov 10 2. January 2, 2001 S. Petrov 20 3. January 2, 2001 P. Sidorov 30 4. January 3, 2001 S. Petrov 40
If the Separately order option is used to process identical values, record 2 is previous to record 3. If the Together option is used, this would be record 1. With the Separately option, a fragment for the record that is current to record 2 is record 2. With the Together option, it would include records 2 and 3. Therefore, with the Separately option, the sum by the current record would be 20. With the Together option, it would be 50.
If Together is specified in the Start and End parameters, shifts for the First, Last, Previous, and Next positions cannot be specified.
Separately is the default option.
Parameter usage example
To calculate a stacked sum, use the following expression:
EvaluateExpression("Sum(AmountTurnover)", , , "First", "Current")
To retrieve a grouping value in this example, use the following expression:
EvaluateExpression("Rate", , , "Previous")
The EvaluateExpression function is calculated based on grouping filters, but not hierarchical filters.
The EvaluateExpression function cannot be applied to a grouping in a group filter of that grouping. For example, you cannot use the EvaluateExpression("Sum(AmountTurnover)", , "Overall") > 1000 expression in the Products grouping filter, but you can use it in a hierarchical filter.
If the last record precedes the first one, it is considered that there are no records for calculating detailed data and aggregate functions.
When interval expressions are calculated for the overall (the second parameter of EvaluateExpression function has the Overall value), it is considered that there are no records for calculating detailed data and aggregate functions.
LEVEL
This function returns the current record level.
LEVEL()
LEVELINGROUP
This function returns the record level relative to the root of the grouping.
LEVELINGROUP()
VALUEISFILLED
This function returns True if the value is not equal to the default value for this type, is not NULL, is not a blank reference, and is not Undefined. Logical values are checked for NULL. Strings are checked for absence of nonwhitespace characters.
SERIALNUMBER
This function returns the next sequential number.
SERIALNUMBER()
GROUPSERIALNUMBER
This function returns the next sequential number in the current grouping.
SEQUENTIALNUMBERINGROUPING()
FORMAT
This function returns a formatted string of the passed value. The format string is set according to the rules defined in 1C:Enterprise script.
Parameters:
- Value,
- Format string.
FORMAT(Invoices.AmountDoc, "NFD=2")
BEGINOFPERIOD
This function extracts a certain date from a given date.
Parameters:
- Expression. Type: Date.
- Period type. A string containing one of the following values:
- Minute
- Hour
- Day
- Week
- Month
- Quarter
- Year
- TenDays
- HalfYear
BEGINOFPERIOD(DATETIME(2009, 10, 12, 10, 15, 34), "Month")
Result:
10/01/2009 12:00:00 AM
ENDOFPERIOD
This function extracts a certain date from a given date.
Parameters:
- Expression. Type: Date.
- Period type. A string containing one of the following values:
- Minute
- Hour
- Day
- Week
- Month
- Quarter
- Year
- TenDays
- HalfYear
ENDOFPERIOD(DATETIME(2009, 10, 12, 10, 15, 34), "Week")
Result:
10/13/2009 11:59:59 PM
DATEADD
This function adds a value to a date. Parameters:
- Expression. Type: Date.
- Increment type. A string containing one of the following values:
- Second
- Minute
- Hour
- Day
- Week
- Month
- Quarter
- Year
- TenDays. The function adds or subtracts the specified number of days multiplied by 10.
- HalfYear
- Size. The size of date increment. Number. Fractions are ignored.
DATEADD(DATETIME(2009, 10, 12, 10, 15, 34), "Month", 1)
Result:
11/12/2009 10:15:34 AM
DATEDIFF
This function returns an interval between two dates. Parameters:
- Expression. Type: Date.
- Expression. Type: Date.
- Difference type. One of the following values:
- Second
- Minute
- Hour
- Day
- Month
- Quarter
- Year
DATEDIFF(DATETIME(2009, 10, 12, 10, 15, 34), DATETIME(2009, 10, 14, 9, 18, 06), "Day")
Result:
2
CURRENTDATE
This function returns the system date. During the composition of the composition template all the expressions available in the composition have the CURRENTDATE function replaced with the current date value.
CURRENTDATE()
SUBSTRING
This function extracts a substring from a string. Parameters:
- an expression of String type. If the first parameter is a string, the result is a string (it can have zero length). If the first parameter is NULL, the result is also NULL. Other values are considered invalid and will cause an error;
- The position of the first character of the substring.
- The substring length.
SUBSTRING(Counterparties.Address, 1, 4)
STRINGLENGTH
This function returns a string length. The parameter is an expression of String type.
STRINGLENGTH(Counterparties.Address)
YEAR
This function returns a year from a Date value. The parameter has the Date type.
YEAR(Invoice.Date)
QUARTER
This function returns a quarter from a Date value. The quarter number is in the range from 1 to 4. The parameter has the Date type.
QUARTER(Invoice.Date)
MONTH
This function returns a month from a Date value. The month number is in the range from 1 to 12. The parameter has the Date type.
MONTH(Invoice.Date)
DAYOFYEAR
This function returns a day of the year from a Date value. The day number is in the range from 1 to 365 (366). The parameter has the Date type.
DAYOFYEAR(Invoice.Date)
DAY
This function returns a day of the month from a Date value. The day number is in the range from 1 to 31. The parameter has the Date type.
DAY(Invoice.Date)
WEEK
This function returns a week number from a Date value. The first week of the year has number 1. The parameter has the Date type.
WEEK(Invoice.Date)
WEEKDAY
This function returns a day of the week from a Date value. The day of the week is in the range from 1 (Monday) to 7 (Sunday). The parameter has the Date type.
WEEKDAY(Invoice.Date)
HOUR
This function returns a number of hours from a Date value. The number is in the range from 0 to 23. The parameter has the Date type.
HOUR(Invoice.Date)
MINUTE
This function returns a number of minutes from a Date value. The number is in the range from 0 to 59. The parameter has the Date type.
MINUTE(Invoice.Date)
SECOND
This function returns the number of seconds from a Date value. The number is in the range from 0 to 59. The parameter has the Date type.
SECOND(Invoice.Date)
CAST
This function gets a type from an expression that can contain a composite type. If the expression contains a type other than the required type, it returns NULL.
Parameters:
- Expression.
- Type. A string containing the type string. For example, Number, String, and so on. In addition to primitive types, the string can also contain a table name. In this case the function attempts to cast to a reference to that table.
CAST(Data.Attribute1, "Number(10,3)")
ISNULL
This function returns the second parameter value if the first parameter has the NULL value. Otherwise it returns the first parameter value.
ISNULL(Sum(Sales.AmountTurnover), 0)
Cos
This function calculates the cosine.
Syntax:
Cos(Expression)
Parameter:
Expression. Type: Number. It is expressed in radians.
ACos
This function calculates the arccosine (in radians).
Syntax:
ACos(Expression)
Parameter:
Expression. Type: Number. A cosine value in the range from -1 to 1.
Sin
This function calculates the sine.
Syntax:
Sin(Expression)
Parameter:
Expression. Type: Number. It is expressed in radians.
ASin
This function calculates the arcsine (in radians).
Syntax:
ASin(Expression)
Parameter:
Expression. Type: Number. A tangent value. A sine value in the range from -1 to 1.
Tan
This function calculates the tangent.
Syntax:
Tan(Expression)
Parameter:
Expression. Type: Number. A tangent value.
ATan
This function calculates the arctangent (in radians).
Syntax:
ATan(Expression)
Parameter:
Expression. Type: Number. A tangent value.
Exp
Returns e raised to the n-th power.
Syntax:
Exp(Expression)
Parameter:
Expression. Type: Number. A value of the power.
Log
This function calculates the natural logarithm.
Syntax:
Log(Expression)
Parameter:
Expression. Type: Number. A positive number.
Log10
This function calculates the logarithm of X to base 10.
Syntax:
Log10(Expression)
Parameter:
Expression. Type: Number. A positive number.
Pow
This function raises a number to the n-th power.
Syntax:
Pow(Base, Power)
Parameters:
- Base. Type: Number. The base of an exponentiation operation.
- Power. Type: Number. A value of the power.
Sqrt
This function calculates the square root.
Syntax:
Sqrt(Expression)
Parameter:
Expression. Type: Number. A nonnegative number.
Round
This function returns a number rounded to the required number of digits. It uses the standard rounding rules (1.5 is rounded as 2).
Syntax:
Round(Expression, Digits)
Parameters:
Expression. Type: Number. An original number. Digits. Type: Number. The number of fraction digits in the rounding result.
Int
This function returns the integer part of a number.
Syntax:
Int(Expression)
Parameter:
Expression. Type: Number. A fractional number.