Appendix 1. Data composition system expression language – 1Ci Support

<< Prev Next >>

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:

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:

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:

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:

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.
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:

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)

Int

This function returns the integer part of a number.

Syntax:

Int(Expression)

Parameter:

Expression. Type: Number. A fractional number.

<< Prev Next >>