Creating a simple report on the storage system. Creating a simple report on SKD How to create a new report in 1s 8.3

In the “Reports” section there are many different reports for each type of activity. Basically they are enough for everyday accounting. But sometimes, to analyze a particular problem, it is necessary to dig deeper, even to the point of comparing, for example, entries in a document and in the registers that it affects. And there are times when standard reports are simply not enough.

For such in-depth data analysis or to create your own report in the 1C 8.3 program, there is a “Universal Report”. I am going to consider its capabilities in this article.

General description of the universal report in 1C 8.3

First, let's figure out where to find a universal report? If we go to the “Reports” menu and then click the “Universal Report” link, we will see this window:

Let's take a quick look at its controls.

We're done with the top line.

• Below, the most interesting button is “Show settings”. It's better to show this with an example.

Instructions for setting up a universal report 1C 8.3

Since we work in the 1C: “Enterprise Accounting 3.0” program, we are primarily interested in accounting registers. In configuration 3.0, only one is available to us - “accounting and tax accounting”. Let's choose it. Let's look at the turnover on the 10.01 “Materials” account.

Get 267 video lessons on 1C for free:

Select a period. I will have this throughout 2012. Next, click the “Show settings” button:

To get the names of materials, we will select a grouping by the 1st subconto. It is in it that the name is stored, or rather, a link to the nomenclature.

Go to the “Selections” tab:

Here we need to indicate that we only want to see the score 10.01.

If you wish, you can specify as many selection conditions as you like here.

Let's click the generate button and see what we got:

It can be seen that the report contains too many unnecessary columns. Such as currency accounting, tax accounting, etc. I don’t keep these records, and I want to remove these columns from the report.

We go back to the settings and immediately go to the “Indicators” tab:

We remove the checkboxes from those columns that we do not need to display.

On the “Generate” tab, you can specify the field by which the sorting will take place. For example, I want my materials to be listed in alphabetical order:

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 the 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.

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 “Dataset - 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. If you use Period, it will be applied to all tables equally and the query will not work correctly.

SKD 1C settings

On the Resources tab, we indicate those fields that will be summarized 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 for 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).

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 query. 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.

The number of possibilities incorporated into the ACS mechanism by developers is large, but many of them are used extremely rarely. Even experienced 1C programmers may not use some functions after years of work. To start working successfully in the access control system, it is enough to know the basic concepts and frequently used settings. In rare cases, documentation will come to the rescue.

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

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

In the very first version of the 1C:Enterprise 8 platform, version 8.0, reports were made like this:

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.

The approach is straightforward, but not the most convenient - there are minimal 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 application solution of the amount of code that had to be written manually, in particular, through visual design. It would be logical to follow the same path in the reporting mechanism. This was done by developing a new mechanism - the Data Composition System.

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.

Statement 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, designed to write 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 directory 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.
• etc.

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

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<= &ДатаКонца}
• etc.

Example of using extensions:

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 determines 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 you can control its layout). 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;
• 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 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:

• Quick access. The setting will be displayed directly at the top of the report window.
• Ordinary. The setting will be available through the “Settings” button.
• Inaccessible. 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.

We examined in some detail the settings of reports implemented on the basis of access control systems. Now let's look at more subtle and detailed settings for report options. The window for “advanced” settings of the report option is called up by the command “More” - “Other” - “Change report option”.

The window for changing the report version is divided into two parts:

1. Report structure.

2. Report settings.

The report option structure section is similar to the “Structure” tab of the standard report settings. The purpose and configuration of groupings is discussed in detail in Part 1 of the article.

The report variant structure table, in addition to the actual column with groupings, contains several additional columns:

The report option settings section gives the user ample opportunities to configure the report to suit their needs. It almost completely coincides with the standard report settings discussed in Part 1. Let’s look at all the tabs of the section and note the differences.

The settings section consists of the following tabs:

1. Parameters. Contains ACS parameters available to the user.

The SKD parameter is a value used to obtain report data. This can be a condition value for selecting or checking data, as well as an auxiliary value.

The parameter table is presented in the “Parameter” - “Value” format. If necessary, you can change the parameter values. Clicking the “Custom Settings Item Properties” button opens the custom settings of the element.

In this window, you can select whether the element will be included in the user settings (that is, visible to the user when setting up a report), set the element's presentation and editing mode (quick access in the report header, normal in the report settings, and inaccessible).

Custom settings item properties also have groupable fields, margins, selections, and conditional appearance elements.

2. Custom fields. Contains fields that are generated by the user himself based on the data selected by the report.

The user can add two types of fields:

• New selection field...
• New expression field...

Selection fields allow you to calculate a value based on a given condition. The selection field editing window contains a field title and a table in which the selection, value and presentation of the field are specified. Selection is a condition, depending on which the desired value will be substituted.

For example, let's calculate an estimate of the number of sales. We will assume that if less than 10 units of a product are sold, we sold a little, and if more than 10 units, we sold a lot. To do this, we will set 2 values ​​for the calculated field: the first will be with the selection “Number of goods Less than or equal to “10””, the second with the selection “Number of goods Greater than “10””.

Expression fields allow you to calculate a value using arbitrary algorithms. They can use the functions of the query language and the built-in 1C programming language. The expression field editing window contains two fields for expressions of detailed and summary records. Total records are groupings configured in the “Report Structure” area; they must use aggregate functions (“Sum”, “Minimum”, “Maximum”, “Quantity”).

For example, let's calculate the average discount percentage. The average discount percentage is calculated using the formula: [Sales amount without discount] - [Sales amount with discount] / [Sales amount without discount]. It is important to remember that the sales amount without a discount may be zero, so we use the SELECT operator to check. We get the following expressions:

· For detailed entries:

Choice

When [Sales amount without discount] = 0

Then 0

Otherwise [Sales amount without discount] - [Sales amount with discount] / [Sales amount without discount]

End

· For summary records:

Choice

When Amount([Sales amount without discount]) = 0

Then 0

Otherwise Sum([Sales amount without discount]) - Sum([Sales amount with discount]) / Sum([Sales amount without discount])

End

As mentioned earlier, in the expression of total records we use the aggregate function “Sum”.

3. Groupable fields. Contains fields by which the results of the report variant will be grouped. Grouped fields are configured separately for each grouping, but you can set general grouped fields for a report option if you select the “Report” root in the structure tree. You can add a field from the report result, a custom field, or select an auto field, then the system will select the fields automatically. This tab also allows you to change the order of grouped fields.

4. Fields. Contains the fields that will be output as a result of the report variant. Fields are configured separately for each grouping, but you can set common fields for a report option if you select the root “Report” in the structure tree. You can add a field from the report result, a custom field, or select an auto field, then the system will select the fields automatically. This tab also allows you to change the order of the fields.

Fields can be grouped to logically highlight any part of the report or to specify a special arrangement of columns. When adding a group, the “Location” column becomes active and allows you to select one of the location options:

• Auto - the system places fields automatically;
• Horizontal - fields are positioned horizontally;
• Vertical - fields are arranged vertically;
• In a separate column - fields are located in different columns;
• Together - the fields are located in one column.

5. Selection. Contains selections used in the report variant. Setting up selections was discussed in detail in part 1 of this article. Filters are configured separately for each grouping, but you can set general filters for a report option if you select the root “Report” in the structure tree.

6. Sorting. Contains the sort fields used in the report variant. Setting up sort fields was discussed in detail in Part 1 of this article. Sorting is configured separately for each grouping, but you can set general sorting fields for a report option if you select the root “Report” in the structure tree.

7. Conditional registration. Contains conditional design elements used in the report variant. Setting up conditional appearance was discussed in detail in Part 1 of this article. Conditional appearance is configured separately for each grouping, but you can set general elements of conditional appearance for a report option if you select the root “Report” in the structure tree.

8. Additional settings. Contains additional report design settings. Allows you to select the general appearance of the report, the location of fields, groupings, details, resources, totals, set chart settings, control the display of the title, parameters and selection, determine the position of resources and fix the header and grouping columns of the report version.

In conclusion, I would like to note that report settings can not only be saved as a report option, but also uploaded to a file (menu “More” - “Save settings”). To download, you must select “Load settings” and select the saved file. Thus, we can transfer report variant settings between different databases that have the same configuration.

Based on this, we can summarize that the user can not only independently customize the report to suit his needs, but also save his settings and use them in the future if necessary.