EvaluateExpression- a rather complex ACS function to understand, and examples of application in the reference information are rather scarce. This article discusses examples that are sure to be useful to every developer:
- cumulative total in the grouping;
- the running total in the crosstab;
- getting the previous value;
- output of PM in one line.
1. Getting the indicator on a cumulative total
Let's get the number of goods on a cumulative basis at the grouping level. To do this, we create a calculated field (see Figure 1).On the "Resources" tab, set the function for the calculated field:
CalculateExpression ("Sum (NumberTurnover)", "First", "Current")
which will sum up the number of products from the first record to the current one (see Figure 2).
If you want to get the quantity of goods on a cumulative basis at the level of detailed records, then the function Calculate Expression is set for the calculated field on the tab "Calculated fields" (see Figure 3).
Depending on the level of obtaining the cumulative total, we create a grouping (see Figure 4): at the resource level - grouping by goods, at the DZ level - grouping of detailed records.
 |
| Figure 4. Groupings of the report with a cumulative total |
2. Getting the value of the indicator from the previous line
Let's get the currency rate for the date and the previous date. To do this, create a calculated field and write the following expression in the expression field (see Figure 5):CalculateExpression (Course, Previous, Previous)
which will take the previous value of the currency rate for the current line, the last parameter of the function limits the data retrieval.
Since we are working at the level of detailed records, go directly to the "Settings" tab and create a grouping - detailed records.
3. Getting an indicator on a cumulative total in a cross-tab
Let's get the number of goods on a cumulative basis in the context of the period. To do this, we create a calculated field (see Figure 1). On the "Resources" tab, for a calculated field, we will specify the following expression (see Figure 6):Calculate Expression ("Sum (NumberTurnover)", "Period", "First", "Current")
which, at the grouping level, will calculate the quantity of goods in the interval from the first line to the current one in the context of the period for each item.
On the "Settings" tab, create a table grouped by item in a row and grouped by period in a column (see Figure 7).
4. Output of tabular section data in one line
Methods for displaying tabular section data in one line, including the method using the EvaluateExpression function, are discussed in the article1. Calculate (Eval)- is intended for evaluating an expression in the context of some grouping. The function is used for compatibility with previous versions of the platform. It is recommended to use the EvaluateExpression function instead.
Syntax:
Calculate (Expression, Grouping, CalculationType)
Parameters :
- Expression(Line). Contains a calculated expression;
- Grouping(Line). Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with the same name.
For instance:
Amount (Sales.Turnover Amount) / Calculate ("Amount (Sales.Turnover Amount)", "TotalTotal").
In this example, the result will be the ratio of the amount for the Sales.SumTurnover field of the grouping record to the amount of the same field in the entire layout. - Settlement Type(Line). If this parameter is set to "OverallTotal", the expression will be calculated for all grouping records. If the value of the parameter is "Grouping", the values will be calculated for the current group record of the grouping.
Syntax:
EvaluateExpression (Expression, Grouping, CalculationType, Start, End, Sort, HierarchicalSort, ProcessSimilarOrderValues)
Parameters :
- Expression(Line). Contains a calculated expression;
- Grouping(Line). Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with the same name;
- Settlement Type(Line). If this parameter is set to "OverallTotal", the expression will be calculated for all grouping records. If the value of the parameter is "Grouping", the values will be calculated for the current group record of the grouping. If the parameter is set to "GroupingNonResource", then when calculating the function for a group record by resources, the expression will be calculated for the first group record of the original grouping. When evaluating the Evaluate Expression function with a Non-Resource Grouping value for group records that are not resource groupings, the function is evaluated in the same way as it would with the Grouping parameter value. The data composition template composer, when generating the data composition template, when outputting to the template a field - a resource by which the grouping is performed, issues an expression calculated using the EvaluateExpression function with the "GroupingNoResource" parameter specified in the template. Regular resource expressions are returned for the rest of the resources grouped by resource. If the parameter is set to Hierarchy, then the expression must be evaluated for the parent hierarchical record, if there is one, and for the entire grouping, if there is no parent hierarchical record. The layout composer, when generating an expression for the% field in a hierarchy group, generates an expression containing the relationship of the resource expression to the EvaluateExpression function for the resource expression evaluated for the current grouping with the Hierarchy calculation type.
- Start... Indicates with which record to start the fragment, in which to calculate the aggregate functions of the expression and from which record to get the values of the fields outside the aggregate functions. A string containing one of:
- "First" ("First"). You need to get the first grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset from the beginning of the grouping. The resulting value must be an integer number greater than zero. For example, First (3) - getting the third record from the beginning of the grouping. If the first record is outside the grouping limits, then it is considered that there are no records. For example, if there are 3 records, and you want to get the First (4), then it is considered that there are no records.
- "Last" ("Last"). You need to get the latest grouping record. An expression can be specified after the word in parentheses, the result of which will be used as an offset from the end of the grouping. The resulting value must be an integer number greater than zero. For example, Last (3) - getting the third record from the end of the grouping. If the last record is outside the grouping limits, then it is considered that there are no records. For example, if there are 3 records, and you want to get the Last (4), then it is considered that there are no records.
- "Previous" ("Previous"). You need to get the previous grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset back from the current grouping record. For example, Previous (2) - get the previous one from the previous record. If the previous record goes beyond the grouping (for example, for the second grouping record you want to get Previous (3)), then the first grouping record is obtained. When getting the previous record for the grouping total, it is considered that the first record is obtained.
- "Next". You need to get the next grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset forward from the current grouping record. For example, Next (2) - get the next from the next record. If the next record goes out of the grouping, then it is considered that there are no records. For example, if there are 3 records and Next is received for the third record, then there are no records. When the next record is received for the grouping total, it is considered that there is no record.
- "Current". You need to get the current record. When retrieving for the grouping total, the first record is obtained.
- "BoundaryValue". The need to get a record at the specified value. After the word BoundedValue in parentheses, you need to specify an expression with the value of which you want to start the fragment, the first ordering field. As a record, the first record will be obtained, the value of the ordering field of which is greater than or equal to the specified value. For example, if the Period field is used as the ordering field, and it has the values 01/01/2010, 01/02/2010, 01/03/2010, and you want to get the BoundingValue (DateTime (2010, 1, 15)), then you will receive a record with the date 01.02. 2010.
- End... Indicates to which record to continue the chunk in which to evaluate the aggregate expression. A string containing one of:
- "First" ("First").
- "Last" ("Last").
- "Previous" ("Previous").
- "Next".
- "Current".
- "BoundaryValue".
- Sorting... A string that lists the expressions, separated by commas, in the direction in which you want to order the sequence. If not specified, the ordering is performed in the same way as for the grouping for which the expression is evaluated. After each expression, you can specify the keyword Ascending, for ascending order, Descending, for descending order, Auto-ordering, for ordering the referenced fields by the fields by which you want to order the referenced object. The word Auto-Arrange can be used with both Ascending and Descending.
- Hierarchical Sort... Similar to sorting. Used to arrange hierarchical records. If not specified, the layout composer generates an ordering according to the ordering specified in the Sort parameter.
- Processing of Identical ValuesOrder... A string containing one of:
- "Together" indicates that a sequence of ordered records is used to define the previous and next records;
- "Separately" - indicates that the previous and next records are determined based on the values of the ordering expressions;
1.January 01, 2001 Ivanov M. 10
2.January 02, 2001 Petrov S. 20
3.02 January 2001 Sidorov R. 30
4. 03 January 2001 Petrov S. 40
When using the processing of the same order values "Separately", the previous record to record 3 will be record 2, and when using "Together" - record 1. And the fragment for the current record to record 2 for "Separately" will be record 2, and for "Together" - records 2 and 3. Thus, the total for the current record for "Separately" will be 20, and for "Together" - 50. If "Together" is specified in the Start and End parameters, you cannot specify an offset for the positions "First", "Last", "Previous", "Next". The default is Separate.
Getting the ratio of the amount for the "Sales.SumTurnover" field of the grouping record to the amount of the same field in the entire layout:
Amount (Sales.SumTurnover) / CalculateExpression ("Amount (Sales.SumTurnover)", "TotalTotal").
This example calculates the value of the current hierarchy:
Choice
When Level ()> 0
Then EvaluateExpression ("Reference", "Hierarchy")
Otherwise Null
End
Notes:
The function takes into account the selection of groupings, but does not take into account hierarchical selections. The function cannot be applied to a grouping in the group selection of this grouping. For example, in the selection of the Nomenclature grouping, you cannot use the expression CalculateExpression ("Sum (SumTurnover)", "TotalTotal")> 1000. But such an expression can be used in hierarchical selection. If the end record precedes the start record, then it is considered that there are no records for calculating detailed data and calculating aggregate functions. When calculating interval expressions for a grand total (the Grouping parameter is set to "Total"), it is assumed that there are no records for calculating detailed data and calculating aggregate functions. The layout composer, when generating an expression for the EvaluateExpression function, if the ordering expression contains fields that cannot be used in the grouping, replaces the EvaluateExpression function with NULL.
3. EvaluateExpressionWithGroupArray (EvalExpressionWithGroupArray) - the function returns an array, each element of which contains the result of evaluating the expression for grouping by the specified field.
Syntax:
EvaluateGroupExpressionArray (Expression ,GroupFieldExpressions, RecordSelection, GroupSelection)
Parameters :
- Expression(String) - the expression to be evaluated. For example, "Amount (AmountTurnover)";
- Field Expressions
- Selection of Records
- Selection of Groups- selection applied to group records. For example: "Amount (AmountTurnover)> & Parameter1".
Maximum (CalculateExpressionWithGroupArray ("Sum (SumTurnover)", "Counterparty"));
The layout composer, when generating expressions for displaying a custom field, in the expression of which only the EvaluateArrayWithGrouping function is present, generates the displayed expression in such a way that the data to display the views and the data are ordered.
For example, for a custom field with an expression:
CalculateExpressionWithGroupingArray ("Sum (SumTurnover)", "Counterparty")
the layout linker will generate the following expression for output:
Connect Rows (Array (Arrange (EvaluateExpressionWith GroupingValuesTable ("View (Sum (Dataset.SumTurnover)), Sum (Dataset.SumTurnover)", "Dataset.Contractor"), "2")))
4. EvaluateExpressionWithGroupValueTable (EvalExpressionWithGroupValueTable) - the function returns a table of values, each element of which contains the result of evaluating the expression for grouping by the specified field.
Syntax:
EvaluateGroupExpressionValueTable (Expression, GroupField Expressions, RecordSelection, GroupSelection)
Parameters :
- Expression(String) - the expression to be evaluated. Several expressions can be listed in a line, separated by commas. Each expression can be followed by an optional AS keyword and the name of a table of values column. For example: "Counterparty, Amount (AmountTurnover) As Sales Volume".
- Field Expressions- expressions of grouping fields, separated by commas. For example, "Counterparty, Party";
- Selection of Records- an expression applied to detailed records. For example, "Deletion Mark = False". If an aggregate function is used in this parameter, an error will occur when performing data composition;
- Selection of Groups- selection applied to group records. For example: "Amount (AmountTurnover)> & Parameter1".
CalculateExpressionWithGroupValuesTable ("Counterparty AS Counterparty, Amount (AmountTurnover) As Sales Volume", "Counterparty")
The result of this function will be a table of values with the Counterparty and SalesVolume columns, in which counterparties with their sales volumes will be located.
The layout composer, when generating the layout, converts the function parameters to the terms of the data composition layout fields. For example, the Account field will be converted to Dataset.Contractor.
For example, a custom field with the expression:
CalculateExpressionWithGroupValuesTable ("Account, Amount (AmountTurnover)", "Account")
The layout composer will generate the following expression for output:
ConnectRows (GetPart (Arrange (EvaluateExpressionWith GroupingValuesTable ("Dataset.Contractor, Dataset.ContractorPresentation, Sum (Dataset.SumTurnover), Presentation (Dataset.TotalTurnover), Dataset.Field" OrderedSet. " ), "2, 4"))
5. Level - the function is designed to get the current recording level.
Syntax:
Level()
Example:
Level()
6. SequenceNumber - get the next serial number.
Syntax:
NumberOrder ()
Example:
NumberOrder ()
7. SequenceNumberInGrouping - returns the next sequential number in the current grouping.
Example:
NumberOrderInGrouping ()
8. Format - get the formatted string of the passed value.
Syntax:
Format (Value, FormatString)
Parameters :
- Meaning- the expression to be formatted;
- FormatString- the format string is set in accordance with the 1C: Enterprise format string.
Format (Invoices.SumDoc, "NPT = 2")
9. Beginning of Period (BeginOfPeriod)
Syntax:
Period Start (Date, Period Type)
Parameters :
- date(Date). Specified date;
- Period Type
Beginning of Period (DateTime (2002, 10, 12, 10, 15, 34), "Month")
Result: 01.10.2002 0:00:00
10. EndOfPeriod (EndOfPeriod) - the function is designed to extract a specific date from a given date.
Syntax:
EndPeriod (Date, PeriodType)
Parameters :
- date(Date). Specified date;
- Period Type(Line). Contains one of the values: Minute; Hour; Day; A week; Month; Quarter; Year; Decade; Half a year.
EndPeriod (DateTime (2002, 10, 12, 10, 15, 34), "Week")
Result: 10/13/2002 23:59:59
11.AddKDate (DateAdd) - the function is intended for adding a certain value to the date.
Syntax:
AddKDate (Expression, IncrementType, Value)
Parameters :
- Expression(Date). Original date;
- Magnification Type(Line). Contains one of the values: Minute; Hour; Day; A week; Month; Quarter; Year; Decade; Half a year.
- The magnitude(Number). By how much the date needs to be increased, the fractional part is ignored.
Add to Date (DateTime (2002, 10, 12, 10, 15, 34), "Month", 1)
Result: 12.11.2002 10:15:34
12.DifferenceDate (DateDifference) - the function is designed to get the difference between two dates.
Syntax:
Date Difference (Expression1, Expression2, DifferenceType)
Parameters :
- Expression1(Date). Subtracted date;
- Expression2(Date). Original date;
- TypeDifference(Line). Contains one of the values: Second; Minute; Hour; Day; Month; Quarter; Year.
DIFFERENCE (DATE TIME (2002, 10, 12, 10, 15, 34),
DATE (2002, 10, 14, 9, 18, 06), "DAY")
Result: 2
13. Substring - this function is designed to extract a substring from a string.
Syntax:
Substring (String, Position, Length)
Parameters :
- Line(Line). The string from which to extract the substring;
- Position(Number). The position of the character from which the substring to be extracted from the string begins;
- Length(Number). The length of the substring to select.
SUBSTRATE (Contractors.Address, 1, 4)
14.StringLength (StringLength) - the function is designed to determine the length of the string.
Syntax:
Line Length (String)
Parameter :
- Line(Line). The string to be specified in length.
String (Accounts.Address)
15. Year- this function is designed to extract a year from a value of the Date type.
Syntax:
Year (Date)
Parameter :
- date(Date). The date by which the year is determined.
YEAR (Invoice Date)
16. Quarter - this function is intended to extract a quarter number from a value of the Date type. The quarter number is normally in the range from 1 to 4.
Syntax:
Quarter (Date)
Parameter :
- date(Date). The date by which the quarter is determined
QUARTER (Consignment Date)
17. Month - this function is intended to extract the number of the month from the value of the Date type. The month number is normally in the range from 1 to 12.
Syntax:
Month (Date)
Parameter :
- date(Date). The date by which the month is determined.
MONTH (Invoice Date)
18. Day of the Year (DayOfYear) - this function is designed to get the day of the year from a value of the Date type. The day of the year is normally in the range from 1 to 365 (366).
Syntax:
Day of the Year (Date)
Parameter :
- date(Date). The date by which the day of the year is determined.
DAY OF THE YEAR (Invoice Date)
19. Day- this function is intended to get the day of the month from a value of the Date type. The day of the month is normally in the range from 1 to 31.
Syntax:
Day (Date)
Parameter :
- date(Date). The date by which the day of the month is determined.
DAY (Invoice Date)
20. Week - this function is intended to get the number of the week of the year from the value of the Date type. The weeks of the year are numbered starting at 1.
Syntax:
Week (Date)
Parameter :
- date(Date). The date by which the week numbers are determined.
WEEK (Invoice Date)
21. WeekDay - this function is intended to get the day of the week from a value of the Date type. The day of the week is normally between 1 (Monday) and 7 (Sunday).
Syntax:
Day of the Week (Date)
Parameter :
- date(Date). The date by which the day of the week is determined.
DAY OF THE WEEK (Invoice Date)
22. Hour- this function is designed to get the hour of the day from a value of the Date type. The hour of the day ranges from 0 to 23.
Syntax:
Hour (Date)
Parameter :
- date(Date). The date by which the hour of the day is determined.
HOUR (Consignment Date)
23. Minute - this function is intended to get the minute of the hour from the value of the Date type. The minute of the hour ranges from 0 to 59.
Syntax:
Minute (Date)
Parameter :
- date(Date). The date by which the minute of the hour is determined.
MINUTE (Invoice Date)
24. Second - this function is intended to get a second of a minute from a value of the Date type. The second of a minute ranges from 0 to 59.
Syntax:
Second (Date)
Parameter :
- date(Date). The date by which the seconds of the minute are determined.
SECOND (Invoice Date)
25. Cast - this function is intended to extract a type from an expression that can contain a complex type. If the expression contains a type other than the required type, a NULL value will be returned.
Syntax:
Express (Expression, Type Specification)
Parameters :
- Expression- the expression to be converted;
- Type Indication(Line). Contains a string of type. For example, "Number", "String", etc. In addition to primitive types, this string can contain the name of the table. In this case, an attempt will be made to express to a reference to the specified table.
Express (Data.Props1, "Number (10.3)")
26. IsNull (IsNull) - this function returns the value of the second parameter if the value of the first parameter is NULL. Otherwise, the value of the first parameter will be returned.
Syntax:
IsNull (Expression1, Expression2)
Parameters :
- Expression1- checked value;
- Expression2- the return value if Expression1 is NULL.
YesNULL (Amount (Sales.SumTurnover), 0)
27. ACos- calculates the arc cosine in radians.
Syntax:
ACos (Expression)
Parameter :
- Expression(Number). The cosine value (in the range -1 ... 1), by which the angle is determined.
Syntax:
ASin (Expression)
Parameter :
- Expression(Number). The sine value (in the range -1 ... 1), by which the angle is determined.
Syntax:
ATan (Expression)
Parameter :
- Expression(Number). The tangent value by which the angle is determined.
Syntax:
Cos (Expression)
Parameter :
- Expression
Syntax:
Exp (Expression)
Parameter :
- Expression(Number). The value of the degree.
Syntax:
Log (Expression)
Parameter :
- Expression
Syntax:
Log10 (Expression)
Parameter :
- Expression(Number). The original number is greater than 0.
Syntax:
Pow (Base, Indicator)
Parameters :
- Base(Number). The basis of the exponentiation operation.
- Indicator(Number). Exponent.
Syntax:
Sin (Expression)
Parameter :
- Expression(Number). Specified in radians.
Syntax:
Sqrt (Expression)
Parameter :
- Expression(Number). Non-negative number.
Syntax:
Tan (Expression)
Parameter :
- Expression(Number). The sine value by which the angle is determined.
Syntax:
Okr (Expression, Bit depth)
Parameters :
- Expression(Number). Original number;
- Bit depth(Number). The number of decimal places to round to.
Syntax:
Int (Expression)
Parameter :
- Expression(Number). Fractional number.
A data composition engine expression can contain calls to functions of global common configuration modules. No additional syntax is required to call such functions.
Example:
Abbreviated Name (Documents.Link, Documents.Date, Documents.Number)
In this example, the function "AbbreviatedName" will be called from the general configuration module.
Note that the use of the functions of common modules is allowed only when the corresponding parameter of the data composition processor is specified.
In addition, the functions of common modules cannot be used in custom field expressions.
41. Resentation - this function returns a string representation of the passed value of a non-primitive type. For values of primitive type, returns the value itself.
<Пустое значение>".
Example:
Submission (Counterparty)
42. String - this function converts the passed value into a string.
If an array or a table of values is used as a parameter, the function returns a string containing the string representation of all array elements, separated by ";" characters. If any element has an empty string representation, then the string "<Пустое значение>".
Example:
String (Date of Sale)
43. ValueIsFilled
For NULL values, Undefined always returns False.
For values, Boolean always returns True.
For other types, returns True if the value differs from the default for this type.
Example:
ValueFilled (Delivery Date)
44. LevelInGroup - this function gets the current recording level relative to the grouping.
It can be used to get the nesting level of a record in a hierarchical grouping.
Example:
LevelInGroup ()
45. ValueType
Syntax:
ValueType (Expression)
Parameter :
- Expression(Line). A string value of the type.
In light of the upcoming release of 8.2.14, I will try to describe some of the new functions of the data composition system.
Open the data composition schema, preferably in an external report, to make it easier to edit.
We add a dataset of the query type and write, either manually or using the query constructor, the simplest query:
1. Set up a request in the ACS.
2. Configuring the calculated fields in the ACS
3. We configure the data composition on the settings tab
4. Launching 1C Enterprise 8.2.14. We open the report. We form, we get.
Description of the new functions themselves:
1. The current date()
Returns the system date. When the layout of the layout is linked, in all expressions that are present in the layout, the CurrentDate () function is replaced with the value of the current date.
2. CALCULATE EXPRESSION ()
Syntax:
Evaluate Expression (<Выражение>, <Группировка>, <ОбластьВычисления>, <Начало>, <Конец>, <Сортировка>, <ИерархическаяСортировка>, <ОбработкаОдинаковыхЗначенийПорядка>)
Description:
The function is designed to evaluate an expression in the context of some grouping.
The function takes into account the selection of groupings, but does not take into account hierarchical selections.
The function cannot be applied to a grouping in the group selection of this grouping. For example, in the selection of the Nomenclature grouping, you cannot use the expression CalculateExpression ("Sum (SumTurnover)", "TotalTotal")> 1000. But such an expression can be used in a hierarchical selection.
If the end record precedes the start record, then it is considered that there are no records for calculating detailed data and calculating aggregate functions.
When calculating interval expressions for the grand total (the Grouping parameter is set to General Total), it is assumed that there are no records for calculating detailed data and calculating aggregate functions.
The layout composer, when generating an expression for the EvaluateExpression function, if the ordering expression contains fields that cannot be used in the grouping, replaces the EvaluateExpression function with NULL.
Parameters
<Выражение>
Type: String. The expression to evaluate.
<Группировка>
Type: String. Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with the same name.
For instance:
Amount (Sales.SumTurnover) / Calculate (“Amount (Sales.SumTurnover)”, “TotalTotal”)
In this example, the result will be the ratio of the amount for the Sales.SumTurnover field of the grouping record to the amount of the same field in the entire layout;
<ОбластьВычисления>
Type: String. The parameter can take the following values:
- GeneralTotal - the expression will be evaluated for all grouping records.
- Hierarchy - the expression will be evaluated for the parent hierarchical record, if there is one, and for the entire grouping, if there is no parent hierarchical record.
- Grouping - the expression will be evaluated for the current grouping grouping record.
- GroupingNonResource - when evaluating a function for a group record by resources, the expression will be calculated for the first group record of the original grouping.
When calculating the function EvaluateExpression () with the GroupNoResource value for group records that are not grouped by resource, the function is calculated in the same way as it would be calculated if the parameter value was equal to Grouping.
The composer of the data composition template, when generating the data composition template, when outputting the resource-field by which the grouping is performed to the template, places an expression calculated using the function EvaluateExpression (), specifying the GroupNoResource parameter. For the rest of the resources, the regular resource expressions are grouped by resource.
<Начало>
Type: String. Indicates with which record to start the fragment, in which to calculate the aggregate functions of the expression and from which record to get the values of the fields outside the aggregate functions. The value can be one of the following:
<Конец>
Type: String. Indicates to which record to continue the fragment in which to evaluate the aggregate functions of the expression. The value can be one of the following:
- First (First). You need to get the first grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset from the beginning of the grouping. The resulting value must be an integer number greater than zero. For example, First (3) - getting the third record from the beginning of the grouping.
If the first record is outside the grouping limits, then it is considered that there are no records. For example, if there are 3 records, and you want to get First (4), then it is considered that there are no records.
- Last (Last). You need to get the latest grouping record. An expression can be specified after the word in parentheses, the result of which will be used as an offset from the end of the grouping. The resulting value must be an integer number greater than zero. For example, Last (3) - getting the third record from the end of the grouping.
If the last record is outside the grouping limits, then it is considered that there are no records. For example, if there are 3 records, and you want to get the Last (4), then it is considered that there are no records.
- Previous. You need to get the previous grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset back from the current grouping record. For example, Previous (2) - get the previous one from the previous record.
If the previous record is outside the grouping (for example, for the second grouping record you want to get Previous (3), then the first grouping record is obtained.
When retrieving the previous record for the grouping total, the first record is considered to be obtained.
- Next (Next). You need to get the next grouping record. An expression can be specified after the word in brackets, the result of which will be used as an offset forward from the current grouping record. For example, Next (2) - get the next from the next record.
If the next record goes out of the grouping, then it is considered that there are no records. For example, if there are 3 records and Next () is received for the third record, then there are no records.
When the next record is received for the grouping total, it is considered that there is no record.
- Current (Current). You need to get the current record.
When retrieving for the grouping total, the first record is obtained.
- BoundaryValue. The need to get a record at the specified value. After the word BoundingValue in parentheses, you need to specify an expression with the value of which you want to start the fragment, the first ordering field.
As a record, the first record will be obtained, the value of the ordering field of which is greater than or equal to the specified value. For example, if the Period field is used as the ordering field, and it has the values 01/01/2010, 01/02/2010, 01/03/2010, and you want to get the BoundingValue (DateTime (2010, 1, 15)), then you will receive a record with the date 01.02. 2010.
<Сортировка>
Type: String. A comma-separated list of expressions that describe the collation rules. If not specified, the ordering is performed in the same way as for the grouping for which the expression is evaluated. After each expression, you can specify the keyword Ascending (for ascending order), Descending (for descending order), and Auto-ordering (for ordering the referenced fields by the fields by which you want to order the referenced object). The word Auto-Arrange can be used with both Ascending and Descending.
<ИерархическаяСортировка>
Type: String. Same as Sorting. Used to arrange hierarchical records. If not specified, the layout composer generates an ordering according to the ordering specified in the Sort parameter.
<ОбработкаОдинаковыхЗначенийПорядка>
Type: String. Specifies the rule for determining the previous or next record in case there are several records with the same ordering value:
- Separately means that a sequence of ordered records is used to define the previous and next records. Default value.
- Together means that the previous and next records are determined based on the values of the collation expressions.
For example, if the resulting sequence is ordered by date:
| № | date | Full name | Meaning |
| 1 | 01 January 2001 | Ivanov M. | 10 |
| 2 | 02 January 2001 | Petrov S. | 20 |
| 3 | 03 January 2001 | R. | 30 |
| 4 | 04 January 2001 | Petrov S. | 40 |
If the parameter value is Separate, then:
The previous entry to entry 3 will be entry 2.
§ if the calculated fragment is defined as Current, Current (respectively, the Start and End parameters), then for record 2 this fragment will consist of one record 2. Expression Calculate Expression (“Sum (Value)”, Current, Current) will be equal to 20.
If the parameter value is Together, then:
The previous entry to entry 3 will be entry 1.
§ if the calculated fragment is defined as Current, Current (respectively, the Start and End parameters), then for record 2 this fragment will consist of records 2 and 3. Expression Calculate Expression (“Sum (Value)”, Current, Current) will be equal to 50.
When specifying the parameter value equal to Together, in the Start and End parameters, you cannot specify an offset for the First, Last, Previous, Next positions.
Calculate Expression (“Sum (SumTurnover)”, “First”, “Current”)
If you want to get the grouping value in the previous line, you can use the following expression:
EvaluateExpression ("Course", "Previous")
List new functions:
EvaluateExpressionGroupedArray(<Выражение>, <ВыражениеПолейГруппировки>, <ОтборЗаписей>, <ОтборГруппировок>) –
The function returns an array, each element of which contains the result of evaluating the expression to be grouped by the specified field.
EvaluateExpressionGroupedValuesTable(<Выражения>, <ВыражениеПолейГруппировки>, <ОтборЗаписей>, <ОтборГруппировок>) –
The function returns a table of values, each row of which contains the result of evaluating expressions for grouping by the specified field
ValueFilled(<Выражение>) - Returns True if the value is different from the default value of the given type, other than NULL, other than an empty reference, other than Undefined. For Boolean values, a null check is performed. For strings, a check is made for the absence of non-whitespace characters
Format(<Выражение>, <Форматная строка>) - Get the formatted string of the passed value. The format string is set in accordance with the format string of the 1C: Enterprise system.
Substring(<Выражение>, <Начальные символ>, <ДлинаПодстроки>) - This function is designed to extract a substring from a string.
Line Length(<Выражение>) - The function is designed to determine the length of the string. Parameter is a string type expression
Line(<Выражение>) - If an array is passed as a parameter, the function returns a string containing string representations of all elements of the array, separated by symbols “; “. If a table of values is passed as a parameter, the function returns a string containing string representations of all rows of the table of values, and the cell representations of each row are separated by “; “, And strings - with a line feed character. If any element has an empty string representation, then the string is displayed instead of its representation<Пустое значение>.
Data Composition System Expression Language
The data composition system expression language is designed to write expressions used in various parts of the system.
Expressions are used in the following subsystems:
- data composition schema - for describing calculated fields, total fields, relationship expressions, etc.
- data composition settings - for describing custom field expressions;
- data composition template - for describing data set relationship expressions, describing template parameters, etc.
Literals
Expressions can contain literals. Literals of the following types are possible:
- Line;
- Number;
- Date;
- Boolean.
Line
A string literal is written in "” "characters, for example:
"String literal"
If you need to use the "" "character inside a string literal, you should use two such characters.
For instance:
“Literal“ “in quotes“ ““
Number
The number is written without spaces, in decimal format. The fractional part is separated by the symbol "." For instance:
10.5 200
date
The date literal is written using the DATETIME key literal. After this keyword, in brackets, separated by commas, the year, month, day, hours, minutes, seconds are listed. The time is optional.
For instance:
DATE TIME (1975, 1, 06) - January 6, 1975 DATE (2006, 12, 2, 23, 56, 57) - December 2, 2006, 23 hours 56 minutes 57 seconds, 23 hours 56 minutes 57 seconds
Boolean
Boolean values can be written using the literals True, False.
Meaning
To specify literals of other types (system enumerations, predefined data), the Value keyword is used, followed by the literal name in parentheses.
Value (Account Type. Active)
Operations on numbers
Unary -
This operation is intended to reverse the sign of a number. For instance:
Sales.Number
Unary +
This operation does not perform any action on the number. For instance:
Sales.Number
Binary -
This operation is designed to calculate the difference between two numbers. For instance:
Remains & Turnovers.Initial Balance - Remains & Turnovers.Final Balance Remains & Turnovers.Initial Balance - 100 400 - 357
Binary +
This operation is designed to calculate the sum of two numbers. For instance:
Balances & Turnovers.Initial Balance + Balances & Turnovers.Turnover Balances & Turnovers.Initial Balance + 100 400 + 357
Work
This operation is designed to calculate the product of two numbers. For instance:
Nomenclature.Price * 1.2 2 * 3.14
Division
This operation is intended to obtain the result of dividing one operand by another. For instance:
Nomenclature.Price / 1.2 2 / 3.14
Remainder of the division
This operation is intended to obtain the remainder of dividing one operand by another. For instance:
Nomenclature Price% 1.2 2% 3.14
String operations
Concatenation (Binary +)
This operation is intended to concatenate two strings. For instance:
Nomenclature.Article + “:” + Nomenclature.Name
Like
This operation checks if the string matches the passed pattern.
The value of the LIKE operator is TRUE if the value<Выражения>matches the pattern and FALSE otherwise.
The following characters in<Строке_шаблона>make sense other than just the next line character:
- % - percentage: a sequence containing zero or more arbitrary characters;
- _ - underscore: one arbitrary character;
- […] - one or more characters in square brackets: one character, any of those listed inside square brackets. The enumeration may contain ranges, for example, a-z, meaning an arbitrary character included in the range, including the ends of the range;
- [^…] - in square brackets a negation icon followed by one or more characters: any character except those listed after the negation icon;
Any other symbol means itself and does not carry any additional meaning. If it is necessary to write one of the listed characters as oneself, then it must be preceded by<Спецсимвол>specified after the ESCAPE keyword.
For example, the template
"% ABC [abvg] \ _ abc%" SPECIAL SYMBOL "\"
means a substring consisting of a sequence of characters: the letter A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore character; letters a; letters b; letters c. Moreover, this sequence can be located starting from an arbitrary position in the line.
Comparison operations
Equals
This operation is intended to compare two operands for equality. For instance:
Sales.Contractor = Sales.NomenclatureMainSupplier
Not equal
This operation is intended to compare two operands for inequality. For instance:
Sales, contractor<>Sales.NomenclatureMainSupplier
Less
This operation is intended to check that the first operand is less than the second. For instance:
SalesCurrent.Amount< ПродажиПрошлые.Сумма
More
This operation is intended to check that the first operand is greater than the second. For instance:
SalesCurrent.Total> SalesPast.Amount
Less or equal
This operation is intended to check that the first operand is less than or equal to the second. For instance:
SalesCurrent.Amount<= ПродажиПрошлые.Сумма
More or equal
This operation is intended to check that the first operand is greater than or equal to the second. For instance:
SalesCurrent.Amount> = SalesPast.Amount
Operation B
This operation checks for the presence of a value in the passed list of values. The result of the operation will be True if the value is found, or False otherwise. For instance:
Item B (& Item1, & Item2)
The operation of checking the existence of a value in a dataset
The operation checks for the existence of a value in the specified data set. The validation dataset must contain one field. For instance:
Sales. Counterparty To Counterparties
Operation of checking a value for NULL
This operation returns True if the value is NULL. For instance:
Sales.Contractor IS NULL
The operation of checking a value for NULL inequality
This operation returns True if the value is not NULL. For instance:
Sales.Contractor IS NOT NULL
Logical operations
Logical operations take Boolean expressions as operands.
Operation NOT
The operation does NOT return True if its operand is False, and False if its operand is True. For instance:
NOT Document.Consignee = Document.Shipper
Operation AND
The AND operator returns True if both operands are True, and False if one of the operands is False. For instance:
Document.Consignee = Document.Shipper AND Document.Consignee = & Counterparty
OR operation
The OR operation returns True if one of the operands is True, and False if both operands are False. For instance:
Document.Consignee = Document.Carrier OR Document.Consignee = & Counterparty
Aggregate functions
Aggregate functions perform some action on a dataset.
Sum
The Sum aggregate function calculates the sum of the expression values passed to it as an argument for all detail records. For instance:
Amount (Sales.SumTurnover)
Quantity
The Count function calculates the number of non-null values. For instance:
Quantity (Sales. Contractor)
Number of different
This function calculates the number of distinct values. For instance:
Quantity (Various Sales. Contractor)
Maximum
The function gets the maximum value. For instance:
Maximum (Balances.Quantity)
Minimum
The function gets the minimum value. For instance:
Minimum (Balances.Quantity)
The average
The function gets the mean of non-NULL values. For instance:
Average (Balances.Amount)
Other operations
Operation SELECT
Operation Select is designed to select one of several values when certain conditions are met. For instance:
Choice When Amount> 1000 Then Amount Else 0 End
Rules for comparing two values
If the types of the compared values differ from each other, then the relationship between the values is determined based on the priority of the types:
- NULL (lowest);
- Boolean;
- Number;
- Date;
- Line;
- Reference types
The relationships between the various reference types are determined based on the reference numbers of the tables corresponding to a particular type.
If the data types match, then the values are compared according to the following rules:
- Boolean type has TRUE value greater than FALSE value;
- the Number type has the usual comparison rules for numbers;
- the Date type has earlier dates less than later ones;
- for the String type - string comparisons in accordance with the established national characteristics of the database;
- reference types are compared based on their values (record numbers, etc.).
Working with NULL values
Any operation in which the value of one of the operands is NULL will result in NULL.
There are exceptions:
- the AND operation will return NULL only if none of the operands is False;
- the OR operation will return NULL only if none of the operands is True.
Operation Priorities
Operations have the following priorities (the first line has the lowest priority):
- B IS NULL IS NOT NULL;
- =, <>, <=, <, >=, >;
- Binary +, Binary -;
- *, /, %;
- Unary +, Unary -.
Expression Language Functions of the Data Composition System
Calculate
The Calculate function is designed to evaluate an expression in the context of some grouping. The function has the following parameters:
- Expression. Type String. Contains a calculated expression;
- Grouping. Type String. Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with the same name. For instance:
In this example, the result will be the ratio of the amount for the Sales.SumTurnover field of the grouping record to the amount of the same field in the entire layout.
Level
The function is intended to get the current recording level.
Level()
NumberOrder
Get the next sequence number.
NumberOrder ()
NumberOrderInGroup
Returns the next sequential number in the current grouping.
NumberOrderInGrouping ()
Format
Get the formatted string of the passed value.
The format string is set in accordance with the 1C: Enterprise format string.
Parameters:
- Meaning;
- Format string.
Format (Invoices.SumDoc, "NPT = 2")
Beginning of period
Parameters:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Beginning of Period (DateTime (2002, 10, 12, 10, 15, 34), "Month")
Result:
01.10.2002 0:00:00
End of Period
The function is designed to extract a specific date from a given date.
Parameters:
- Date. Type Date. Specified date;
- Period type. Type String. Contains one of the values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
EndPeriod (DateTime (2002, 10, 12, 10, 15, 34), "Week")
Result:
13.10.2002 23:59:59
Add to Date
The function is intended for adding a certain value to the date.
Parameters:
- Magnification type. Type String. Contains one of the values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
- Value - by how much the date should be increased. Type Number. The fractional part is ignored.
Add to Date (DateTime (2002, 10, 12, 10, 15, 34), "Month", 1)
Result:
12.11.2002 10:15:34
Date Difference
The function is designed to get the difference between two dates.
Parameters:
- Expression. Type Date. Original date;
- Expression. Type Date. Subtracted date;
- Difference type. Type String. Contains one of the values:
- Second;
- Minute;
- Day;
- Month;
- Quarter;
DIFFERENCE (DATE TIME (2002, 10, 12, 10, 15, 34), DATE TIME (2002, 10, 14, 9, 18, 06), "DAY")
Result:
Substring
This function is designed to extract a substring from a string.
Parameters:
- Line. Type String. The string from which to extract the substring;
- Position. Type Number. The position of the character from which the substring to be extracted from the string begins;
- Length. Type Number. The length of the substring to select.
SUBSTRATE (Contractors.Address, 1, 4)
Line Length
The function is designed to determine the length of a string.
Parameter:
- Line. Type String. The string to be specified in length.
String (Accounts.Address)
Year
This function is designed to extract a year from a value of the Date type.
Parameter:
- Date. Type Date. The date by which the year is determined.
YEAR (Invoice Date)
Quarter
This function is intended to extract a quarter number from a value of the Date type. The quarter number is normally in the range from 1 to 4.
Parameter
- Date. Type Date. The date by which the quarter is determined
Month
This function is intended to extract the number of the month from a value of the Date type. The month number is normally in the range from 1 to 12.
- Date. Type Date. The date by which the month is determined.
Day of the Year
This function is designed to get the day of the year from a value of the Date type. The day of the year is normally in the range from 1 to 365 (366).
- Date. Type Date. The date by which the day of the year is determined.
Day
This function is intended to get the day of the month from a value of the Date type. The day of the month is normally in the range from 1 to 31.
- Date. Type Date. The date by which the day of the month is determined.
A week
This function is intended to get the number of the week of the year from a value of the Date type. The weeks of the year are numbered starting at 1.
- Date. Type Date. The date by which the week numbers are determined.
Day of the week
This function is intended to get the day of the week from a value of the Date type. The day of the week is normally between 1 (Monday) and 7 (Sunday).
- Date. Type Date. The date by which the day of the week is determined.
Hour
This function is designed to get the hour of the day from a value of the Date type. The hour of the day ranges from 0 to 23.
- Date. Type Date. The date by which the hour of the day is determined.
Minute
This function is intended to get the minute of an hour from a value of the Date type. The minute of the hour ranges from 0 to 59.
- Date. Type Date. The date by which the minute of the hour is determined.
Second
This function is intended to get a second of a minute from a value of the Date type. The second of a minute ranges from 0 to 59.
- Date. Type Date. The date by which the seconds of the minute are determined.
Express
This function is intended to extract a type from an expression that can contain a complex type. If the expression contains a type other than the required type, a NULL value will be returned.
Parameters:
- The expression to be converted;
- Type indication. Type String. Contains a string of type. For example, "Number", "String", etc. In addition to primitive types, this string can contain the name of the table. In this case, an attempt will be made to express to a reference to the specified table.
Express (Data.Props1, "Number (10.3)")
YesNull
This function returns the value of the second parameter if the value of the first parameter is NULL.
Otherwise, the value of the first parameter will be returned.
YesNULL (Amount (Sales.SumTurnover), 0)
Common module functions
A data composition engine expression can contain calls to functions of global common configuration modules. No additional syntax is required to call such functions.
In this example, the function "AbbreviatedName" will be called from the general configuration module.
Note that the use of the functions of common modules is allowed only when the corresponding parameter of the data composition processor is specified.
In addition, the functions of common modules cannot be used in custom field expressions.
Content
1 Expression language of the data composition system
2 Literals
2.1 String
2.2 Number
2.3 Date
2.4 Boolean
2.5 Value
3 Operations on numbers
3.1 Unary -
3.2 Unary +
3.3 Binary -
3.4 Binary +
3.5 Artwork
3.6 Division
3.7 Remainder
4 Operations on strings
4.1 Concatenation (Binary +)
4.2 Similar
5 Comparison operations
5.1 Equal
5.2 Not equal
5.3 Less
5.4 More
5.5 Less than or equal
5.6 Greater than or equal
5.7 Step B
5.8 Operation of checking the existence of a value in a dataset
5.9 Operation of checking a value for NULL
5.10 Operation of checking a value for NULL inequality
6 Logical operations
6.1 Operation NOT
6.2 Operation AND
6.3 OR operation
7 Aggregate functions
7.1 Amount
7.2 Quantity
7.3 Number of different
7.4 Maximum
7.5 Minimum
7.6 Average
8 Other operations
8.1 Operation SELECT
9 Rules for comparing two values
10 Working with NULL
11 Priorities of operations
12 Expression language functions of the data composition system
12.1 Calculate
12.2 Level
12.3 Number By Order
12.4 NumberOrderInGroup
12.5 Format
12.6 Beginning of Period
12.7 End of Period
12.8 Add to Date
12.9 Date Difference
12.10 Substring
12.11 Line Length
12.12 Year
12.13 Quarter
12.14 Month
12.15 Day of the Year
12.16 Day
12.17 Week
12.18 Day of the Week
12.19 Hour
12.20 Minute
12.21 Second
12.22 Express
12.23 YesNull
12.24 Functions of common modules
Data Composition System Expression Language
The data composition system expression language is designed to write expressions used in various parts of the system.
Expressions are used in the following subsystems:
- data composition schema - for describing calculated fields, total fields, relationship expressions, etc.
- data composition settings - for describing custom field expressions;
- data composition template - for describing data set relationship expressions, describing template parameters, etc.
Literals
Expressions can contain literals. Literals of the following types are possible:
- Line;
- Number;
- Date;
- Boolean.
Line
A string literal is written in "” "characters, for example:
"String literal"
If you need to use the "" "character inside a string literal, you should use two such characters.
For instance:
“Literal“ “in quotes“ ““
Number
The number is written without spaces, in decimal format. The fractional part is separated by the symbol "." For instance:
date
The date literal is written using the DATETIME key literal. After this keyword, in brackets, separated by commas, the year, month, day, hours, minutes, seconds are listed. The time is optional.
For instance:
DATE (1975, 1, 06) - January 6, 1975
DATE TIME (2006, 12, 2, 23, 56, 57) - December 2, 2006, 23 hours 56 minutes 57 seconds, 23 hours 56 minutes 57 seconds
Boolean
Boolean values can be written using the literals True, False.
Meaning
To specify literals of other types (system enumerations, predefined data), the Value keyword is used, followed by the literal name in parentheses.
Value (Account Type. Active)
Operations on numbers
Unary -
This operation is intended to reverse the sign of a number. For instance:
-Sales.Quantity
Unary +
This operation does not perform any action on the number. For instance:
+ Sales.Quantity
Binary -
This operation is designed to calculate the difference between two numbers. For instance:
Balances & Turnovers.Initial Balance - Balances & Turnovers.Final Balance
Balances AND Turnovers. Initial Balance - 100
400 – 357
Binary +
This operation is designed to calculate the sum of two numbers. For instance:
Balances & Turnovers.Initial Balance + Balances & Turnovers.Turnover
Balances AND Turnovers. Initial Balance + 100
400 + 357
Work
This operation is designed to calculate the product of two numbers. For instance:
Nomenclature.Price * 1.2
2 * 3.14
Division
This operation is intended to obtain the result of dividing one operand by another. For instance:
Nomenclature.Price / 1.2
2 / 3.14
Remainder of the division
This operation is intended to obtain the remainder of dividing one operand by another. For instance:
Nomenclature.Price% 1.2
2 % 3.14
String operations
Concatenation (Binary +)
This operation is intended to concatenate two strings. For instance:
Nomenclature.Article + “:” + Nomenclature.Name
Like
This operation checks if the string matches the passed pattern.
The value of the LIKE operator is TRUE if the value<Выражения>matches the pattern and FALSE otherwise.
The following characters in<Строке_шаблона>make sense other than just the next line character:
- % - percentage: a sequence containing zero or more arbitrary characters;
- _ - underscore: one arbitrary character;
- […] - one or more characters in square brackets: one character, any of those listed inside square brackets. The enumeration may contain ranges, for example, a-z, meaning an arbitrary character included in the range, including the ends of the range;
- [^…] - in square brackets a negation icon followed by one or more characters: any character except those listed after the negation icon;
Any other symbol means itself and does not carry any additional meaning. If it is necessary to write one of the listed characters as oneself, then it must be preceded by<Спецсимвол>specified after the ESCAPE keyword.
For example, the template
"% ABC [abvg] \ _ abc%" SPECIAL SYMBOL "\"
means a substring consisting of a sequence of characters: the letter A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore character; letters a; letters b; letters c. Moreover, this sequence can be located starting from an arbitrary position in the line.
Comparison operations
Equals
This operation is intended to compare two operands for equality. For instance:
Sales.Contractor = Sales.NomenclatureMainSupplier
Not equal
This operation is intended to compare two operands for inequality. For instance:
Sales.Contractor Sales.NomenclatureMainSupplier
Less
This operation is intended to check that the first operand is less than the second. For instance:
SalesCurrent.Amount
More
This operation is intended to check that the first operand is greater than the second. For instance:
SalesCurrent.Total> SalesPast.Amount
Less or equal
This operation is intended to check that the first operand is less than or equal to the second. For instance:
SalesCurrent.Amount
More or equal
This operation is intended to check that the first operand is greater than or equal to the second. For instance:
SalesCurrent.Amount> = SalesPast.Amount
Operation B
This operation checks for the presence of a value in the passed list of values. The result of the operation will be True if the value is found, or False otherwise. For instance:
Item B (& Item1, & Item2)
The operation of checking the existence of a value in a dataset
The operation checks for the existence of a value in the specified data set. The validation dataset must contain one field. For instance:
Sales. Counterparty To Counterparties
Operation of checking a value for NULL
This operation returns True if the value is NULL. For instance:
Sales.Contractor IS NULL
The operation of checking a value for NULL inequality
This operation returns True if the value is not NULL. For instance:
Sales.Contractor IS NOT NULL
Logical operations
Logical operations take Boolean expressions as operands.
Operation NOT
The operation does NOT return True if its operand is False, and False if its operand is True. For instance:
NOT Document.Consignee = Document.Shipper
Operation AND
The AND operator returns True if both operands are True, and False if one of the operands is False. For instance:
Document.Consignee = Document.Shipper AND Document.Consignee = & Counterparty
OR operation
The OR operation returns True if one of the operands is True, and False if both operands are False. For instance:
Document.Consignee = Document.Carrier OR Document.Consignee = & Counterparty
Aggregate functions
Aggregate functions perform some action on a dataset.
Sum
The Sum aggregate function calculates the sum of the expression values passed to it as an argument for all detail records. For instance:
Amount (Sales.SumTurnover)
Quantity
The Count function calculates the number of non-null values. For instance:
Quantity (Sales. Contractor)
Number of different
This function calculates the number of distinct values. For instance:
Quantity (Various Sales. Contractor)
Maximum
The function gets the maximum value. For instance:
Maximum (Balances.Quantity)
Minimum
The function gets the minimum value. For instance:
Minimum (Balances.Quantity)
The average
The function gets the mean of non-NULL values. For instance:
Average (Balances.Amount)
Other operations
Operation SELECT
Operation Select is designed to select one of several values when certain conditions are met. For instance:
Choice When Amount> 1000 Then Amount Else 0 End
Rules for comparing two values
If the types of the compared values differ from each other, then the relationship between the values is determined based on the priority of the types:
NULL (lowest);
Boolean;
Number;
Date;
Line;
Reference types
The relationships between the various reference types are determined based on the reference numbers of the tables corresponding to a particular type.
If the data types match, then the values are compared according to the following rules:
Boolean type has TRUE value greater than FALSE value;
the Number type has the usual comparison rules for numbers;
the Date type has earlier dates less than later ones;
for the String type - string comparisons in accordance with the established national characteristics of the database;
reference types are compared based on their values (record numbers, etc.).
Working with NULL values
Any operation in which the value of one of the operands is NULL will result in NULL.
There are exceptions:
the AND operation will return NULL only if none of the operands is False;
the OR operation will return NULL only if none of the operands is True.
Operation Priorities
Operations have the following priorities (the first line has the lowest priority):
OR;
AND;
NOT;
B IS NULL IS NOT NULL;
=, <>, <=, <, >=, >;
Binary +, Binary -;
*, /, %;
Unary +, Unary -.
Expression Language Functions of the Data Composition System
Calculate
The Calculate function is designed to evaluate an expression in the context of some grouping. The function has the following parameters:
Expression. Type String. Contains a calculated expression;
Grouping. Type String. Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with the same name. For instance:
Amount (Sales.SumTurnover) / Calculate ("Amount (Sales.SumTurnover)", "TotalTotal")
In this example, the result will be the ratio of the amount for the Sales.SumTurnover field of the grouping record to the amount of the same field in the entire layout.
Level
The function is intended to get the current recording level.
Example:
Level()
NumberOrder
Get the next sequence number.
Example:
NumberOrder ()
NumberOrderInGroup
Returns the next sequential number in the current grouping.
Example:
NumberOrderInGrouping ()
Format
Get the formatted string of the passed value.
The format string is set in accordance with the 1C: Enterprise format string.
Parameters:
Meaning;
Format string.
Example:
Format (Invoices.SumDoc, "NPT = 2")
Beginning of period
Parameters:
-
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Example:
Beginning of Period (DateTime (2002, 10, 12, 10, 15, 34), "Month")
Result:
01.10.2002 0:00:00
End of Period
The function is designed to extract a specific date from a given date.
Parameters:
- Date. Type Date. Specified date;
- Period type. Type String. Contains one of the values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Example:
EndPeriod (DateTime (2002, 10, 12, 10, 15, 34), "Week")
Result:
13.10.2002 23:59:59
Add to Date
The function is intended for adding a certain value to the date.
Parameters:
- Magnification type. Type String. Contains one of the values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Example:
Add to Date (DateTime (2002, 10, 12, 10, 15, 34), "Month", 1)
Result:
12.11.2002 10:15:34
Date Difference
The function is designed to get the difference between two dates.
Parameters:
- Expression. Type Date. Original date;
- Expression. Type Date. Subtracted date;
- Difference type. Type String. Contains one of the values:
- Second;
- Minute;
- Day;
- Month;
- Quarter;
Example:
DIFFERENCE (DATE TIME (2002, 10, 12, 10, 15, 34),
DATE (2002, 10, 14, 9, 18, 06), "DAY")
Result:
Substring
This function is designed to extract a substring from a string.
Parameters:
- Line. Type String. The string from which to extract the substring;
- Position. Type Number. The position of the character from which the substring to be extracted from the string begins;
- Length. Type Number. The length of the substring to select.
Example:
SUBSTRATE (Contractors.Address, 1, 4)
Line Length
The function is designed to determine the length of a string.
Parameter:
Line. Type String. The string to be specified in length.
Example:
String (Accounts.Address)
This function is designed to extract a year from a value of the Date type.
Parameter:
Date. Type Date. The date by which the year is determined.
YEAR (Invoice Date)
Quarter
This function is intended to extract a quarter number from a value of the Date type. The quarter number is normally in the range from 1 to 4.
Parameter
Date. Type Date. The date by which the quarter is determined
QUARTER (Consignment Date)
Month
This function is intended to extract the number of the month from a value of the Date type. The month number is normally in the range from 1 to 12.
Date. Type Date. The date by which the month is determined.
MONTH (Invoice Date)
Day of the Year
This function is designed to get the day of the year from a value of the Date type. The day of the year is normally in the range from 1 to 365 (366).
Date. Type Date. The date by which the day of the year is determined.
DAY OF THE YEAR (Invoice Date)
Day
This function is intended to get the day of the month from a value of the Date type. The day of the month is normally in the range from 1 to 31.
Date. Type Date. The date by which the day of the month is determined.
DAY (Invoice Date)
A week
This function is intended to get the number of the week of the year from a value of the Date type. The weeks of the year are numbered starting at 1.
Date. Type Date. The date by which the week numbers are determined.
WEEK (Invoice Date)
Day of the week
This function is intended to get the day of the week from a value of the Date type. The day of the week is normally between 1 (Monday) and 7 (Sunday).
Date. Type Date. The date by which the day of the week is determined.
DAY OF THE WEEK (Invoice Date)
This function is designed to get the hour of the day from a value of the Date type. The hour of the day ranges from 0 to 23.
Date. Type Date. The date by which the hour of the day is determined.
HOUR (Consignment Date)
Minute
This function is intended to get the minute of an hour from a value of the Date type. The minute of the hour ranges from 0 to 59.
Date. Type Date. The date by which the minute of the hour is determined.
MINUTE (Invoice Date)
Second
This function is intended to get a second of a minute from a value of the Date type. The second of a minute ranges from 0 to 59.
Date. Type Date. The date by which the seconds of the minute are determined.
SECOND (Invoice Date)
Express
This function is intended to extract a type from an expression that can contain a complex type. If the expression contains a type other than the required type, a NULL value will be returned.
Parameters:
The expression to be converted;
Type indication. Type String. Contains a string of type. For example, "Number", "String", etc. In addition to primitive types, this string can contain the name of the table. In this case, an attempt will be made to express to a reference to the specified table.
Example:
Express (Data.Props1, "Number (10.3)")
YesNull
This function returns the value of the second parameter if the value of the first parameter is NULL.
Otherwise, the value of the first parameter will be returned.
Example:
YesNULL (Amount (Sales.SumTurnover), 0)
Common module functions
A data composition engine expression can contain calls to functions of global common configuration modules. No additional syntax is required to call such functions.
Example:
Abbreviated Name (Documents.Link, Documents.Date, Documents.Number)
In this example, the function "AbbreviatedName" will be called from the general configuration module.
Note that the use of the functions of common modules is allowed only when the corresponding parameter of the data composition processor is specified.
In addition, the functions of common modules cannot be used in custom field expressions.
[you must register to view the link]
