In this short note, I want to show how you can sum up values at different grouping levels in a report using a data composition system.
As shown in the image, only at the "Item groups" grouping level, the "Order" resource is calculated, it displays how much to order for the current item group based on certain conditions:
This value can only be calculated at this grouping level, because, above or below, there are no values to calculate. For example, at the level of detailed records, there is no data on the maximum number in a group, because these data are only true for the group as a whole, and not for its individual components.
Accordingly, now it is necessary to calculate the totals for the higher groupings (“Warehouses”, “Warehouse Types”) and the total.
For this, the function is used Calculate Expression With Grouped Array:
EVALUATE ARRAY GROUPING EXPRESSION (EVALEXPRESSIONWITHGROUPARRAY)
Syntax:
ComputeExpressionGroupedArray(,)
Description:
The function returns an array, each element of which contains the result of evaluating the expression for grouping by the specified field.
The layout builder, when generating a layout, converts function parameters into terms of data layout layout fields. For example, the Account field will be converted to Dataset. Account.
The layout builder, when generating expressions for displaying a custom field whose expression contains only the CalculateArrayWithGroupArray() function, generates the output expression in such a way that the output information is ordered. For example, for a custom field with an expression:
Calculate ExpressionWith GroupingArray("Amount(AmountTurnover)", "Counterparty")
The layout builder will generate the following expression for output:
ConnectStrings(Array(Order(CalculateGroup ExpressionValueTable("View(Amount(DataSet.AmountTurnover)),Amount(DataSet.AmountTurnover)","DataSet.Counterparty"),"2")))
Options:
Type: String. The expression to be evaluated. A string, for example, Amount(AmountTurnover).
Type: String. Grouping field expressions – grouping field expressions separated by commas. For example, Contractor, Party.
Type: String. An expression that describes the filter applied to detail records. Aggregate functions are not supported in an expression. For example,DeletionMark = False.
Type: String. An expression that describes the filter applied to group records. For example, Amount(AmountTurnover) > &Parameter1.
Example:
Maximum(Calculate ExpressionWith GroupingArray("Amount(AmountTurnover)", "Counterparty"));
A detailed description of the function syntax can be found at http://its.1c.ru/db/v837doc#bookmark:dev:TI000000582
Now for the calculation, we will duplicate the “Order” field, with different values of “Calculate by ...”, using the following expressions, note that in each higher level, the values of the levels of the lower groupings are used.
As a result, we get the following construction:
Content
1 Data composition system expression language
2 Literals
2.1 Line
2.2 Number
2.3 Date
2.4 Boolean
2.5 Meaning
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 Equals
5.2 Not equal
5.3 Less
5.4 More
5.5 Less than or equal
5.6 Greater than or equal
5.7 Operation B
5.8 The operation of checking the presence of a value in a data set
5.9 NULL test operation
5.10 NULL test operation
6 Boolean 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 Operational priorities
12 Data composition system expression language functions
12.1 Calculate
12.2 Level
12.3 Sequence Number
12.4 NumberInOrderInGroup
12.5 Format
12.6 StartPeriod
12.7 End of Period
12.8 AddDate
12.9 DifferenceDate
12.10 Substring
12.11 StringLength
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 General module functions
Data Composition System Expression Language
The expression language of the data composition system is designed to record expressions used in various parts of the system.
Expressions are used in the following subsystems:
- data composition schema - for describing calculated fields, total fields, link expressions, etc.;
- data composition settings - to describe custom field expressions;
- data composition layout - for describing dataset relation expressions, describing layout parameters, etc.
Literals
The expression can contain literals. Possible literals of the following types:
- Line;
- Number;
- Date of;
- Boolean.
Line
A string literal is written in the characters """, for example:
"String literal"
If you need to use the character """ inside the string literal, you should use two such characters.
For example:
“literal ““in quotes“““
Number
The number is written without spaces, in decimal format. The fractional part is separated by the "." character. For example:
date
A date type 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. Time indication is not required.
For example:
DATETIME(1975, 1, 06) - January 6, 1975
DATETIME(2006, 12, 2, 23, 56, 57) - December 2, 2006, 23:56:57, 23:56:57
boolean
Boolean values can be written using literals True (True), False (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(AccountType. Active)
Operations on numbers
Unary -
This operation is designed to reverse the sign of a number. For example:
-Sales.Quantity
Unary +
This operation does not perform any operations on the number. For example:
+Sales.Quantity
Binary -
This operation is designed to calculate the difference of two numbers. For example:
RemainsAndTurnovers.InitialRemainder – RemainsAndTurnovers.EndRemaining
RemainsAnd Turnovers.InitialRemainder - 100
400 – 357
Binary +
This operation is designed to calculate the sum of two numbers. For example:
Balances AND Turnovers. Initial Balance + Balances AND Turnovers. Turnover
RemainsAnd Turnovers.InitialRemainder + 100
400 + 357
Work
This operation is designed to calculate the product of two numbers. For example:
Nomenclature.Price * 1.2
2 * 3.14
Division
This operation is designed to obtain the result of dividing one operand by another. For example:
Nomenclature.Price / 1.2
2 / 3.14
Remainder of the division
This operation is designed to obtain the remainder of the division of one operand by another. For example:
Nomenclature.Price % 1.2
2 % 3.14
Operations on strings
Concatenation (Binary +)
This operation is designed to concatenate two strings. For example:
Nomenclature.Article + “: ”+ Nomenclature.Name
Like
This operation checks if a string matches the given pattern.
The value of the LIKE operator is TRUE if the value<Выражения>matches the pattern, and FALSE otherwise.
The following characters in<Строке_шаблона>have a meaning other than just another character in the string:
- % - percentage: a sequence containing zero or more arbitrary characters;
- _ - underscore: one arbitrary character;
- […] - one or more characters in square brackets: one character, any of the ones listed inside the square brackets. An enumeration can contain ranges, such as a-z, meaning any character within the range, including the ends of the range;
- [^…] - in square brackets a negation icon followed by one or more characters: any character other than those listed after the negation icon;
Any other symbol means itself and does not carry any additional load. If it is necessary to write one of the listed characters as itself, then it must be preceded by<Спецсимвол>The specified after the ESCAPE keyword.
For example, template
“%ABC[abc]\_abc%” SPECIAL CHARACTER “\”
means a substring consisting of a sequence of characters: letters A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore character; letters a; letters b; letters in. Moreover, this sequence can be located starting from an arbitrary position in the string.
Comparison operations
Equals
This operation is designed to compare two operands for equality. For example:
Sales.Counterparty = Sales.NomenclatureMainSupplier
Not equal
This operation is designed to compare two operands for inequality. For example:
Sales.Counterparty Sales.NomenclatureMainSupplier
Less
This operation is designed to check that the first operand is less than the second. For example:
SalesCurrent.Amount
More
This operation is designed to check that the first operand is greater than the second. For example:
SalesCurrent.Amount > SalesPast.Amount
Less or equal
This operation is designed to check that the first operand is less than or equal to the second. For example:
SalesCurrent.Amount
More or equal
This operation is designed to check that the first operand is greater than or equal to the second. For example:
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 is True if the value is found, or False otherwise. For example:
Item B (&Item1, &Item2)
Operation to check if a value exists in a dataset
The operation checks for the presence of a value in the specified data set. The validation dataset must contain one field. For example:
Sales. Contractor To Contractors
NULL check operation
This operation returns True if the value is NULL. For example:
Sales.Contractor IS NULL
The operation of checking a value for non-NULL
This operation returns True if the value is not NULL. For example:
Sales.Contractor IS NOT NULL
Boolean operations
Logical operations accept as operands expressions of type Boolean.
Operation NOT
The NOT operator returns True if its operand is False, and returns False if its operand is True. For example:
NOT Document.Consignee = Document.Consignor
Operation AND
The AND operation returns True if both operands are True, and returns False if one of the operands is False. For example:
Document.Consignee = Document.Consignor AND Document.Consignee = &Contractor
OR operation
The OR operation returns True if one of the operands is True, and False if both operands are False. For example:
Document.Consignee = Document.Consignor OR Document.Consignee = &Contractor
Aggregate functions
Aggregate functions perform some action on a set of data.
Sum
The Sum aggregate function calculates the sum of the values of the expressions passed to it as an argument for all detail records. For example:
Amount(Sales.AmountTurnover)
Quantity
The Count function counts the number of non-null values. For example:
Quantity(Sales.Contractor)
Number of different
This function counts the number of distinct values. For example:
Quantity(Various Sales.Contractor)
Maximum
The function gets the maximum value. For example:
Maximum(Remainders. Quantity)
Minimum
The function gets the minimum value. For example:
Minimum(Balance. Quantity)
Average
The function gets the mean for non-null values. For example:
Average(Remainders. Quantity)
Other operations
Operation SELECT
The Select operation is designed to select one of several values under certain conditions. For example:
Select When Sum > 1000 Then Sum 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 precedence of the types:
NULL (lowest);
boolean;
Number;
Date of;
Line;
Reference types
Relationships between different reference types are defined based on the reference numbers of tables corresponding to a particular type.
If the data types match, then the values are compared according to the following rules:
type Boolean TRUE is greater than FALSE;
type Number has the usual comparison rules for numbers;
for the Date type, earlier dates are less than later ones;
for the String type - comparisons of strings in accordance with the established national features of the database;
reference types are compared based on their values (record number, etc.).
Working with NULL
Any operation in which the value of one of the operands is NULL will produce a NULL result.
There are exceptions:
the AND operation will only return NULL 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 -.
Data Composition System Expression Language Functions
Calculate
The Calculate function is designed to calculate 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 whose context 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 string GrandTotal 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 that name. For example:
Amount(Sales.AmountTurnover) / Calculate("Amount(Sales.AmountTurnover)", "TotalTotal")
In this example, the result will be the ratio of the amount in the "Sales.AmountTurnover" field of the grouping record to the amount of the same field in the entire layout.
Level
The function is designed to get the current recording level.
Example:
Level()
NumberInOrder
Get the next sequence number.
Example:
OrderNumber()
NumberIn OrderInGrouping
Returns the next sequence number in the current grouping.
Example:
NumberInOrderInGroup()
Format
Get the formatted string of the passed value.
The format string is set in accordance with the 1C:Enterprise format string.
Options:
Meaning;
Format string.
Example:
Format(Expenditure Invoices.AmountDoc, "NPV=2")
Beginning of period
Options:
-
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Example:
PeriodStart(DateTime(2002, 10, 12, 10, 15, 34), "Month")
Result:
01.10.2002 0:00:00
EndPeriod
The function is designed to extract a specific date from a given date.
Options:
- Date of. Type Date. Given date;
- Period type. Type String. Contains one of the following 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
AddDate
The function is designed to add some value to the date.
Options:
- Magnification type. Type String. Contains one of the following values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
Example:
AddToDate(DateTime(2002, 10, 12, 10, 15, 34), "Month", 1)
Result:
12.11.2002 10:15:34
DifferenceDate
The function is designed to get the difference between two dates.
Options:
- Expression. Type Date. original date;
- Expression. Type Date. Subtracted date;
- Difference type. Type String. Contains one of the following values:
- Second;
- Minute;
- Day;
- Month;
- Quarter;
Example:
DATE DIFFERENCE(DATETIME(2002, 10, 12, 10, 15, 34),
DATETIME(2002, 10, 14, 9, 18, 06), "DAY")
Result:
Substring
This function is designed to select a substring from a string.
Options:
- Line. Type String. The string from which the substring is extracted;
- 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 selected substring.
Example:
SUBSTRING(Accounts.Address, 1, 4)
String Length
The function is designed to determine the length of a string.
Parameter:
Line. Type String. The string whose length is being determined.
Example:
String(Accounts.Address)
This function is designed to extract the year from a value of the Date type.
Parameter:
Date of. Type Date. The date by which the year is determined.
YEAR(Inc.Date)
Quarter
This function is designed to extract the number of the quarter from the value of the Date type. The block number normally ranges from 1 to 4.
Parameter
Date of. Type Date. The date by which the quarter is determined
QUARTER(Invoice.Date)
Month
This function is designed 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 of. 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 Date type value. The day of the year normally ranges from 1 to 365(366).
Date of. Type Date. The date by which the day of the year is determined.
DAY OF THE YEAR(Exp.Inc.Date)
Day
This function is designed to get the day of the month from a value of the Date type. The day of the month normally ranges from 1 to 31.
Date of. Type Date. The date by which the days of the month are determined.
DAY(Exp.Inc.Date)
A week
This function is designed 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 from 1.
Date of. Type Date. The date by which week numbers are determined.
WEEK(Exp.Inc.Date)
Day of the Week
This function is designed to get the day of the week from a value of the Date type. The day of the week normally ranges from 1 (Monday) to 7 (Sunday).
Date of. Type Date. The date by which the day of the week is determined.
DAY OF THE WEEK(Exp.Inc.Date)
This function is designed to get the hour of the day from a Date type value. The hour of the day ranges from 0 to 23.
Date of. Type Date. The date by which the hour of the day is determined.
HOUR(Exp.Inc.Date)
Minute
This function is designed to get the minute of the hour from a Date type value. The minute of the hour ranges from 0 to 59.
Date of. Type Date. The date by which the minute of the hour is determined.
MINUTE(Exp.Inc.Date)
Second
This function is designed to get the second of a minute from a Date type value. The second of a minute ranges from 0 to 59.
Date of. Type Date. The date by which the seconds of the minute are determined.
SEC(Exp.Inc.Date)
express
This function is designed to extract a type from an expression that may contain a composite type. If the expression contains a type different from the required type, NULL will be returned.
Options:
The expression to be converted;
Type indication. Type String. Contains a type string. For example, "Number", "String", etc. In addition to primitive types, this string may contain the name of a table. In this case, an attempt will be made to express to a reference to the specified table.
Example:
Express(Data.Attribute1, "Number(10,3)")
Null
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:
IsNULL(Amount(Sales.AmountTurnover), 0)
Common Module Functions
The data composition engine expression can contain function calls to 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 "ShortName" function will be called from the general configuration module.
Note that the use of shared module functions is only allowed when the corresponding data composition processor option is specified.
Also, common module functions cannot be used in custom field expressions.
[you need to register to view the link]
Data Composition System Expression Language
The expression language of the data composition system is designed to record expressions used in various parts of the system.
Expressions are used in the following subsystems:
- data composition schema - for describing calculated fields, total fields, link expressions, etc.;
- data composition settings - to describe custom field expressions;
- data composition layout - for describing dataset relation expressions, describing layout parameters, etc.
Literals
The expression can contain literals. Possible literals of the following types:
- Line;
- Number;
- Date of;
- Boolean.
Line
A string literal is written in the characters """, for example:
"String literal"
If you need to use the character """ inside the string literal, you should use two such characters.
For example:
“literal ““in quotes“““
Number
The number is written without spaces, in decimal format. The fractional part is separated by the "." character. For example:
10.5 200
date
A date type 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. Time indication is not required.
For example:
DATETIME(1975, 1, 06) - January 6, 1975 DATETIME(2006, 12, 2, 23, 56, 57) - December 2, 2006, 23:56:57 seconds, 23:56:57 seconds
boolean
Boolean values can be written using literals True (True), False (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(AccountType. Active)
Operations on numbers
Unary -
This operation is designed to reverse the sign of a number. For example:
Sales.Quantity
Unary +
This operation does not perform any operations on the number. For example:
Sales.Quantity
Binary -
This operation is designed to calculate the difference of two numbers. For example:
BalancesAndTurnovers.InitialBalance - BalancesAndTurnovers.EndBalance BalancesAndTurnovers.InitialBalance - 100 400 - 357
Binary +
This operation is designed to calculate the sum of two numbers. For example:
BalancesAndTurnovers.InitialBalance + BalancesAndTurnovers.Turnover BalancesAndTurnovers.InitialBalance + 100 400 + 357
Work
This operation is designed to calculate the product of two numbers. For example:
Nomenclature. Price * 1.2 2 * 3.14
Division
This operation is designed to obtain the result of dividing one operand by another. For example:
Nomenclature. Price / 1.2 2 / 3.14
Remainder of the division
This operation is designed to obtain the remainder of the division of one operand by another. For example:
Nomenclature. Price % 1.2 2 % 3.14
Operations on strings
Concatenation (Binary +)
This operation is designed to concatenate two strings. For example:
Nomenclature.Article + “:”+ Nomenclature.Name
Like
This operation checks if a string matches the given pattern.
The value of the LIKE operator is TRUE if the value<Выражения>matches the pattern, and FALSE otherwise.
The following characters in<Строке_шаблона>have a meaning other than just another character in the string:
- % - percentage: a sequence containing zero or more arbitrary characters;
- _ - underscore: one arbitrary character;
- […] - one or more characters in square brackets: one character, any of the ones listed inside the square brackets. An enumeration can contain ranges, such as a-z, meaning any character within the range, including the ends of the range;
- [^…] - in square brackets a negation icon followed by one or more characters: any character other than those listed after the negation icon;
Any other symbol means itself and does not carry any additional load. If it is necessary to write one of the listed characters as itself, then it must be preceded by<Спецсимвол>The specified after the ESCAPE keyword.
For example, template
“%ABC[abc]\_abc%” SPECIAL CHARACTER “\”
means a substring consisting of a sequence of characters: letters A; letters B; letters B; one digit; one of the letters a, b, c or d; underscore character; letters a; letters b; letters in. Moreover, this sequence can be located starting from an arbitrary position in the string.
Comparison operations
Equals
This operation is designed to compare two operands for equality. For example:
Sales.Counterparty = Sales.NomenclatureMainSupplier
Not equal
This operation is designed to compare two operands for inequality. For example:
Sales.Contractor<>Sales.NomenclatureMainSupplier
Less
This operation is designed to check that the first operand is less than the second. For example:
SalesCurrent.Amount< ПродажиПрошлые.Сумма
More
This operation is designed to check that the first operand is greater than the second. For example:
SalesCurrent.Amount > SalesPast.Amount
Less or equal
This operation is designed to check that the first operand is less than or equal to the second. For example:
SalesCurrent.Amount<= ПродажиПрошлые.Сумма
More or equal
This operation is designed to check that the first operand is greater than or equal to the second. For example:
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 is True if the value is found, or False otherwise. For example:
Item B (&Item1, &Item2)
Operation to check if a value exists in a dataset
The operation checks for the presence of a value in the specified data set. The validation dataset must contain one field. For example:
Sales. Contractor To Contractors
NULL check operation
This operation returns True if the value is NULL. For example:
Sales.Contractor IS NULL
The operation of checking a value for non-NULL
This operation returns True if the value is not NULL. For example:
Sales.Contractor IS NOT NULL
Boolean operations
Logical operations accept as operands expressions of type Boolean.
Operation NOT
The NOT operator returns True if its operand is False, and returns False if its operand is True. For example:
NOT Document.Consignee = Document.Consignor
Operation AND
The AND operation returns True if both operands are True, and returns False if one of the operands is False. For example:
Document.Consignee = Document.Consignor AND Document.Consignee = &Contractor
OR operation
The OR operation returns True if one of the operands is True, and False if both operands are False. For example:
Document.Consignee = Document.Consignor OR Document.Consignee = &Contractor
Aggregate functions
Aggregate functions perform some action on a set of data.
Sum
The Sum aggregate function calculates the sum of the values of the expressions passed to it as an argument for all detail records. For example:
Amount(Sales.AmountTurnover)
Quantity
The Count function counts the number of non-null values. For example:
Quantity(Sales.Contractor)
Number of different
This function counts the number of distinct values. For example:
Quantity(Various Sales.Contractor)
Maximum
The function gets the maximum value. For example:
Maximum(Remainders. Quantity)
Minimum
The function gets the minimum value. For example:
Minimum(Balance. Quantity)
Average
The function gets the mean for non-null values. For example:
Average(Remainders. Quantity)
Other operations
Operation SELECT
The Select operation is designed to select one of several values under certain conditions. For example:
Select When Sum > 1000 Then Sum 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 precedence of the types:
- NULL (lowest);
- boolean;
- Number;
- Date of;
- Line;
- Reference types
Relationships between different reference types are defined based on the reference numbers of tables corresponding to a particular type.
If the data types match, then the values are compared according to the following rules:
- type Boolean TRUE is greater than FALSE;
- type Number has the usual comparison rules for numbers;
- for the Date type, earlier dates are less than later ones;
- for the String type - comparisons of strings in accordance with the established national features of the database;
- reference types are compared based on their values (record number, etc.).
Working with NULL
Any operation in which the value of one of the operands is NULL will produce a NULL result.
There are exceptions:
- the AND operation will only return NULL 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 -.
Data Composition System Expression Language Functions
Calculate
The Calculate function is designed to calculate 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 whose context 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 string GrandTotal 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 that name. For example:
In this example, the result will be the ratio of the amount in the "Sales.AmountTurnover" field of the grouping record to the amount of the same field in the entire layout.
Level
The function is designed to get the current recording level.
Level()
NumberInOrder
Get the next sequence number.
OrderNumber()
NumberIn OrderInGrouping
Returns the next sequence number in the current grouping.
NumberInOrderInGroup()
Format
Get the formatted string of the passed value.
The format string is set in accordance with the 1C:Enterprise format string.
Options:
- Meaning;
- Format string.
Format(Expenditure Invoices.AmountDoc, "NPV=2")
Beginning of period
Options:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
PeriodStart(DateTime(2002, 10, 12, 10, 15, 34), "Month")
Result:
01.10.2002 0:00:00
EndPeriod
The function is designed to extract a specific date from a given date.
Options:
- Date of. Type Date. Given date;
- Period type. Type String. Contains one of the following 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
AddDate
The function is designed to add some value to the date.
Options:
- Magnification type. Type String. Contains one of the following values:
- Minute;
- Day;
- A week;
- Month;
- Quarter;
- Decade;
- Half a year.
- Value - by how much you want to increase the date. Type Number. The fractional part is ignored.
AddToDate(DateTime(2002, 10, 12, 10, 15, 34), "Month", 1)
Result:
12.11.2002 10:15:34
DifferenceDate
The function is designed to get the difference between two dates.
Options:
- Expression. Type Date. original date;
- Expression. Type Date. Subtracted date;
- Difference type. Type String. Contains one of the following values:
- Second;
- Minute;
- Day;
- Month;
- Quarter;
DATEDIFF(DATETIME(2002, 10, 12, 10, 15, 34), DATETIME(2002, 10, 14, 9, 18, 06), "DAY")
Result:
Substring
This function is designed to select a substring from a string.
Options:
- Line. Type String. The string from which the substring is extracted;
- 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 selected substring.
SUBSTRING(Accounts.Address, 1, 4)
String Length
The function is designed to determine the length of a string.
Parameter:
- Line. Type String. The string whose length is being determined.
String(Accounts.Address)
Year
This function is designed to extract the year from a value of the Date type.
Parameter:
- Date of. Type Date. The date by which the year is determined.
YEAR(Inc.Date)
Quarter
This function is designed to extract the number of the quarter from the value of the Date type. The block number normally ranges from 1 to 4.
Parameter
- Date of. Type Date. The date by which the quarter is determined
Month
This function is designed 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 of. 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 Date type value. The day of the year normally ranges from 1 to 365(366).
- Date of. Type Date. The date by which the day of the year is determined.
Day
This function is designed to get the day of the month from a value of the Date type. The day of the month normally ranges from 1 to 31.
- Date of. Type Date. The date by which the days of the month are determined.
A week
This function is designed 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 from 1.
- Date of. Type Date. The date by which week numbers are determined.
Day of the Week
This function is designed to get the day of the week from a value of the Date type. The day of the week normally ranges from 1 (Monday) to 7 (Sunday).
- Date of. 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 Date type value. The hour of the day ranges from 0 to 23.
- Date of. Type Date. The date by which the hour of the day is determined.
Minute
This function is designed to get the minute of the hour from a Date type value. The minute of the hour ranges from 0 to 59.
- Date of. Type Date. The date by which the minute of the hour is determined.
Second
This function is designed to get the second of a minute from a Date type value. The second of a minute ranges from 0 to 59.
- Date of. Type Date. The date by which the seconds of the minute are determined.
express
This function is designed to extract a type from an expression that may contain a composite type. If the expression contains a type different from the required type, NULL will be returned.
Options:
- The expression to be converted;
- Type indication. Type String. Contains a type string. For example, "Number", "String", etc. In addition to primitive types, this string may contain the name of a table. In this case, an attempt will be made to express to a reference to the specified table.
Express(Data.Attribute1, "Number(10,3)")
Null
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.
IsNULL(Amount(Sales.AmountTurnover), 0)
Common Module Functions
The data composition engine expression can contain function calls to global common configuration modules. No additional syntax is required to call such functions.
In this example, the "ShortName" function will be called from the general configuration module.
Note that the use of shared module functions is only allowed when the corresponding data composition processor option is specified.
Also, common module functions cannot be used in custom field expressions.
One of the most important areas of business software is reporting. How easy it is to customize an existing report to the changing needs of business (and legislation) or make a new one may depend (and not in a figurative sense!) The fate of the business, whether it is a report for the tax office or a diagram of the dependence of demand for goods on the season and other factors . A powerful and flexible reporting system that allows you to easily extract the right data from the system, present it in an understandable form, allowing the end user to reconfigure the standard report to see the data in a new light - this is the ideal that every business system should strive for.
In the 1C:Enterprise platform, a mechanism called the Data Composition System (ACS for short) is responsible for generating reports. In this article, we will try to give a brief description of the idea and architecture of the ACS mechanism and its capabilities.
ACS is a mechanism based on the declarative description of reports. ACS is designed to build reports and display information that has a complex structure. By the way, in addition to developing reports, the ACS mechanism is also used in 1C:Enterprise in a dynamic list, a tool for displaying list information with rich functionality (displaying flat and hierarchical lists, conditional row formatting, grouping, etc.).
A bit of history
In the very first version of the 1C:Enterprise 8 platform, version 8.0, reports were made as follows:- One or more queries were written in the 1C query language (SQL-like language, more about it below).
- Code was written that transferred the results of executed queries to a spreadsheet document or chart. The code could also do work that cannot be done in a query - for example, it calculated values using the built-in 1C language.
One of the ideas that formed the basis of the ACS was the flexibility and customization of reports, which is available to both the developer and the end user. Ideally, we would like to give the end user access to the same set of report design tools as the developer. It would be logical to make a single set of tools available to everyone. Well, since the tools involve the participation of the end user, it means that the use of programming in them should be removed to a minimum (best of all, completely eliminated), and visual settings should be used to the maximum.
Formulation of the problem
The task before the development team was this - to make a reporting system based not on an algorithmic (ie, through writing code), but on a declarative approach to creating reports. And we believe that the problem has been successfully solved. In our experience, about 80% of the required reporting can be implemented using ACS without a single line of code (except for writing formulas for calculated fields), for the most part - through visual settings.The development of the first version of the ACS took about 5 man-years.
Two languages
Two languages are involved in creating reports. One is the query language used to fetch the data. The second is the data composition expression language, designed to write expressions used in various parts of the system, for example, in data composition settings, to describe custom field expressions.Query Language
The query language is based on SQL and is easily mastered by those who know SQL. Request example:
It is easy to see analogues of standard SQL query sections - SELECT, FROM, GROUP BY, ORDER BY.
At the same time, the query language contains a significant number of extensions aimed at reflecting the specifics of financial and economic tasks and at the maximum reduction in efforts to develop applied solutions:
- Referencing fields through a dot. If the fields of any table are of a reference type (they store links to objects of another table), the developer can refer to them in the query text through ".", while the number of nesting levels of such links is not limited by the system (for example, Customer Order.Agreement.Organization. Telephone).
- Multidimensional and multilevel formation of results. Totals and subtotals are formed taking into account grouping and hierarchy, levels can be bypassed in an arbitrary order with summing up subtotals, the correct construction of totals by time dimensions is ensured.
- Support for virtual tables. The virtual tables provided by the system allow you to get almost ready-made data for most application tasks without the need to write complex queries. Thus, a virtual table can provide data on the balance of goods in the context of periods at a certain point in time. At the same time, virtual tables make the most of the stored information, for example, previously calculated totals, etc.
- Temporary tables. The query language allows you to use temporary tables in queries. With their help, you can improve query performance, in some cases reduce the number of locks, and make the query text easier to read.
- batch requests. For more convenient work with temporary tables, the query language supports working with batch queries - thus, the creation of a temporary table and its use are placed in one query. A batch request is a sequence of requests separated by a semicolon (";"). The requests in the batch are executed one after the other. The result of executing a batch query, depending on the method used, will be either the result returned by the last query of the batch, or an array of the results of all the batch requests in the order in which the requests in the batch follow.
- Get reference field views. Each object table (in which a directory or document is stored) has a virtual field - "Representation". This field contains a textual representation of the object and makes the work of the reporter easier. So, for a document, this field contains all the key information - the name of the document type, its number and date (for example, "Sale 000000003 from 07/06/2017 17:49:14"), saving the developer from writing a calculated field.
- and etc.
There are also special query language extensions for SKD. The expansion is carried out using special syntax instructions enclosed in curly braces and placed directly in the request text. With the help of extensions, the developer determines what operations the end user will be able to perform by customizing the report.
For example:
- CHOOSE. This sentence describes the fields that the user will be able to select for output. After this keyword, aliases of fields from the main query selection list, which will be available for customization, are listed separated by commas. Example: (SELECT Nomenclature, Warehouse)
- WHERE. Describes the fields on which the user can apply selection. This proposal uses table fields. The use of select list field aliases is not allowed. Each union part can contain its own WHERE element. Examples: (WHERE Nomenclature.*, Warehouse ), (WHERE Document.Date >= &StartDate, Document.Date<= &ДатаКонца}
- and etc.
Data Composition Expression Language
The data composition expression language is designed to write expressions used, in particular, to describe custom field expressions. ACS allows you to define custom fields in a report using either your own expressions or sets of options with conditions for their selection (analogous to CASE in SQL). Custom fields are analogous to calculated fields. They can be set both in the configurator and in 1C:Enterprise mode, but functions of common modules cannot be used in custom field expressions. Therefore, custom fields are more for the user than for the developer.Example: 
The process of creating a report on the ACS
When creating a report, we need to create a layout that defines how the data will be displayed in the report. You can create a layout based on a data composition schema. The data composition schema describes the essence of the data that is provided to the report (where to get the data from and how you can control its composition). The data composition scheme is the basis on which all kinds of reports can be generated. The data composition scheme may contain:- request text with data composition system instructions;
- description of several data sets;
- a detailed description of the available fields;
- description of relationships between several data sets;
- description of data acquisition parameters;
- description of field layouts and groupings;
- and etc.
For example, you can add a query to the data composition scheme as a data set, and call the query constructor, which allows you to graphically compose a query of arbitrary complexity:
The result of running the query builder will be the text of the query (in the 1C:Enterprise query language). This text can be manually adjusted if necessary:
There can be several data sets in a data composition scheme, data sets can be linked in a layout in an arbitrary way, calculated fields can be added, report parameters can be set, etc. It is worth mentioning an interesting feature of the query mechanism in 1C:Enterprise. The queries are eventually translated into a dialect of SQL specific to the DBMS that the application is directly working with. In general, we try to use the capabilities of DBMS servers to the maximum (we are limited by the fact that we use only those capabilities that are simultaneously available in all DBMS supported by the 1C:Enterprise platform - MS SQL, Oracle, IBM DB2, PostgreSQL). Thus, at the query level in calculated fields, we can use only those functions that are translated into SQL.
But at the data composition scheme level, we can already add custom fields and use functions in them in the built-in 1C development language (including those written by us), which greatly expands the capabilities of reports. Technically, it looks like this - everything that can be translated into SQL is translated into SQL, the query is executed at the DBMS level, the query results are placed in the memory of the 1C application server, and the ACS calculates the values of calculated fields for each record, whose formulas are written in 1C language.
Adding Custom Fields
You can add any number of tables and charts to the report:
Report Designer
Report at run time
With the help of ACS, the user can add complex selections to the report (which will be added to the query in the right places), conditional design (allowing you to format differently - font, color, etc. - output fields depending on their values) and much more .
Briefly describe the process of building and generating a report as follows:
- The developer at design time with the help of the designer (or at runtime with the help of code) defines the data layout scheme:
- Text of request/requests
- Description of calculated fields
- Relationships between queries (if there are more than one)
- Report Options
- Default settings
- Etc.
- The above settings are saved in the layout
- User opens a report
- Possibly makes additional settings (for example, changes parameter values)
- Presses the "Generate" button
- User settings apply to the data composition scheme defined by the developer.
- An intermediate layout of the data composition is formed, containing instructions on where to get the data from. In particular, queries specified in the layout are corrected. So, fields that are not used in the report are removed from the request (this is done in order to minimize the amount of data received). All fields that are used in calculated field formulas are added to the query.
- The data composition processor is included in the case. The layout processor executes queries, links data sets, calculates the values of calculated fields and resources, performs grouping. In a word, it does all the calculations that were not performed at the DBMS level.
- The data output processor launches a query for execution and outputs the received data to a spreadsheet document, chart, etc.
The process of generating a report by the ACS mechanism
We try to minimize the amount of reporting data transferred from the server to the client application. When displaying data in a spreadsheet document, when opening a spreadsheet document, we transfer from the server only those lines that the user sees at the beginning of the document. As the user moves along the lines of the document, the missing data is downloaded from the server to the client.
User settings
All ACS tools are available to both the developer and the end user. But practice has shown that the end user is often frightened by the abundance of tool capabilities. Moreover, in most cases, the end user does not need all the power of settings - it is enough for him to have quick access to setting one or two report parameters (for example, period and counterparty). Starting with a certain version of the platform, the report developer has the opportunity to mark which report settings are available to the user. This is done using the "Include in user settings" checkbox. Also, the report settings now have a "Display mode" flag that takes one of three values:- Fast access. The setting will be displayed directly at the top of the report window.
- Ordinary. The setting will be available through the "Settings" button.
- Not available. The setting will not be available to the end user.
Setting display mode in design time
Display the setting in Quick Access mode at runtime (under the Generate button)
Development plans
One of the priorities in the development of ACS for us is the simplification of user settings. Our experience shows that for some end users, working with user settings is still a serious job. We take this into account and work in this direction. Accordingly, it will also become easier for developers to work with ACS, because we, as before, want to provide a single toolkit for setting up reports for both the developer and the end user.Greetings, my dear readers! Let's continue with the basics of the layout system. In this series of articles, you learned the differences between a dataset - a query vs. a regular query. You also looked at the parameters of virtual tables and got acquainted with the extension of the query language. Now take a look at the content that you will learn here. Good luck!
Functions of the SKD expression language.
When a query is built in the layout, the regular functions of the query language are used inside it. But ACS allows you to define additional functions. Some functions are similar to those in the query language, but use a different syntax. For example, the "BeginPeriod" function:
PeriodStart(DateTime(2002, 10, 12, 10, 15, 34), "Month")
This function differs from the query language function in the second parameter. Here the string "Month" is used, whereas in the query language function the literal MONTH is used.
Description of functions can be found by opening the Configurator: Help -> Contents -> Data Composition System -> Data Composition System Expression Language -> Data Composition System Expression Language Functions.
Where can I use the functions of the SKD expression language if they cannot be used in the query language?
- On a tab in the Expression column.
- On the "Parameters" tab in the "Expression" column.
- On the "Settings" tab in the "Custom fields" column. The user can create his own fields and write expressions to them.
- On the Datasets tab, in the View Expressions and Ordering Expressions columns.
These are expressions that set up data composition fields.
Features of the SKD.
When using a dataset query, there are a number of features that appear
when optimizing the SKD.
- The resulting query is reduced according to the report variant setting,
because some fields are removed in the resulting query. - In addition, tables can also be deleted from the resulting query if temporary tables are described in the query and they are not used anywhere else.
- The same applies to data sets, if we have described many data sets in the schema, but use only some of them, then the system will receive information only on the data sets used, and the rest of the data sets will be removed from the resulting data composition layout.
Can these features have any negative consequences? Yes, since in some cases the request that we wanted will not be formed. Therefore, you need to take these features into account when building complex queries.
Basic settings for the ACS fields.
On the “Data sets” tab, when creating a data set of the query type, the layout fields are auto-filled. As you can see, the fields contain a large number of settings.
Column "Field" contains the name of the field, which is obtained from the infobase (IB).
An immutable value that is hardwired to the request. It matches the request alias.
But there is one feature. If we change the alias, for example, "Nomenclature", in the Query Builder on the "Combinations / Aliases" tab in the "Field Name" column, to "Product", then a new field "Product" will appear on the "Datasets" tab of the layout, but at the same time the "Nomenclature" field remains. This is because the "AutoComplete" flag is set. With this setting, the system cannot understand that the "Product" field is the same as the "Nomenclature" field, so, just in case, the field is added twice.
Could such a design harm the user experience? Yes. After all, two fields “Product” and “Nomenclature” will appear in the settings of the report variant. It will not be clear to the user how they differ, although these selections are identical.
Although you can exclude the appearance of the "Nomenclature" field by checking the boxes "Field restriction" - "Condition" and "Attribute restriction" - "Condition" on the "Data sets" tab in the field settings. In addition, you can uncheck the "Autocomplete" flag and fill in all the fields in the Query Builder on the "Data Composition" tab.
Column "Path" specifies how we will refer to the field on all tabs of the data composition. A value that you can change to your liking. Spaces are allowed in the path name, but such a name is enclosed in square brackets. But it is better that the path be without spaces, since this is a service field. It is better to change the title by checking the box in the "Title" column.
Why was it necessary to create two different fields "Field" and "Path", if their names are almost always the same? Because many datasets are used. For example, if the second dataset is going to be a union, then in order to match the fields you need to have the same paths. In other cases, the paths can be left unchanged if their names are clear.
You can use the following method to work with the "Path" column. By specifying a path, you can create nested virtual element attributes. For example, let's place the "Quality" attribute inside the "Product" attribute. Let's change the "Path" field of the "Quality" attribute to "Product.Quality". Now on the “Settings” tab in the “Selected fields” tab, we will not find the “Quality” field, but it will appear inside the “Product” field.
Let's take a closer look at the accessibility restriction settings. As you can see, there are two lines to restrict accessibility in columns "Field Limit" And "Restriction of props".
Columns limiting the SKD fields
The "Field Restriction" column means that the restrictions will be imposed on the field itself. The column "Restriction of details" means that restrictions will be imposed on the nested details of this field, if the field belongs to a complex data type, such as a lookup or document.
Each line is divided into four more columns. If a flag is set for a field or attributes of this field:
- Field, then they will not be available for selection on the "Settings" tab in the "Selected fields" tab.
- Condition, then they will not be available as a condition on the "Settings" tab in the "Selection" tab.
- Group, then grouping by them will not be available on the "Settings" tab when creating a new grouping.
- ordering, then they will not be available for ordering on the "Settings" tab in the "Sort" tab.
Column "View Expression" needed to change the representation of the field as a result of the report. For example, you need to display not only the name of the product, but also its article. Therefore, in this column you can write the following:
Product. Article + "-" + Goods. Name
In this field, we can use all the possibilities of the ACS language and access the functions of common modules.
If the configuration contains a common module called "ForSKD" and this module defines a common (with the keyword "Export") function "GetView" with the parameter "Reference", then in the field "View Expression" we will write:
For SKD. GetRepresentation(Item)
But you need to use this feature carefully, especially if the function of the common module accesses the database, since this access occurs after all the data for the report has been received. In fact, for each row, the query will be executed again.
Column "Order Expression" needed to define a custom sort. Here you can use the same constructions as in the "View Expression" field.
Well, he told everything, as promised. If you have questions, then write them in the comments. So that the material is fixed in your mind, I will prepare a test.
