1c 8.3 data layout parameters. Create a new report

Attention! This is an introductory version of the lesson, the materials of which may be incomplete.

Log in as a student

Log in as a student to access school materials

Data composition system 1C 8.3 for beginners: we make the connection of data sets

• Write a report that displays customers and their favorite products. Each client has a favorite color, and each product has its own color - on the basis of these colors it is necessary to determine the "favorite" of the product. For example, if Andrey's favorite color is red, then one of his favorite foods will be tomatoes (they are red).
• Apply two datasets in the report. The first set is data from the "Clients" lookup table. The second is data from the table of the "Food" reference book.
• To carry out the relationship of these two sets so that only favorite products for each client remain in the report.

Create a new report

Open the "Gastronom" database in the configurator and create a new report through the main menu:

Document type - "External report":

In the form of a new report, specify the name "Lesson6" and click the "Open data composition schema" button:

We leave the default schema name:

Adding the first dataset

In the opened diagram, go to the "Datasets" tab and select "Add dataset - query" through the green plus sign:

We call the query constructor:

We indicate the table "Clients" and the fields that you want to get from the request:

Adding a second dataset

Add a second set of data:

Select it (DataSet2) and call the query constructor again:

We indicate the table of the "Food" reference book and the fields that are required to be obtained from the request:

The result is the following request text:

Please note that we now have two datasets in the report: Dataset1 and Dataset2. Each has its own request text and data.

Making names more descriptive

For clarity, let's rename Dataset1 to Clients and Dataset2 to Food.

Do this by double clicking on each of the sets:

We can use the data of each of the sets in our report. We will refer to this data through the fields.

At the moment, the "Clients" set has the following fields: "Name" and "FavoriteColor", and the "Food" set: "Name" and "Color".

We see that the names overlap and we get confused easily. So let's change the field names to be more descriptive.

Select the "Clients" set and change the field names as follows:

Then select the "Food" set and change the field names like this:

Calling the settings constructor

Finally, go to the Settings tab and hit the magic wand to invoke the settings constructor:

Report type - "List ...":

Select fields for the report from both sets:

See why it was so important to change the field names? At the stage of data composition settings, we do not see from which sets these fields are. We see only their names.

Checking the report

We save the report and generate it in user mode:

Yeah. Okay, but not really. A so-called cross-join of two sets has occurred (you should be familiar with this from the joins in queries that we studied in previous modules). Each record from the "Customers" table is associated with a record from the "Food" table.

But out of all these records, we need to leave only those in which the "FavoriteClientColor" field is equal to the "FoodColor" field:

We link two data sets

To do this, we will link two data sets (Clients and Food) in the fields FavoriteClientColor and FoodColor.

Go to the "Dataset Links" tab and click on the plus button to add a new link:

Set up the parameters as in the picture below:

Let me explain.

Communication source and destination. Well, everything is clear. We indicate the first set (Customers) and the second set (Food) data. I would like to draw your attention to the fact that communication will be carried out according to the principle external left connection(we went through it in the topic of requests in previous modules). Based on this, it is necessary to choose which set will be the source and which will be the receiver.

Source expression. We indicate here an expression or just a field from the source dataset (here we have specified the Customer's FavoriteColor field from the Clients set).

The expression is the receiver. We indicate here an expression or just a field from the receiver data set (here we indicated the Food Color field from the Food set).

Thus, this relationship will leave from the previous list only those lines for which the FavoriteClientColor field is equal to the FoodColor field.

Let's save the report and run it in user mode:

Fine!

I will make an explanation about the field " Communication condition", about which novice programmers so often beat their spears.

The link condition is an auxiliary field. Expressions can be written there involving fields only from the dataset specified in the link source.

In this case, for all lines from the link source, this expression (link condition) will be checked before linking. And if this expression is TRUE, then an attempt will be made to establish links of this row with rows from the communication receiver. If the expression is FALSE, then no such attempts will be made.

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:

1. Creation of a request. The request can be written manually or you can do without the program code using a user-friendly interface;
2. Customizing the report. Fields, totals, groupings, parameters, report design are selected;
3. 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:

1. Datasets - all queries involved in the generation of the report are listed here;
2. Dataset links - used to build links between different queries from the first tab;
3. 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;
4. 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;
5. 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;
6. 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;
7. Nested schemas. They are required when your report is to contain data from other reports;
8. 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.

One 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 a brief description of the idea and architecture of the ACS mechanism and its capabilities.

ACS is a mechanism based on the declarative description of reports. ACS is 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:

1. One or several queries were written in the 1C query language (SQL-like language, more on it below).
2. A code was written that transferred the results of executed queries into a 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.

The approach is straightforward, but not the most convenient - there is a minimum of visual settings, everything has to be programmed “hand-to-hand”. And one of the trump cards at that time of the completely new platform "1C: Enterprise 8" was the minimization in the applied solution of the amount of code that needs to be written manually, in particular, due to visual design. It would be logical to follow the same path in the mechanism for generating reports. This was done by developing a new mechanism - the Data Composition System.

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 applications without the need for 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 convenient work with temporary tables, the query language supports working with batch queries - thus, the creation of a temporary table and its use are placed in one query. A batch request is a sequence of requests separated by a semicolon (";"). The requests in the 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.

The request mechanism automatically modifies the request, taking into account the roles to which the user belongs, on whose behalf the request is made (i.e., the user will see only the data that he has the right to see) and functional options (i.e., in accordance with the one configured in the application solution functionality).

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 instance:

• SELECT. This sentence describes the fields that the user can select for display. After this keyword, the field aliases 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.

An example of using extensions:

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.
• Usual. 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.

Let's consider the process of creating a report in 1C 8.3 using the Data composition system. For example, let's take the following task: the report must enter data from the tabular section of the goods of documents Sales of goods and services for a certain period and for the selected organization. He must also group data by the Counterparty, Document Link and Nomenclature fields.

An example of the resulting report can be downloaded by.

Using the File-> New menu, add a new external report, give it a name and save it to disk. Let's create a data composition schema using the Open data composition schema button.

Writing a 1C request for ACS

After creating the layout diagram, we need to write a query that will collect data for the report. To do this, on the Datasets tab, create a Query dataset.

Now you can start writing a query, there is a special field for it in the dataset. You can write both manually and using the constructor (which is more convenient by an order of magnitude). In our report, the request is the simplest:

| CHOOSE | RealizationGoodsServicesTovary.Link, | RealizationGoodsServicesTovary.Slink.Organization, | RealizationGoodsServicesTovary.Slink.Contractor, | RealizationGoodsServicesTovary.Nomenclature, | RealizationGoodsServicesTovary.Summa, | RealizationGoodsServicesTovary.StavkaNDS, | RealizationGoodsServicesTovary.SummaNDS, | Realization of GoodsServicesGoods.Price | FROM | Document.RealizationGoodsServices.Tovary | HOW Implementation of GoodsServicesGoods | WHERE | RealizationGoodsServicesTovary.Link.Data | BETWEEN & Beginning of Period AND & End of Period | And RealizationGoodsServicesTovary.Link.Organization | = & Organization

Setting up dataset fields

After writing a request, ACS will automatically fill in the table with field settings.

I will briefly tell you about some of the settings:

Configuring Resources

Resource fields in the data composition system are fields whose values ​​are calculated based on the detailed records included in the grouping. In essence, resources are group or general totals of the report. In our case, the resources will be the Amount and VAT Amount fields. Resource totals can be calculated using ACS expression language functions, the simplest of which are Sum (), Average (), Maximum (), Minimum (), and Quantity (). To set the report resources, go to the Resources tab and drag the required report fields into the resource table. After that, you need to specify an expression (in our case, the Sum ()), you can also select the groupings for which you want to see the totals for this resource, this can be done in the Calculate by… column.

Please note that you cannot create groupings by the fields selected in the resources.

All parameters specified in the request will be displayed on the Layout scheme parameters tab. In our case, these are Beginning of Period, End of Period and Organization. Let's go through some of their settings:

1. Columns Name, Title and Type are filled in automatically and you should not change their values ​​unnecessarily;
2. A list of values ​​is available. If you want to pass a list to the parameter, you need to set this flag, otherwise only the first element of the list will get there.
3. Meaning. Here you can specify a default value for the parameter. In our example, we will select the Elements value for the Organization parameter (an empty link to the Organizations reference book).
4. Include in available fields. If you clear this flag, the parameter will not be visible in the settings: in the selected fields, selection.
5. Limited availability. The flag is responsible for the ability to set the parameter value in the ACS settings.

Setting up the structure of the report

Let's go to the Settings tab, here we can specify the groupings of the report, the fields displayed in the report, filters, sorts, etc. The statement of the task says that the report should group data by the fields: Account, Link and Nomenclature, we will indicate them one by one in the groupings area.

On the Selected fields tab, drag our resources (Amount, VAT Amount) from the available fields to the report fields.

Let's add filters by Counterparty and Nomenclature for the convenience of using the report. We will uncheck the flags of using the selection, the user will set them when required.

This completes the configuration of the report, now you can run it in enterprise mode.

Report generation

For a report on the ACS in 1C, it is not necessary to create a form, the layout system itself will generate it. Open the report in 1C Enterprise 8 mode.

Before generating it, press the Settings button and fill in the parameter values. You can also set the selection, if necessary, change groupings, sorts, etc.

See also the video on creating reports on the ACS:

When creating reports using the data composition system (ACS), it is convenient to display the report parameters (at least the most frequently used ones) on the form so as not to force the user to dig into the standard ACS settings window.

Let's consider how this can be done using conventional 1C forms. Let's make a reservation right away that this article will focus on the output of parameters to the form, i.e. the parameters themselves in the ACS must already be created.

There are two ways to display ACS parameters in the usual form:

• output of the parameter settings table in its entirety
• output of individual parameters in the form of input fields.

The parameter setting table displayed on the form is similar to the parameter setting table generated by the ACS. To display it on the form, you need to create a control TabularField, in which in the property Data point out Settings Composer.Settings.Data Parameters

In this case, the user will be shown all the ACS parameters for which the availability restriction is not set.

However, in some cases this method may not seem so beautiful and not very user-friendly. Let's consider how to display the ACS parameters on the form separately in the form of ordinary input fields.

1. Let's create the details of the report corresponding to the parameters. In our example, these will be the requisites Start Date, End Date, Organization... Let's set the corresponding data types for them.
2. Let's create an input field on the form for each of the parameters and link them through the property Data with the details of the report. For each input field, it would be nice to create an inscription next to the parameter name so that the user understands which parameter he is editing.
3. For each input field, you need to create a procedure for the event handler When it changes, which will set the value selected by the user to the ACS parameters. In the example, I created one procedure and assigned it to change events for all three input fields. That is, when one parameter is changed, all three are reset. From the point of view of performance, this is most likely not efficient, but since there are not many parameters, the difference is not noticeable. With a large number of parameters, it is worthwhile to output the setting of each in a separate procedure. So, the procedure code for the handler When it changes will look like this:

   Procedure ParametersOnChange (Item)

   ParameterDateStart = Settings Linker. Settings. Data Parameters. Elements. Find ("BeginningPeriod"); // Search is performed by the name of the ACS parameter
   ParameterDateStart. Usage = True; // The parameter will be used when selecting
   ParameterDateStart. Value = Start Date; // The parameter is assigned the value of the report attribute

   ParameterDateCon = Settings Linker. Settings. Data Parameters. Elements. Find ("EndPeriod");
   ParameterDateCon. Usage = True;
   ParameterDateCon. Value = EndDay (EndDate);

   ParameterOrg = Settings Linker. Settings. Data Parameters. Elements. Find ("Organization");
   ParameterOrg. Usage = True;
   ParameterOrg. Value = Organization;

   End of Procedure