Data Composition System Expression Language (1Cv8). Data Composition System Expression Language (1Cv8) Remainder
One of the most important areas of business software is reporting. The fate of a business can depend (and not in a figurative sense!) on how easy it is to customize an existing report to the changing needs of business (and legislation) or create a new one, be it 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 makes it easy to 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 the ideal that every business system should strive for.
In the 1C:Enterprise platform, a mechanism called the “Data Composition System” (abbreviated as DCS) 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 a declarative description of reports. The access control system is designed for generating reports and displaying information with 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 rows, groupings, etc.).
A little history
In the very first version of the 1C:Enterprise 8 platform, version 8.0, reports were made like this:- 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 could not 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 access control system was the flexibility and customization of reports, which was accessible to both the developer and the end user. Ideally, I would like to give the end user access to the same set of report design tools as the developer. It would be logical to create a single set of tools available to everyone. Well, since the tools require the participation of the end user, it means that the use of programming in them should be reduced to a minimum (it is best to eliminate it completely), and visual settings should be used to the maximum.
Formulation of the problem
The task before the development team was to create 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 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), mostly through visual settings.The development of the first version of the SDS took about 5 person-years.
Two languages
There are two languages involved in creating reports. One is a query language used to retrieve data. The second is the data composition expression language, intended for writing expressions used in various parts of the system, for example, in data composition settings, to describe expressions of user fields.Query language
The query language is based on SQL and is easy to learn for those knowledgeable in SQL. Example request:
It is easy to see analogues of sections standard for SQL queries - 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 problems and at minimizing the effort required to develop application solutions:
- Accessing fields using a dot. If the fields of a table are of a reference type (they store links to objects of another table), the developer can refer to them in the text of the request through “.”, and the system does not limit the number of nesting levels of such links (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 traversed in any order with summing up, and the correct construction of totals according to time dimensions is ensured.
- Support for virtual tables. Virtual tables provided by the system allow you to obtain almost ready-made data for most application tasks without the need to create complex queries. Thus, a virtual table can provide data on product balances by periods at a certain point in time. At the same time, virtual tables make maximum use 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 blockings and make the query text easier to read.
- Batch requests. To make working with temporary tables more convenient, 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 semicolons (";"). The requests in the batch are executed one after another. 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 results from all queries in the batch in the sequence in which the queries in the batch follow.
- Retrieving representations of reference fields. Each object table (in which a reference book or document is stored) has a virtual field - “View”. This field contains a textual representation of the object and makes the report creator's job 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 access control systems. Expansion is carried out using special syntactic instructions enclosed in curly braces and placed directly in the request body. Using extensions, the developer determines what operations the end user will be able to perform when 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 that will be available for configuration are listed, separated by commas. Example: (SELECT Item, Warehouse)
- WHERE. The fields on which the user can apply selection are described. This proposal uses table fields. The use of selection 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 designed to write expressions used, in particular, to describe custom field expressions. SKD 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 similar to calculated fields. They can be set both in the configurator and in 1C:Enterprise mode, but the functions of common modules cannot be used in custom field expressions. Therefore, custom fields are intended for the user rather than the developer.Example: 
The process of creating a report on the access control system
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 layout diagram. A data layout diagram describes the essence of the data that is provided to the report (where to get the data from and how its layout can be controlled). 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 instructions for the data composition system;
- description of multiple data sets;
- detailed description of available fields;
- describing relationships between multiple 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 create a query of arbitrary complexity:
The result of launching the query designer will be the query text (in the 1C:Enterprise query language). This text can be adjusted manually if necessary:
There can be several data sets in a data layout scheme, data sets can be linked in the layout in any way, calculated fields can be added, report parameters can be specified, etc. It is worth mentioning an interesting feature of the query mechanism in 1C:Enterprise. Queries are ultimately translated into a dialect of SQL specific to the DBMS with which the application directly operates. 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 DBMSs supported by the 1C:Enterprise platform - MS SQL, Oracle, IBM DB2, PostgreSQL). Thus, at the query level in calculated fields, we can only use those functions that are translated into SQL.
But at the level of the data composition scheme, 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 SKD calculates for each record the values of calculated fields whose formulas are written in the 1C language.
Adding Custom Fields
You can add an arbitrary number of tables and charts to the report:
Report designer
Runtime report
Using SKD, the user can add complex selections to the report (which will be added to the request in the right places), conditional design (allowing the displayed fields to be formatted differently - with font, color, etc., depending on their values) and much more. .
The process of constructing and generating a report can be briefly described as follows:
- The developer in design time with the help of a designer (or in runtime using code) determines the data layout scheme:
- Text of the request/requests
- Description of calculated fields
- Relationships between requests (if there are several of them)
- Report Options
- Default settings
- Etc.
- The above settings are saved in the layout
- User opens report
- Possibly makes additional settings (for example, changes parameter values)
- Clicks the “Generate” button
- User settings are applied to the data composition scheme defined by the developer.
- An intermediate data composition layout is formed, containing instructions on where to receive data from. In particular, the queries specified in the layout are adjusted. Thus, 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 participate in calculated field formulas are added to the query.
- The data composition processor comes into play. The layout processor executes queries, links data sets, calculates values for calculated fields and resources, and performs grouping. In a word, it makes all the calculations that were not performed at the DBMS level.
- The data output processor launches a request for execution and displays the received data in a spreadsheet document, chart, etc.
The process of generating a report using 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 downloaded 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 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 up one or two report parameters (for example, period and counterparty). Starting from 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, which 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 our priority areas in the development of access control systems is simplifying user settings. Our experience shows that for some end users, working with user settings is still a major undertaking. We take this into account and are working in this direction. Accordingly, it will also become easier for developers to work with access control systems, because We, as before, want to provide a single tool for setting up reports for both the developer and the end user.SKD 1C stands for Data Composition System. SKD 1C is a new way of writing reports in 1C, which allows the user to completely customize the report themselves.
The disadvantage of SKD 1C is that its settings are quite complex and not all users learn them quickly.
Writing ACS reports requires the ability to work with queries in 1C, which we discussed in.
What is the technology for creating an SKD 1C report:
- Write a 1C request to the 1C SKD, which provides data acquisition
- Specify the role of fields in SKD 1C (calculated fields, resources)
- Enter default 1C SKD settings.
The user has the opportunity to change many settings at will.
What is the technology for creating a report on previous technologies:
- Write a request in the 1C program
- Develop a settings form (unique for the report) that will allow you to change only N settings selected by the programmer
- Write the code (program) for executing the request and constructing a printed form of the report.
As you can see, SKD 1C has a significant advantage for both the user and the programmer:
- Programmer – eliminates the need to write a program to run the report and settings
- User – gains significant access to report settings.
In all new 1C configurations, all reports will be used only on the 1C SKD.
Let's see how to create a report on SKD 1C yourself from scratch.
Creating a SKD 1C report
In the configurator, click the new file button (File/New menu).
Select the file type New Report.
A new report will be created. Let's enter a name - without spaces for the configuration, a synonym for the user.
Let's create the basic scheme of SKD 1C.
We create a request for the SKD 1C report
There can be many data sources for SKD 1C. To use, for example, two separate queries, add first “Data set - union”, and then several queries.
In our example we will use a simple query.
Let's sketch out a request to obtain data.
Working with the query constructor is no different from usual; we discussed this in.
As a result of generating the request, SKD 1C will create a list of fields available for use and fill them with default names.
If the name is inconvenient, you can change it here.
Please note that we used the &StartDate parameter in the request. In SKD 1C there is a default parameter (date) called &Period and you can use it.
Moreover, you do not need to specify it directly in the request - it will be used automatically. However, there is a subtlety - it applies to all tables, including left joins and so on, which can give rise to errors.
For example, you get balances at the beginning of the month, and using the left join you get data for today. When used, Period will be applied equally to all tables and the query will not work correctly.
SKD 1C settings
On the Resources tab, we indicate the fields that will be summed up for the totals (i.e., numbers). You can specify a summation function for fields. By default it is Sum (i.e. sum the numbers from all lines and get the total), but you can use Average, Quantity, Maximum, etc.
On the Settings tab, we will disable the ones we don’t need and enable the ones we need. Here you can also set a human name for the parameter.
Basic settings are made on the Settings tab:
Let's open it in Enterprise mode. Please note that we did not do any programming, drawing shapes, etc. To set the parameter, click the Settings button.
The user sees almost the same form of settings as the programmer.
Voila. The report is working.
SKD 1C settings
All settings of the SKD 1C report are made by clicking on the Settings button. At least in the default template. It is possible to create the SKD 1C report form yourself and then the settings can be transferred to other forms drawn by the programmer independently.
Let's drag the Warehouse from the Columns to the Rows.
Let's drag the Item to the Warehouse.
Click OK and generate the report. Hooray - we just changed how the report works in Programming mode without programming.
Right-click on Rows (or any of the fields) and select New Grouping.
Expand the Warehouse field and select one of its fields, for example Warehouse type.
After thinking, we remembered that we would like several fields to be displayed in one column at once. Click on the newly created field twice with the left mouse button. Here you can add fields.
If you add a new grouping but do not select a field, it means “All Fields” (displayed as “Detail Entries”). It looks like this.
Left-click our grouping (Warehouse/Item) and drag it to a new grouping (Warehouse type).
The result of our settings.
In addition to the settings, the effect of which is easy to guess by their name (Selection, Sorting, etc.), there is a tab “Other settings”. It allows you to enable or disable such things as displaying totals, placing groupings in columns, etc.
Please note that settings can be set for the entire report (Report is selected) or for a specific row of fields (select a row, for example Nomenclature, and select Settings:Nomenclature).
In light of the upcoming release of 8.2.14, I will try to describe some new functions of the data composition system.
Open the data layout diagram, preferably in an external report, to make editing easier.
We add a dataset of the query type and write, either manually or using the query designer, a simple query:
1. Set up a request in the access control system.
2. Set up calculated fields in the access control system
3. Configure the data layout on the settings tab
4. Launch 1C Enterprise 8.2.14. Open the report. We form, we receive.
Description of the new functions themselves:
1. The current date()
Returns the system date. When composing a layout layout, in all expressions that are present in the layout, the CurrentDate() function is replaced with the value of the current date.
2. COMPUTEEXPRESSION()
Syntax:
CalculateExpression(,)
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 that grouping. For example, in selecting the Nomenclature grouping, you cannot use the expression CalculateExpression("Sum(SumTurnover)", "TotalTotal") > 1000. But such an expression can be used in hierarchical selection.
If the end record precedes the start record, then it is considered that there are no records for calculating detailed data and calculating aggregate functions.
When calculating interval expressions for a grand total (the Grouping parameter is set to GrandTotal), it is assumed that there are no records for calculating detailed data and calculating aggregate functions.
Layout linker when generating a function expression CalculateExpression, if the ordering expression contains fields that cannot be used in grouping, replaces the function CalculateExpression on NULL.
Options
Type: Line. The expression to be evaluated.
Type: 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 the grouping name, the calculation will be performed in the context of the current grouping. If the GeneralTotal string is used as the group 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 group with the same name.
For example:
Sum(Sales.SumTurnover)/Calculate("Sum(Sales.SumTurnover)", "Total")
In this example, the result will be the ratio of the sum by field Sales.AmountTurnover grouping records to the sum of the same field in the entire layout;
Type: Line. The parameter can take the following values:
· Grand total— the expression will be calculated 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 group grouping record.
· 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 a function CalculateExpression() with meaning GroupingNonResource for group records that are not grouped 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 data composition layout builder, when generating a data composition layout when outputting a resource field by which grouping is performed to the layout, places an expression in the layout that is calculated using the function CalculateExpression() , indicating the parameter GroupingNonResource. For other resources, the usual resource expressions are placed in the resource grouping.
Type: Line. Indicates from which record the fragment should begin, in which aggregate expression functions should be calculated, and from which record to obtain field values outside of aggregate functions. The value can be one of the following:
· First
· Last (Last)
· Previous
· Next (Next)
· Current
· LimitingValue(BoundaryValue) LimitingValue
Type: Line. Indicates to which record the fragment should be continued, in which the aggregate functions of the expression should be calculated. The value can be one of the following:
· First. It is necessary to obtain 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 greater than zero. For example, First(3) - receiving the third record from the beginning of the grouping.
If the first record is outside the grouping, 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 last grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset from the end of the grouping. The resulting value must be an integer greater than zero. For example, Last(3) - receiving the third record from the end of the group.
If the last record is outside the grouping, then it is considered that there are no records. For example, if there are 3 records, and you want to get Last(4), then it is considered that there are no records.
· Previous. You need to get the previous grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset back from the current grouping record. For example, Previous(2) - getting the previous from the previous record.
If the previous record goes beyond the grouping (for example, for the second grouping record you need to get Previous(3)), then the first grouping record is obtained.
When retrieving the previous record for a grouping total, it is considered that the first record is obtained.
· Next (Next). You need to get the next grouping record. After the word in brackets, you can specify an expression, the result of which will be used as an offset forward from the current grouping entry. For example, Next(2) - getting next from the next record.
If the next record goes beyond the grouping, then it is considered that there are no records. For example, if there are 3 entries and the third entry receives Next() , then it is considered that there are no entries.
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 a grouping total, the first record is obtained.
· LimitingValue(BoundaryValue). The need to get a record by the specified value. After the word LimitingValue in brackets you need to indicate the expression with the value of which you want to start the fragment, the first ordering field.
The first record whose ordering field value is greater than or equal to the specified value will be returned as the record. For example, if the Period field is used as the ordering field, and it has the values 01/01/2010, 02/01/2010, 03/01/2010, and you want to get LimitingValue(DateTime(2010, 1, 15)), then a record with the date 02/01/2010 will be received.
Type: Line. Lists expressions, separated by commas, that describe the ordering rules. If not specified, then 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(for ordering in ascending order), Descending(for ordering in descending order) and Auto-order(to order reference fields by the fields by which you want to order the object being referenced). Word Auto-order can be used as with the word Age, so with the word Descending.
Type: Line. Same as parameter Sorting. Used to organize hierarchical records. If not specified, the layout builder generates the ordering according to the ordering specified in the parameter Sorting.
Type: 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 determine 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 | January 01, 2001 |
Ivanov M. |
10 |
| 2 | 02 January 2001 | Petrov S. | 20 |
| 3 | January 03, 2001 | Sidorov R. | 30 |
| 4 | 04 January 2001 | Petrov S. | 40 |
Separately, That:
§ the previous entry to entry 3 will be entry 2.
Current, Current(respectively, 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, That:
§ the previous entry to entry 3 will be entry 1.
§ if the calculation fragment is defined as Current, Current(respectively, parameters Start And End), then for record 2 this fragment will consist of records 2 and 3. Expression CalculateExpression("Sum(Value)", Current, Current) will be equal to 50.
When specifying a parameter value equal to Together, in parameters Start And End you cannot specify an offset for positions First, Last, Previous, Next.
CalculateExpression("Sum(SumTurnover)", "First", "Current")
If you want to get the grouping value in the previous line, you can use the following expression:
CalculateExpression("Rate", "Previous")
List new functions:CalculateExpressionWithGroupArray(,) -
The function returns an array, each element of which contains the result of evaluating an expression for grouping by the specified field.
CalculateExpressionWithGroupValueTable(,) -
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 other than the default value of this type, other than NULL, other than an empty reference, other than Undefined. Boolean values are checked for NULL values. Strings are checked for the absence of non-whitespace characters
Format(, ) - Receive a 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 a string. Parameter is a string 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 string representations of all rows of the table of values, with the cell representations of each row separated by ";" characters, and the rows by a newline character. If an element's string representation is empty, then a string is displayed instead of its representation.
Quite often there is a need to work with variables of the “Date” type. In this article we will look at the basic techniques - passing the current date, checking for an empty value, an arbitrary date.
When writing queries, you often need to compare data with the current date. The built-in 1C language has the CurrentDate() function. It allows you to determine the current time and date on your computer. To perform operations with the current date, you must pass the value of this function as a parameter to the request.
Below is a query that selects all files attached to expense reports with a creation date up to now:
ExampleRequest = New Request;
Example Request.Text = "
|SELECT
| AdvanceReportAttachedFiles.Link
|FROM
| Directory.AdvanceReportAttachedFilesAS AdvanceReportAttachedFiles
|WHERE
| AdvanceReportAttachedFiles.Date< &ТекДата»;
Example Request.SetParameter("CurrentDate", CurrentDate());
Custom date
The function discussed above allows you to compare and, therefore, make a selection for any period. This method allows you to specify a strict selection in the request without using additional parameters.
Please note that when using this function in the example above, we only passed three numbers (year, month, day) as input parameters. The last three (hour, minute, second) are optional and, if absent, are replaced with “0”, that is, the beginning of the day.
This example will retrieve all files attached to expense reports up to the end of last year 2016. In this regard, we will indicate the hour, minute and second to compare the time point “December 31, 2016 23:59:59”.
CHOOSE
AdvanceReportAttachedFiles.Link
FROM
Directory.AdvanceReportAttachedFiles AS AdvanceReportAttachedFiles
WHERE
AdvanceReportAttachedFiles.Date< ДАТАВРЕМЯ(2016, 12, 31, 23, 59, 59)
Blank date
The easiest way to check a variable to see if it contains an empty date is to use a simple comparison. In this example, using a query, we will select all cash receipts to the account for which the incoming date is not filled in.
Let's assume that we have received a task for which the customer wants to receive a report on the “Sales of goods and services” documents entered into the database, and for each document it is necessary to obtain the price from the information register “Item prices” on the date of the document. The report is written for the "Manufacturing Enterprise Management" configuration version 1.3.
The record from the document table is joined by a record from the virtual table "Nomenclature Prices. SliceLast" according to the corresponding conditions of the period, price type and item. The price type is obtained from the document details of the same name.
The problem has several ways to solve it. Let's consider two of them: a report on the access control system using two sets of data and obtaining all the necessary data in one request. Now, in order.
Using ACS
To implement such a report on the access control system, we will create two data sets in the data composition scheme. The first will receive a list of documents, the second will receive prices for document dates according to the selected item and price type. The connection between two sets of data is carried out by item, period (document date) and price type. From the first set it is necessary to transfer the parameters “Nomenclature”, “Price Type” and “Period” to the second set.
The first data set contains the following query:
The query selects the following fields from the document table and the “Products” tabular part: “Link”, “Date”, “Price Type”, “Nomenclature”. Now let's look at the second data set:
In the second set of data, you should pay attention to the parameters passed to the request. The list of selectable fields contains the parameters "Period, "Item" and "Price Type". The same parameters are used in the parameters of the virtual table "Nomenclature Prices. Slice of Latest". It is necessary to display these parameters in the list of selectable fields so that the values of these parameters can be transferred from the first set to the second set. This is done using the connection settings between data sets in the “Data Set Connections” tab of the data layout diagram designer:
Setting up connections between data sources is carried out using the selection fields in the request. In the second data set, we added query parameters to the selection fields. When establishing relationships between sets, if you select a field in the "Parameter" column for the relationship, the value from the "Source Expression" column will be transferred to the "Destination Expression" and fill the selected parameter with the corresponding value.
Having configured the report structure and generated it, we will get the following result:
The report is working. Let's move on to consider options for obtaining the price for the document date in one request.
In one request
Let's create a new report with similar output fields and report settings. The only difference will be the data sources. This time, the report will contain a single data set in which all document products and their associated prices are selected in one query. The request text looks like this:
"SELECT | DocumentNomenclaturePeriod.Document, | DocumentNomenclaturePeriod.Document.Date AS Date, | DocumentNomenclaturePeriod.Document.PriceType AS TypePrice, | DocumentNomenclaturePeriod.Nomenclature, | PricesNomenclature.Price | FROM | (SELECT | Sales of GoodsServicesProducts. Link AS Document, | ovServicesProducts.Nomenclature AS Nomenclature, | MAXIMUM (PricesNomenclatures.Period) AS Period | Document.Sale of GoodsServices.Goods AS Sales of GoodsServicesGoods | LEFT CONNECTION Register of Information.PricesNomenclatures AS PricesNomenclatures | Clatura | And Sales of Goods and Services Goods. Link. Date >= Prices Nomenclatures. Period | And Sales of GoodsServicesProducts.Link.PriceType = PricesNomenclatures.TypePrices | | Software DocumentNomenclaturePeriod.Nomenclature = PricesNomenclature.Nomenclature | And DocumentNomenclaturePeriod.Document.PriceType = PricesNomenclature.PriceType | And DocumentNomenclaturePeriod.Period = PricesNomenclature.Period"
The principle of the request is to obtain the nearest date of price change for each used item in the tabular section of the document, and then attach data from the physical table “Item prices” by period, item and price type to the resulting query result. At first glance, the request may seem complicated, but in fact its syntax is quite simple.
The execution result is similar to the result of the previous report (see screenshot above).
Draw conclusions
Which option for completing the task is better? It all depends on the specific conditions. In some situations, a report from two data sets will be faster than retrieving all the data for a report in one query. Within the scope of this article, we will not be able to touch upon performance issues for the considered methods of solving the problem.
It is also worth remembering that creating reports based on document tables is not recommended by the development methodology from the 1C company, since it is believed that the information in the documents may not be reliable. All reports must be based on registers.
