In this small note, I want to show how you can summarize values at different levels of grouping in a report using the data composition system.
As shown in the image, only at the "Item groups" grouping level, the "Order" resource is calculated, it displays how much you need to order for the current item group based on certain conditions:
This value can only be calculated at this grouping level, since, above or below, there are no values for the calculation. For example, at the level of detailed records, there is no data on the maximum number in a group, because this data is valid only for the group as a whole, and not for its individual components.
Accordingly, now it is necessary to calculate the totals for the above groupings ("Warehouses", "Types of Warehouses") and the grand total.
To do this, use the function EvaluateGroupExpressionArray:
CALCULATE EXPRESSION BY ARRAY GROUP (EVALEXPRESSIONWITHGROUPARRAY)
Syntax:
EvaluateGroupExpressionArray (,)
Description:
The function returns an array, each element of which contains the result of evaluating the expression to be grouped by the specified field.
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.
When generating expressions for displaying a custom field, the expression of which contains only the CalculateArrayWithGroupingArray () function, the layout composer generates the displayed expression so that the displayed information is ordered. For example, for a custom field with an expression:
CalculateExpressionWithGroupingArray ("Sum (SumTurnover)", "Counterparty")
The layout composer will generate the following expression for output:
Connect Rows (Array (Arrange (EvaluateExpressionWith GroupingValuesTable ("View (Sum (Dataset.SumTurnover)), Sum (Dataset.SumTurnover)", "Dataset.Contractor"), "2")))
Parameters:
Type: String. The expression to evaluate. A string, for example, Amount (AmountTurnover).
Type: String. Grouping field expressions are grouping field expressions, separated by commas. For example, Counterparty, Party.
Type: String. An expression describing the selection applied to detail records. The expression does not support the use of aggregate functions. For example, Delete Mark = False.
Type: String. An expression describing the filtering applied to group records. For example, Sum (SumTurnover)> & Parameter1.
Example:
Maximum (CalculateExpressionWithGroupArray ("Sum (SumTurnover)", "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, let's duplicate the "Order" field, with different values "Calculate by ...", using the following expressions, note that in each higher level, the values of the levels below the groupings are used.
As a result, we get the following construction:
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 with 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 - Balances & Turnovers.Final Balance Balances & 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.
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 displaying the resource-field by which the grouping is performed in 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 a 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<Пустое значение>.
One of the most convenient and unique development tools in 1C is the data composition system (ACS). In few information systems, developers can create reports without writing program code. This mechanism was developed in order to simplify and speed up the development of reporting forms and provide more opportunities for users to work with the output data. The latter is very highly appreciated by advanced users who, thanks to this, can independently customize the report to their own needs, without waiting for the developers' actions.
Creation of a report in 1C via ACS
The process of developing a report using the ACS can be divided into the following stages:
- Creation of a request. The request can be written manually or you can do without the program code using a user-friendly interface;
- Customizing the report. Fields, totals, groupings, parameters, report design are selected;
- After that, we will only have to connect the resulting report to the configuration in any available way.
Despite the ability of users to customize reports on the ACS, they must be created through the configurator.
Consider an example of creating an external report on the ACS:
Now we go into 1C, open our report to make sure that the actions performed are correct. All data is reflected, groupings can be collapsed and expanded. As you can see, ACS allows you to receive full-fledged reports without writing code, except for non-standard requirements. Considering that most reports have a similar structure, knowledge of the ACS will significantly reduce the development time for these objects.
The great popularity of this mechanism was provided by the support of wide possibilities in the formation of the report. Moreover, they can be used not only by developers, but also by ordinary users.
ACS capabilities
There are situations when we have made a report, and then a user comes and asks to make a small revision. For example, display SKUs instead of item names. ACS allows users to make such improvements on their own using the "More" button - "Change option ...".
The window that opens is similar to the settings window in the report in the configurator, and it also has similar functions. To solve the problem, the user must go to the "Fields" tab and change the "Nomenclature" field. This field for editing is opened by double-clicking, and the "Select ..." button becomes available.
The window that opens provides us with the ability to select any value that will appear in the "Nomenclature" field. Some fields on the left have a plus sign - the developer has placed links in these fields, which means that we can see their details. We open the "Nomenclature" and see the article that we need. Select it and select it.
The window for editing report variants contains many useful functions of the data composition system. For example, a user can independently change the order of groupings, add a selection or apply a conditional design. We finish editing and generate a report - as you can see, the entire nomenclature is now displayed in the form of SKUs.
The 1C: Enterprise 8.3 ACS mechanism also has advanced functionality for developers. When developing the report, we used only 2 tabs - "Datasets" and "Settings", but there are many more of them in the ACS. To use all the functions of the data composition system, you need to understand why each of the tabs is needed:
- Datasets - all queries involved in the generation of the report are listed here;
- Dataset links - used to build links between different queries from the first tab;
- Calculated fields - a list of added fields not from the request. Most often it is used in cases when, based on the values of several fields, you need to get 1 value from a query;
- Resources. In 1C, the so-called fields for which you need to know the results. The resources support various arithmetic operations - sum, quantity, maximum, and others;
- Parameters. They are used if, in order to generate a report, it is necessary for the user to enter certain data - the date, divisions, or nomenclature, for example;
- Layouts. Designed for those cases when users want to see a uniquely designed report. Create a separate place for signatures or a new upper part of the report - all this can be done here;
- Nested schemas. They are required when your report is to contain data from other reports;
- Settings. The section declares the displayed fields, groupings, and customizes the appearance of the report.
The number of possibilities inherent in the ACS mechanism by developers is large, but many of them are used extremely rarely. Even experienced 1C programmers over the years of work may not use some functions. To start successful work in the ACS, it is enough to know the basic concepts and frequently used settings. In rare cases, documentation will come to the rescue.
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 the quantity of goods on a cumulative basis is required to be obtained 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).
