Sometimes, in order to obtain a certain layout result, it is necessary to create links between data sets in the access control system. We will try to talk about when this is needed and how to do it in our article.

The data layout scheme implemented in 1C supports three types of data sources (Fig. 1)

Data sources

The most obvious and frequently used data source by developers is the Query.

The query designer implemented in version 8 of 1C is very convenient, and, in most cases, you can link several tables together and make the necessary groupings and selections directly in it.

In complex cases, when it is required or more appropriate to use the execution of some code (for example, loading data from a third-party file) or when it is not possible to use a query, the “Object” data source comes to the rescue. Arbitrary code executed in the module must have a certain structure at the output that the access control system can access.

The third data source is “Union”. In fact, this source can be considered as one of the types of communication. It combines (reduces several tables into one), without matching, but the coincidence of certain fields. That is, if two tables being joined have 3 and 4 rows, respectively, then the resulting data source will have 7 rows.

Formulation of the problem

As a testing ground, we will choose the UPP base, version 1.3.92.3, operating in normal application mode.

We have been given the following task: using only the access control system, create a report that would display a table of purchased items from the document, with prices taken from the file sent by the counterparty.

So we must:

1. Take tabular part receipt document;
2. Upload the file to the “Object” data source;
3. Link these two sources by a certain parameter (in our case this will be code);
4. Display the resulting table.

Execution Process

Let's go through all the steps listed:

• We create a query for the tabular part of the receipt document (Fig. 2)

Fig.2

• Create a data set object (Fig. 3);

Fig.3

Here it is important to pay attention to the “Name of the object containing the data”; this is the name we will have to specify in the code of the report module.

• We go to the report module and create the “When Composing Result” procedure there (Fig. 4);

Fig.4

We will not describe the procedure for obtaining data from an external file; we will pay attention to that part of the code that must be present in the layout so that we can obtain data for “Data Set 2” (Fig. 5).

Fig.5

Important! When creating an “Object” in procedure code during linking, the value of the StandardProcessing parameter must be False.

Go to the “Data Set Links” tab.

Linking sets

Go to the corresponding tab of the diagram (Fig. 6).

We see a table part that is very similar to the table part that is in the query designer, with some exceptions. The “All” checkbox cannot be checked for the communication source set and communication destination set, but several additional columns have been added.

In dataset relationships, you can only establish a relationship similar to the query constructor's left outer join.

Before creating a connection, let's decide on the purpose of the columns:

1. The link source is the first data set from which all available values ​​will be taken;
2. Communication receiver – a set of data from which values ​​corresponding to our condition will be selected;
3. Source expression – a field or expression of the first data set against which the comparison will occur;
4. The destination expression is a field or expression of the dependent set;
5. Parameter – if you specify the parameter name in this field, then communication with the receiver set will be carried out only according to the value specified in the parameter;
6. List of parameters – determines the possibility of using a list of values ​​as a parameter;
7. Connection condition – by specifying an expression here using source fields, you can create a condition, the fulfillment of which will serve as a signal to establish a connection;
8. Initial value – shows the initial value of the connection;
9. Mandatory Relationship - Determines whether the fields used in the source (set to FALSE) or destination (set to TRUE) are enabled and based on this adds the relationship to the layout.

Thus:

• The source of communication will be the result of our request;
• The object will act as a receiver;
• The source expression will be “NomenclatureCode”;
• Receiver expression “Nomenclature”;
• Communication will be mandatory (Fig. 7).

If we had indicated the name of any nomenclature, or its type of reproduction, or something else as a connection condition, we could have obtained a more accurate sample for our task. The result of our actions can be seen in Fig. 8

Fig.8

There was no match for the bottom two lines of the report in the pricing file.

A data composition system can use multiple data sets. And it is quite logical that these data sets should somehow be connected with each other. By analogy with the 1C query language, data sets can be either or. In this article we will talk about connection of data sets in access control system.

At first glance, it may be unclear why you need to create several data sets and then join them, if you can simply join tables within one data set. There is at least one reason for this - in the case of using an external data set. As you know, when we write a query for an ACS data set, we cannot pass into it, for example, . Although for a normal request this is done quite simply. In the case of using an access control system, we are forced to transfer a table of values ​​​​to it as a . Hence the need to connect data sets. Let me immediately note that data sets can only be connected using LEFT CONNECTION. Therefore, unlike here, you will not see checkboxes that are responsible for the type of table connection.

Now let’s come up with a situation, using an example of which we can consider connecting data sets in an access control system. Suppose we have a directory Goods, which we can receive from different suppliers, and we want to generate a report with prices for goods by supplier in order to select the supplier with the most favorable price. And we will take prices that are current at the moment, for example, through web services from suppliers’ websites.

Accordingly, for goods we will have a data set obtained using a request, and for prices we will have a data set obtained from an external source.

Now let's go to the bookmark Dataset relationships and make the settings as shown in the figure.

On the bookmark Settings First, let's make a basic output of detailed records


Let it be in our directory Goods contains the following entries

We run the report and see the following picture

That is, we see that the data sets were interconnected according to all the rules LEFT CONNECTION

On the bookmark Dataset relationships It is possible to specify some additional parameters in the appropriate fields. But they are used quite rarely. You can read more in the technical documentation and on the ITS disks.

Data layout diagram (1C SKD)- a convenient designer for creating complex reports in 1C:Enterprise software products that contribute to the development and tracking of production automation, allowing them to be made as flexible and beautiful as possible in a minimum of time. An additional advantage of the Data Composition Scheme (1C SKD) is the automatic generation of a controlled report form, and with further development of this area, it is an important factor when choosing a method for developing a report. But due to the complexity of the structure of the Data Composition Scheme (1C SKD) and the huge number of settings, it often leads to longer development of the report than through the “output form designer”. Therefore, a 1C programmer needs to understand all the intricacies of the Data Composition Scheme (1C DCS) in order to further speed up the development time for generating reports.

Let's look at the first three tabs of the Data Composition Scheme (1C SKD) - data set, data set connections and calculated fields.

Data set in 1C SKD

The data set includes the ability to create three objects - a query, an object and a union, let's take a closer look at each of them:

This is a regular query that is generated using the Query Builder button. If the Autofill flag is set, then all selected details will automatically be included in the fields of the data set. It is also possible to customize the filling of fields in the request on the Data Composition tab, where there are three tabs:

Tables, here the tables are selected that will participate in the generation of the report, usually the default data is selected, since on the Tables and Fields tab we have already selected the documents, directories, registers we need...

Fields, here we select those objects that should be included in the report, the children flag indicates whether there will be accessible child elements for the object or not, it is logical that for string, numeric and similar data it will not be possible to set the flag to True.

Conditions, here we select those objects that can be used under conditions in the access control system.

Some of the work is done in the data composition scheme, and some of it is done programmatically; let’s look at a simple example:

First, we will create a layout diagram for the data layout of the document and call it SKD (for example: 1C SKD), in it we create a data set object, then we fill in the fields, for example, we have a document with a tabular part of goods with details - nomenclature, quantity and price.

Let's add three fields and fill in each column with the name of the details, the remaining columns will be filled in automatically:

Let's create a button on the document form, describe the mechanism of operation in controlled forms:

&OnClient

Procedure Print()

OurReport = PrintOnServer(); //call the function on the server

OurReport.Show(); //display the generated report

End of Procedure

&On server

Function PrintOnServer()

DocumentObject = FormAttributeValue(“Object”);

//we place the tabular part Products in a structure with the name ProductsSKD in the same way as we indicated in the SKD itself the name of the object containing the data

DataSet = new Structure;

DataSet.Insert(“ProductsSKD”, DocumentObject.Products);

//we get our layout and set the default settings so that all report output settings are taken from our layout

OurLayout = DocumentObject.GetLayout(“SKD”);

Settings = OurLayout.DefaultSettings;

//create a data layout layout with our settings

LayoutLinker = newDataLayoutLayoutLinker;

LayoutLayout = LayoutComposer.Execute(OurLayout, Settings);

//perform data composition with our data set

DataCompositionProcessor = newDataCompositionProcessor;

DataCompositionProcessor.Initialize(LayoutLayout, DataSet);

//We create a spreadsheet document and display our report in it

ReportDocument = New TabularDocument;

OutputProcessor = New OutputProcessorDataCompositionResultInTabularDocument;

OutputProcessor.SetDocument(ReportDocument);

OutputProcessor.Output(DataCompositionProcessor);

Return DocumentReport;

EndFunction

If you wish, you can get areas of any other layout and also display them in this report, for example, we have a standard layout for generating a payment order and the header is very well created in it, then, so as not to do unnecessary work, we’ll just first get the layout, display the header, then we will generate and display our report on the access control system.

ABOUT unification

We can place our queries and objects in it, but unlike a connection, it simply adds tables to each other, that is, if we connect two identical tables, we will end up with one, and when combined, it will double, let’s look at a simple example :

We have tables:

Upon communication we will receive:

And when combined:

Let's now look at filling out columns in data sets (we'll skip some, as they are related to other tabs; we'll return to them in future articles):

- field, indicate the general name of the attribute;

­­- path, indicate the name of the details by which we will contact it in the access control system, for example, in Calculated fields;

- title, indicate the name of the attribute that will be displayed in the report;

- field limitation, indicate the availability of this requisite;

- restriction of details, indicate availability child elements, it is important that if the availability of the details is indicated, then the field itself will be available, perhaps this mechanics will be changed in future releases;

- expression by which the field representation is calculated, it’s convenient to use when we need to change the output of details a little, for example, we need after the name nomenclature was displayed stock, where it is located, then fill in the following: Item + “is in the warehouse” + Warehouse. I repeat that access to the details is carried out through the name indicated in the column path;

- expression ordering, a convenient mechanism for setting up report ordering, where the condition can be set manually, similar to the previous point, but as practice shows, this mechanism often does not work as we would like, and I advise you to use standard sorting;

- value type, indicates the type of value of the attribute; this must be filled in if you use the following field;

- available values, works only when full value type, open the form and in the column Meaning we indicate the element that needs to be changed, according to the type, it can be predefined objects or numeric, for example, details have simple values, in presentation We indicate what we need to change to, an example of a Boolean type:

- decor – standard setting field format, similar to the setting in managed forms, allows you to more accurately and beautifully customize the output of certain details.

Data set connections in 1C SKD

Here it is installed only left join, on a principle similar to connections in requests, in source of communication specify the main table for the connection, in receiver additional. IN expression source And expression receiver We indicate the details by which communication will take place. We'll look at the remaining columns in more detail when we look at the tab. Options. If there is no additional connection with parameters, then it is recommended to do the connection in the request, this will speed up the report.

  41
 I recently made a report with an indefinite number of columns. I didn’t want to tinker with the code, so I decided to do it on the access control system. There was no problem with this, it was necessary to stretch the result onto an arbitrary layout (your own header +...   27
 Even though CDS students come across this on the first or second day, it should be in the FAQ section. A simple example of programmatically outputting a report on a layout, using the default settings. //Get the diagram from...   18
 When generating reports on the access control system, by default all groupings are expanded, but sometimes it is necessary to show a report with collapsed groupings immediately after generation! This code in the report module allows you to collapse...   9
 What is required when developing reports is that for a user with limited rights, the report is generated completely without checking rights! Especially if RLS is configured. There are several ways to do this: 1. Install...

In this case, the Required checkbox determines whether the fields used in the source (set to FALSE) or in the destination (set to TRUE) are enabled and, based on this, adds the relationship to the layout.

For each of the data sets, you can set up a hierarchy check:

This option is used if the selection condition for a field needs to be processed in a non-standard way. A field can be specified with a data set from which data is obtained to check whether a link belongs to a hierarchy of a certain value.

Question 11.33 of exam 1C: Platform Professional. Is it acceptable to use multiple data sets?

1. Only when using datasets – object
2. Only when using datasets - query

The correct answer is the first one, it is acceptable in any case.

Question 11.10 of exam 1C: Platform Professional. When defining a relationship between two data sets in a data composition diagram

1. The connection option (left, full) is selected by the developer
2. The left join of the first set to the second is always used
3. The left join of the second set to the first is always used
4. A left join of the first set to the second is used, in some cases an inner join is established

The correct answer is fourth. Data sets are connected only by left or inner join.

Question 11.13 of exam 1C: Platform Professional. In what cases is it necessary to configure hierarchy checking on the "Data Sets" tab in the layout diagram designer window

1. If you need to prohibit receiving totals according to your own hierarchy that differs from the standard one
2. If you need to allow totals to be obtained according to your own hierarchy, different from the standard one
3. If you need to prohibit the installation of selection for entry into a group of your own hierarchy, different from the standard one
4. If you need to allow selection to be included in a group of your own hierarchy, different from the standard one

The correct answer is number four, description in the post.

Question 11.16 of exam 1C: Platform Professional. What kinds of connections between two sets of data can be implemented in a composition system?

1. All as in the query language: "All to All", "Left", "Right", "Inner" and "Full"
2. Only "All to All", "Left", "Right" and "Inner"
3. Only "Left" and "Inner"
4. Only "All to All", "Left", and "Inner"
5. "Left", "Right", "Inner" and "Full" only

The correct answer is fourth, according to the test result. In fact, the third one is correct.

Question 11.17 of exam 1C: Platform Professional. When setting up a relationship between two data sets, the "Required Relationship" flag is selected. In what case will the user's actions cause the connection to fail?

1. The connection of data from both sets will be implemented regardless of what settings the user has made
2. The list of selected fields shows only the fields in the left set
3. In the list of selected fields, only the fields in the right set are specified
4. In the list of selected fields, only the fields of the left set are indicated, the field of the right set is selected
5. In the list of selected fields, only the fields of the right set are indicated; the field of the left set is selected

The correct answer is the second, if there is only one set (and the source), then there is nothing to connect.

Question 11.39 of exam 1C: Platform Professional. How is external data transferred to a set - an object in a data composition system?

1. By means of the built-in language through the data composition processor
2. Using the built-in language via the layout builder
3. By means of the built-in language through the output processor of the data composition system

The correct answer is the first one, through the processor.