1s ckd calculated fields expression. Data Composition System Expression Language (1Cv8). Common module functions
EvaluateExpression- enough complex function ACS for understanding, and application examples in reference information are rather scarce. This article discusses examples that are sure to be useful to every developer:
- cumulative total in the grouping;
- the running total in the crosstab;
- getting the previous value;
- output of PM in one line.
1. Getting the indicator on a cumulative total
Let's get the number of goods on a cumulative basis at the grouping level. To do this, we create a calculated field (see Figure 1).On the "Resources" tab, set the function for the calculated field:
CalculateExpression ("Sum (NumberTurnover)", "First", "Current")
which will sum up the number of products from the first record to the current one (see Figure 2).
If you want to get the quantity of goods on a cumulative basis at the level of detailed records, then the function Calculate Expression is set for the calculated field on the tab "Calculated fields" (see Figure 3).
Depending on the level of obtaining the cumulative total, we create a grouping (see Figure 4): at the resource level - grouping by goods, at the DZ level - grouping of detailed records.
 |
| Figure 4. Groupings of the report with a cumulative total |
2. Getting the value of the indicator from the previous line
Let's get the currency rate for the date and the previous date. To do this, create a calculated field and write the following expression in the expression field (see Figure 5):CalculateExpression (Course, Previous, Previous)
which will take the previous value of the currency rate for the current line, the last parameter of the function limits the data retrieval.
Since we are working at the level of detailed records, go directly to the "Settings" tab and create a grouping - detailed records.
3. Getting an indicator on a cumulative total in a cross-tab
Let's get the number of goods on a cumulative basis in the context of the period. To do this, we create a calculated field (see Figure 1). On the "Resources" tab, for a calculated field, we will specify the following expression (see Figure 6):Calculate Expression ("Sum (NumberTurnover)", "Period", "First", "Current")
which, at the grouping level, will calculate the quantity of goods in the interval from the first line to the current one in the context of the period for each item.
On the "Settings" tab, create a table grouped by item in a row and grouped by period in a column (see Figure 7).
4. Output of tabular section data in one line
Methods for displaying tabular section data in one line, including the method using the EvaluateExpression function, are discussed in the articleOne of the most important areas of business software is reporting. The fate of the business may depend (and not in a figurative sense!) On how easy it is to adjust the existing report to the changing needs of the business (and legislation) or to make a new one, 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 necessary data from the system, present it in an understandable form, allowing the end user to reconfigure a standard report to see the data in a new light - this is an ideal that every business system should strive for.
In the 1C: Enterprise platform, a mechanism called “Data composition system” (abbreviated ACS) is responsible for generating reports. In this article we will try to give short description ideas and architecture of the ACS mechanism and its capabilities.
ACS is a mechanism based on the declarative description of reports. ACS is intended for building reports and for displaying 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 design of lines, groupings, 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 several queries were written in the 1C query language (SQL-like language, more on it below).
- A code was written that transferred the results of executed queries to spreadsheet document or into a diagram. The code could also do work that cannot be done in a request - 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 customizability of reports, moreover, 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 end user, it means that you need to remove the use of programming in them to a minimum (it is best to eliminate it altogether), and use the visual settings to the maximum.
Formulation of the problem
The task for the development team was as follows - to make a reporting system based not on an algorithmic (i.e., through writing code), but on a declarative approach to creating reports. And we believe that the task 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), mostly through visual settings.The development of the first version of the ACS took about 5 man-years.
Two languages
There are two languages involved in reporting. One is the query language used to fetch data. The second is a data composition expression language, designed to write expressions used in various parts of the system, for example, in data composition settings, to describe expressions of custom fields.Query language
The query language is based on SQL and is easily mastered by knowledgeable SQL. Example request:
It is easy to see analogs of the standard sections for a SQL query - 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 minimizing efforts to develop applied solutions:
- Point-to-point reference to fields. If the fields of a table are of a reference type (they store references to objects of another table), the developer can refer to them in the query text with "." Telephone).
- Multidimensional and multilevel formation of results. Totals and subtotals are formed taking into account the grouping and hierarchy, the levels can be traversed in any order with summing up the 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 applied tasks without the need to compose complex queries. So, a virtual table can provide data on the balances of goods in the context of periods at some 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 the performance of queries, in some cases reduce the number of locks and make the query text easier to read.
- Batch requests. For more comfortable work with temporary tables in the query language, batch queries are supported - thus, creating a temporary table and using it is placed in one query. A batch request is a sequence of requests separated by a semicolon (";"). The requests in the package are executed one after the other. The result of executing a batch request, depending on the method used, will be either the result returned by the last request in the batch, or an array of the results of all requests in the batch in the order in which the requests in the batch follow.
- Getting representations of referenced fields. Each object table (which stores a reference or document) has a virtual field - "View". This field contains the textual representation of the object and facilitates the work of the report writer. 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 dated 07/06/2017 17:49:14"), saving the developer from writing a calculated field.
- and etc.
There are also special extensions of the query language for ACS. Expansion is performed using special syntax instructions enclosed in curly braces and placed directly in the query text. With the help of extensions, the developer determines what operations the end user can carry out by customizing the report.
For example:
- SELECT. This sentence describes the fields that the user can select for display. After this keyword Aliases of fields from the main query selection list that will be available for customization are listed separated by commas. Example: (SELECT Item, Warehouse)
- WHERE. Describes the fields on which the user can filter. This proposal uses table fields. The use of select list field aliases is not allowed. Each part of the union can contain its own WHERE element. Examples: (WHERE Item. *, Warehouse), (WHERE Document.Date> = & StartDate, Document.Date<= &ДатаКонца}
- and etc.
Data Composition Expression Language
The data composition expression language is intended to write expressions used, in particular, to describe custom field expressions. ACS allows you to define custom fields in the 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 the 1C: Enterprise mode, but the functions of common modules cannot be used in the expressions of custom fields. Therefore, custom fields are intended for the user rather than the developer.Example: 
The process of creating a report on the ACS
When creating a report, we need to create a layout that determines how the data will be displayed in the report. You can create a layout based on the data composition schema. The data composition schema describes the essence of the data that is provided to the report (where to get the data and how you can control its composition). The data composition schema is a base on the basis of which all kinds of reports can be generated. A data composition schema can contain:- request text with instructions of the data composition system;
- description of several data sets;
- a detailed description of the available fields;
- description of relationships between several datasets;
- description of data retrieval parameters;
- description of layouts of fields and groupings;
- and etc.
For example, you can add a query as a dataset to the data composition schema, and call the query constructor, which allows you to graphically compose a query of arbitrary complexity:
The result of running the query designer will be the query text (in the 1C: Enterprise query language). This text can be manually adjusted if necessary:
There can be several datasets in a data composition schema, datasets 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 ultimately translated into a dialect of SQL specific to the DBMS that the application interacts with directly. 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 available simultaneously 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 level of the data composition schema, 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 for each record the values of calculated fields, whose formulas are written in 1C language.
Adding custom fields
You can add an arbitrary number of tables and charts to the report:
Report Designer
Runtime report
With the help of the ACS, the user can add complex filters to the report (which will be added to the request in the right places), conditional design (which allows you to format the displayed fields in different ways - by font, color, etc. - depending on their values) and much more ...
The process of building and generating a report can be briefly described as follows:
- The developer at design time with the help of the designer (or at runtime with the help of code) defines the data composition scheme:
- Request / requests text
- Description of calculated fields
- Relationships between requests (if there are several)
- Report parameters
- Default settings
- Etc.
- The above settings are saved in the layout
- User opens a report
- Perhaps makes additional settings (for example, changes parameter values)
- Presses the "Generate" button
- User settings are applied to a developer-defined data composition schema.
- An intermediate data composition template is formed, containing instructions on where to get data from. In particular, the requests specified in the layout are adjusted. For example, 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 included in the calculated field formulas are added to the query.
- The data composition processor is involved. The composition processor executes queries, communicates datasets, calculates the values of calculated fields and resources, and performs grouping. In a word, it does all the calculations that were not performed at the DBMS level.
- The data output processor launches an execution request and outputs the received data to a spreadsheet document, diagram, etc.
The process of generating a report by the ACS mechanism
We try to minimize the amount of report 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 pumped from the server to the client.
Custom settings
All ACS tools are available to both the developer and the end user. But practice has shown that the end user is often intimidated by the abundance of the tool's capabilities. Moreover, in most cases, the end user does not need all the power of the settings - he just needs 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 "Display mode" flag appeared in the report settings, which takes one of three values:- Fast access. The setting will be displayed directly in the upper part of the report window.
- Normal. The setting will be available through the "Settings" button.
- Not available. The setting will not be available to the end user.
Setting display mode at design time
Displaying the setting in the "Quick Access" mode at runtime (under the "Generate" button)
Development plans
One of the priority directions in the development of ACS for us is to simplify user settings. Our experience shows that for some end users, working with custom settings is still a serious work. We take this into account and work in this direction. Accordingly, it will also become easier for developers to work with ACS, since we, as before, want to provide a unified toolkit for customizing reports for both the developer and the end user.Competent use of the data composition scheme (ACS) allows you to:
- significantly reduce the time required to develop a report;
- get rid of the need to create a managed form handler;
- get a beautiful result at the output with the possibility of additional customization by the user.
But not all developers make the most of the circuit's capabilities, since not all of its settings are obvious and intuitive. In particular, many people know that in 1C ACS there are calculated fields, however, they do not fully represent the area of their use and methods of working with them.
What is a calculated field
In most cases, a query acts as a data source in a layout schema. In principle, within the query itself, you can already use various formulas, constructions and expressions. A natural question arises, why do we need duplicate functionality?
The fact is that the ACS is more than just displaying the result of the query execution, and this is perfectly visible from the form for creating a schema (Fig. 1).
Calculated fields allow you to perform certain actions with the generated dataset:
- Output to a specific cell an array of data received by a query by concatenating several lines into one;
- Access the export functions of the common module;
- Execute various expressions available for the layout language and use the EvaluateExpression special functions.
Let's follow this list and go.
An array of values in one cell
Let's simulate a situation when it is necessary to get in a separate cell all the numbers of receipts for a counterparty:
thus, we have created an additional calculated field in our scheme;
As you can see from the above example, there are no difficulties in adding and processing calculated fields. We used two functions: Array () and JoinStrings ().
A few words about the latter. In addition to the first parameter indicating the identifier of the array, values or values, two more can be set in it:
- Element Separator - indicates which character will separate one element of an array or one row of a table of values from another (in our case, we omitted this parameter and a line break was assigned by default);
- Column Separator is a character used to separate the columns of a table of values (semicolon is used by default).
Accessing the Export Functions of a Common Module
The functions of the general module can act as a data source for filling a calculated field.
A few important points:
- The function must be exportable;
- If a function is located in a common module with the "Global" flag set, it is called directly by name, otherwise the function should be called according to the "Common module name" scheme. The name of the called function.
As an example of use, we will take the same request for receipt documents and display it in a separate column. We will not describe the request itself, we will go directly to the calculated fields:
Thus, we see that almost any data handler can be initialized from the ACS, which greatly expands the possibilities of using the schema.
Link Language Expressions
Quite often in the work of a developer, a situation arises when it is necessary to display the result of division in the ACS field:
- Calculate the average cost of the item;
- All kinds of interest;
- Calculations of average earnings, etc.
To avoid problems, in these cases it is advisable to enter a division by 0 check in the calculated field.
This can be done using the "Choice When .... Then ... Otherwise ... End" construction.
At the end, a few words about the fairly new function EvaluateExpression (). With its help, in particular, you can calculate the deviations in value between the current and the previous line, cumulative balance, etc.
Suppose, you can get the Amount of the Document from the previous line of our query by specifying the value Calculate Expression ("Amount of Document", "PreviousSum") in the "Expression" field.
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 grouping Nomenclature, you cannot use the expression CalculateExpression ("Sum (SumTurnover)", "TotalTotal")> 1000... But such an expression can be used in hierarchical selection.
If the end record precedes the start record, then it is considered that there are no records for calculating detailed data and calculating aggregate functions.
When calculating interval expressions for 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.
Layout linker when generating a function expression EvaluateExpression, in case the ordering expression contains fields that cannot be used in the grouping, replaces the function EvaluateExpression on NULL.
Options
Type of: Line... The expression to evaluate.
Type of: Line... Contains the name of the grouping in the context of which the expression is to be evaluated. If an empty string is used as a grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal line is used as the grouping name, the calculation will be performed in the context of the grand total. Otherwise, the calculation will be performed in the context of the parent grouping with the same name.
For example:
Amount (Sales.SumTurnover) / Calculate ("Amount (Sales.SumTurnover)", "TotalTotal")
In this example, the result will be the ratio of the sum over the field Sales.AmountTurnover grouping records to the sum of the same field in the entire layout;
Type of: Line... The parameter can take the following values:
· The overall result- the expression will be evaluated for all grouping records.
· Hierarchy- the expression will be evaluated for the parent hierarchical record, if any, and for the entire grouping, if there is no parent hierarchical record.
· Grouping- the expression will be evaluated for the current group record of the grouping.
· GroupingNonResource- when calculating 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 meaning GroupingNonResource for group records that are not groupings by resources, the function is calculated in the same way as it would be calculated if the parameter value was equal to the value Grouping.
The composer of the data composition template, when generating the data composition template, when outputting the resource-field by which the grouping is performed to the template, places an expression calculated using the function EvaluateExpression () , specifying the parameter GroupingNonResource... For the rest of the resources, the regular resource expressions are grouped by resource.
Type of: Line... 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:
· First
· Last
· Previous
· Next (Next)
· Current
· Limiting Value(BoundaryValue) Limiting Value
Type of: Line... 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... You need to get the first grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset from the beginning of the grouping. The resulting value must be an integer number greater than zero. For example, First (3) - getting the third record from the beginning of the grouping.
If the first record is outside the grouping limits, then it is considered that there are no records. For example, if there are 3 records, and you want to get the First (4), then it is considered that there are no records.
· Last... 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... You need to get the current record.
When retrieving for the grouping total, the first record is obtained.
· Limiting Value(BoundaryValue)... The need to get a record at the specified value. After the word Limiting Value 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 ConstrainingValue (DateTime (2010, 1, 15)), then you will receive a record with the date 02/01/2010.
Type of: Line... 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 a keyword Age(to order in ascending order), Descending(for descending order) and Auto-ordering(to order the referenced fields by the fields by which you want to order the referenced object). Word Auto-ordering can be used as with a word Age, so with the word Descending.
Type of: Line... Similar to the parameter Sorting... Used to arrange hierarchical records. If not specified, the layout composer generates the ordering according to the ordering specified in the parameter Sorting.
Type of: Line... Specifies the rule for determining the previous or next record in case there are several records with the same ordering value:
· Separately indicates that a sequence of ordered records is used to define the previous and next records. Default value.
· Together indicates that the previous and next records are determined based on the values of the ordering 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 |
Separately, then:
The previous entry to entry 3 will be entry 2.
Current, Current(respectively, the parameters Start and End), then for record 2 this fragment will consist of one record 2. The expression 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 parameters Start and End), 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 specified, the parameter value is Together, in parameters Start and End you cannot specify an offset for positions First, Last, Previous, Next.
Calculate Expression ("Sum (SumTurnover)", "First", "Current")
If you want to get the grouping value in the previous line, you can use the following expression:
Evaluate Expression (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 array elements separated by ";" characters. If a table of values is passed as a parameter, the function returns a string containing the string representations of all rows of the table of values, with the cell representations of each row separated by the ";" characters, and the strings - by the newline character. If any element has an empty string representation, then a string is displayed instead of its representation.
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 EvaluateArrayWithGroupArray () 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")))
Options:
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, we will 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:
