Data composition system 1s 8.3. Creating a simple report on the storage system

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 for 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

1. One or more queries were written in the 1C query language (SQL-like language, more about it below).
2. 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

Two languages

Query language

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 maximizing the reduction of efforts 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

Example:

The process of creating a report on the access control system

• 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

• 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

Let's look at 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 part of the goods of the Sales of goods and services documents for a certain period and for the selected organization. He should also group the data by the fields Account, Link to document and Item.

An example of the resulting report can be downloaded from.

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

Writing a 1C request for SKD

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

Now you can start writing a query; there is a special field for it in the data set. You can write either manually or using a constructor (which is much more convenient). In our report, the query is the simplest:

|SELECT | Sales of Goods and Services Goods. Link, | Sales of Goods and Services Goods. Link. Organization, | Sales of Goods and Services Goods. Link. Counterparty, | Sales of Goods and Services Goods. Nomenclature, | Sales of Goods and Services Goods. Amount, | Sales of Goods and Services Goods. VAT Rate, | Sales of Goods and Services Goods. Amount of VAT, | Sales of Goods and Services Goods. Price | FROM | Document. Sales of Goods and Services. Goods | HOW TO SELL PRODUCTS AND SERVICES PRODUCTS | WHERE | Sales of Goods and Services Goods. Link. Date | BETWEEN &Begin of Period AND &End of Period | And Sales of Goods and Services Goods. Link. Organization | = &Organization

Setting Dataset Fields

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

I'll briefly tell you about some settings:

Setting up resources

In the data composition system, resource fields mean fields whose values ​​are calculated based on detailed records included in the grouping. Essentially, resources are group or overall results of a report. In our case, the resources will be the Amount and VAT Amount fields. Totals for resources can be calculated using functions of the SKD expression language, the simplest of which are Sum(), Average(), Maximum(), Minimum() and Quantity(). To set report resources, you need to go to the Resources tab and drag the necessary report fields into the resources table. After this, you need to set an expression (in our case, 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 based on 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 the Beginning of the Period, the End of the Period and the Organization. Let's go through some of their settings:

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

Setting up the report structure

Let's go to the Settings tab, here we can specify report groupings, fields displayed in the report, selections, sortings, etc. The task conditions say that the report should group data by fields: Account, Link and Item, we will indicate them one after another in the grouping area.

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

Let's add selections by Contractor and Item for ease of use of the report. We will clear the flags for using selection; the user will set them when required.

This completes the report setup; you can now run it in enterprise mode.

Generating a report

For a report on the access control system 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 creating it, click the Settings button and fill in the parameter values. You can also set selection, if necessary, change groupings, sorting, etc.

Watch also the video on creating reports on the access control system:

1C 8.3 reports are a special metadata object designed to create a user-friendly presentation of data from 1C database tables.

Let's figure it out how to set up reports, what they are needed for, how external reports differ from built-in ones, and how reports differ from processing.

In order to create or customize a report, you need to know the built-in and . Whatever one may say, the basis for creating almost any report is the initial query that receives the data. We process this data and display it in a user-friendly form.

Reports are the main thing in any information system, so 1C paid close attention to the development of reports and created a large number of designers and mechanisms for creating them.

Let's look at the basic methods for creating reports

How to make a report in 1C using a layout

Creating using a layout is the most routine way. However, they often resort to it out of desperation, because... in this method, the developer completely writes each action in code, which means we can do anything. Other mechanisms cannot boast of this.

But in this case, all the items, menus and other “bows” will have to be entered manually, which is very labor-intensive.

Get 267 video lessons on 1C for free:

Creating a report using the output form designer

The output form designer is a mechanism built into the platform that makes report development easier. The designer is only available for regular forms.

The designer is given the request and all the necessary parameters for the future report, and it, in turn, generates modules and report forms. The mechanism is based on an object of the built-in language 1C 8.2 - Report Builder.

Creating a report using Generic Report

The universal report has great functionality, a convenient settings interface that is familiar to the user:

Reports based on the Data Composition System

This is the most progressive method of creating reports in 1C, recommended by 1C itself. appeared in the 1C platform in version 8.1.

SKD allows you to create simple reports without a line of programming at all, because it has a very flexible and functional constructor for creating a data composition scheme:

In all recent configurations, all reports are written using a data composition system.

What is the difference between external reports and built-in ones?

As it is written in the documentation - nothing. The external reporting mechanism was created in order to debug reports during development.

The only difference when developing a solution is that you cannot access an external report “by name”, unlike a built-in report.

How does a report differ from processing?

In fact, practically nothing. The main difference is the purpose of using the object: reports are needed to display information, and reports are needed to change information.

The main differences in properties: in reports, you can specify the Basic Data Composition Schema (DCS) and specify settings for saving report parameters.

One of the most convenient and unique development tools in 1C is the data composition system (DCS). Few information systems allow developers to create reports without writing code. This mechanism was developed to simplify and speed up the development of reporting forms and give more opportunities to users in working with the output data. The latter is very highly valued by advanced users, who thanks to this can independently customize the report to their own needs, without waiting for the actions of developers.

Creating a report in 1C via SKD

The process of developing a report using ACS can be divided into the following stages:

1. Creating a request. You can write a request manually or do without code using a convenient interface;
2. Setting up the report. Select fields, totals, groupings, parameters, report design;
3. After this, all we have to do is connect the resulting report to the configuration in any available way.

Despite the ability of users to customize reports on the access control system, they must be created through the configurator.

Let's look at an example of creating an external report on an access control system:

Now we go to 1C, open our report to make sure the actions taken are correct. All data is reflected, groupings can be collapsed and expanded. As you can see, the access control system allows you to receive full-fledged reports without writing code, with the exception of non-standard requirements. Considering that most reports have a similar structure, knowledge of the access control system will significantly reduce the time for developing these objects.

This mechanism has become very popular due to its support for extensive reporting capabilities. Moreover, not only developers, but also ordinary users can use them.

ACS capabilities

There are situations when we have made a report, and then a user comes and asks to make a small modification. For example, instead of product names, display article numbers. SKD allows users to make such modifications independently using the “More” - “Change option...” button.

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 task, the user must go to the “Fields” tab and change the “Nomenclature” field. This edit field opens by double-clicking and the “Select...” button becomes available.

The window that opens gives us the opportunity to select any value that will appear in the “Nomenclature” field. Some fields have a plus sign on the left - the developer has placed links in these fields, which means we can see their details. We open “Nomenclature” and see the article that we need. Select it and select it.

The window for changing report options contains many useful functions of the data composition system. For example, the user can independently change the order of groupings, add selection, or apply conditional design. We complete the editing and generate a report - as you can see, the entire product range is now displayed in the form of articles.

The SKD 1C:Enterprise 8.3 mechanism also has expanded functionality for developers. When developing the report, we used only 2 tabs - “Data sets” 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 what each of the tabs is for:

1. Data sets – all queries involved in generating the report are listed here;
2. Data set connections – used to build connections between different queries from the first tab;
3. Calculated fields – a list of added fields not from the request. Most often used in cases where, based on the values ​​of several fields, you need to get 1 value from the request;
4. Resources. In 1C, this is the name for the fields for which you need to know the results. Resources support various arithmetic operations - sum, quantity, maximum and others;
5. Options. They are used if to generate a report it is necessary for the user to enter certain data - date, divisions or nomenclature, for example;
6. Layouts. Designed for cases where users want to see a uniquely designed report. You can create a separate place for signatures or a new top part of the report - all this can be done here;
7. Nested diagrams. They are needed when your report must contain data from other reports;
8. Settings. The section declares the displayed fields, groupings, and configures the appearance of the report.

If you have any questions about setting up a report, please contact

Each organization, in order to make timely and correct management decisions, needs operational information about the availability of goods in warehouses, its cost, and sales. Trade organizations work with a large number of items and counterparties, and this requires good setup of analytical accounting and quick acquisition of the required information from its data. The article discusses the basic techniques for working with standard reports in the standard solution “1C: Trade Management 8” (edition 11), built on the basis of a data composition system, and provides useful practical recommendations that will be useful to both novice users and those who have switched to the new edition from previous editions.

For example, let's take the report

• Product availability analysis;

Settings.

In a collumn Type of comparison Nomenclature

• Equals
• Not equal
• On the list
• Not on the list
• In Group Nomenclature;
• Not in group Nomenclature;
• In a group from the list Nomenclature;
• Not in a group from the list Nomenclature.

Meaning

"Quick selections"

As the 1C:Enterprise 8 platform develops and the new version 8.2 appears, reports in systems become more flexible, and users have more and more opportunities to customize them to their requirements without the help of programmers.

New opportunities provided by the data composition system (DCS) allow you to experience the benefits of 1C:Enterprise 8 capabilities when creating reports like never before. And, despite the fact that the report interface has undergone significant changes (compared to edition 10.3 of the “Trade Management” configuration, hereinafter referred to as UT), the report settings are still available to the average user. Let's look at some basic techniques for working with them.

The first thing you can pay attention to is the quick selections. They allow you to display data in a report based on certain values ​​of report fields according to the user’s wishes.

For example, let's take the report Analysis of product availability in UT. Let us immediately note that the entire demonstration of the reporting capabilities in UT 11 will take place using the example of two reports:

• Product availability analysis;
• Revenue and cost of sales.

Selection by some fields can be done either directly in the report form or using a button Settings.

In a collumn Type of comparison the user can select different comparison conditions. For example, for the field Nomenclature You can select the following types of comparison:

• Equals- the report will be built only for the selected item;
• Not equal- the report will be generated for the entire product range, except for the selected item;
• On the list- the report will be based on a list of item items;
• Not on the list- the report will be built on the entire product range, except for selected items;
• In Group- the report will be built on the entire item located in the selected directory folder Nomenclature;
• Not in group- the report will be built on the entire product range, except for items located in the selected directory folder Nomenclature;
• In a group from the list- similar to comparison In the list, only folders in the directory are selected as list values Nomenclature;
• Not in a group from the list- similar to comparison Not in the list, only folders in the directory are selected as list values Nomenclature.

The checkbox to the left of the selection field indicates that selection for this field is “enabled,” i.e., is taken into account by the report.

Unchecked fields are not taken into account even if you have selected specific values ​​for those fields.

Depending on the selected type of comparison in the column Meaning a specific element or folder (group) of the directory, or a list of elements or folders is indicated.

“Quick selections” are available in all solutions on the 1C:Enterprise 8 platform.

An example of a simple change in report settings

To view/change report settings, you need to go to the menu All actions - Change option.

The settings window for the selected report option opens in front of us.

The report structure is displayed at the top of the window. In essence, this is a visual display of the groupings of rows and columns of the report, i.e. in what order the analytics data will be displayed in the report in rows and columns.

At the bottom of the window, information related to the report as a whole is displayed (if the top level is selected in the report structure Report), or to a specific grouping of rows or columns of the report (if a grouping at a lower level is selected), settings for displaying information and designing fields.

Example 1

Step 1. We need to remove the grouping by product characteristics, leaving only the grouping by product. To do this, at the top of the report settings window, click on the field Nomenclature, Characteristics. At the bottom of the window, go to the tab Groups.

Select the field Characteristic and click on the button Delete command panel.

Confirm changes to the report settings by clicking the button Finish editing in the lower left corner of the screen.

Step 2. After removing the characteristic, our task, according to the conditions of the example, will be to add a price group. In essence, this grouping should be at a level lower than the grouping by warehouse, but at a level higher than the grouping by item. Therefore, in the structure of the report we will highlight a grouping Stock.

By right-clicking on it, select the command from the context menu New group.

In the grouping field editing window that opens, select Nomenclature.Price group.

By clicking the button OK, we will see that a new grouping has been added to the report, subordinate to the grouping Stock.

Now let’s select the grouping by product and, without releasing the right mouse button, drag it inside (i.e. below) the grouping by price group. We will get the structure presented in Figure 1. The result of setting up the report is presented in Figure 2.

Rice. 1. The resulting report structure

Rice. 2. Result of report customization

Working with custom fields

Let's now take a closer look at the new options for customizing reports in the 1C: Trade Management 8 program.

In the form of editing the structure and settings of our report, let’s pay attention to the tab Custom fields.

Previously, we added fields to the report, the list of which was pre-specified by the developer. Using this tab, we can create our own fields that we need - Selection field or Expression field.

Example 2

Step 1. Let's open the report. Let's go to the form for setting up the report structure ( All actions -> Change option). Let’s delete in turn all the groups previously created in the report - to do this, select each of them and click on the button Delete command panel, or use the DEL key.

Just as in the previous example, let's add a grouping to the report by partner, and then by item. We already know how to do this, so it won’t take much time.

Step 2. Let's create a new custom field. Let's open the bookmark Custom fields and execute the command Add -> New Select Field.

Let's give our new field a name - Region in general.

Let's click on the button Add. In a new line, click on the button in the column Selection. In the selection editing form that appears, add selection by field Partner.Business region. Let's choose the type of comparison Equals, meaning - Moscow.

Let's click on the button OK, after which we will return to the custom field editing window. Next, you need to click on the button in the column Meaning and in the data type selection list that appears, select Line and write as value Moscow.

Let's repeat the operation. Let's add one more row to the table. This time for selection by field Partner.Business region choose the type of comparison Not equal.

Returning to the custom field editing window, in the column Meaning let's write Other regions(see Fig. 3).

Rice. 3. Editing a custom field

Let's click on the button OK. Our new field is ready.

Step 3. Let's add our new field to the report structure. Selecting the top level with the mouse Report in structure, add a new grouping. In props Field let's choose Region in general.

Let’s drag the grouping by partner and the subordinate grouping by item inside the newly created one with the mouse.

Let's generate a report and look at the result (shown in Fig. 4).

Rice. 4. Result of report generation

Selection and sorting

Let's return to the window for editing the report structure and settings and pay attention to the bookmarks Selection And Sorting.

If the root element is selected in the report structure ( Report), then the settings made on these tabs will apply to the entire report as a whole. If a report grouping is selected, the settings will affect only that grouping.

Example 3

As a result, the report Sales history Only those implementations where the manager is specified will be included. Ivanov Ivan Fedorovich.

Example 4

Save the changes made

If you change the report structure, you can always save it using the command All actions -> Save option.

In the form for saving an option, you can use the switch to select Save to existing report version(instead of one of the already available options), or Save new report version.