Using characteristics in skd. Using characteristics in skd Changing the order of columns

That's all. The report functionality now allows you to select fields of additional characteristics in the report (output fields, filters, etc.). But there is one BUT. We can use these fields in report settings only in 1C:Enterprise mode. In the configurator, we cannot see the characteristics fields, which is logical, because the characteristics are entered by the user and stored in the infobase.

But if there is a need, we can add characteristic fields, for example, to the selection before opening it. Let's consider a small example.

Programmatic work with characteristics

When opening the report form, execute the following program code:

CurrentSettings = SettingsComposer. Settings; Selections Collection = CurrentSettings. Selection. Elements; // Adding selection by add. requisites of the item "Main warehouse". [ Main warehouse] // Adding selection by add. requisites of the nomenclature "Country of location" NewEl = Selections Collection. Add(Type(" Data Layout Selection Element" ) ) ; NewEl.ComparisonType = DataCompositionComparisonType. Equal to; NewEl.LeftValue = . [ Location country]" ) ; NewEl. Usage = False ; // Adding selection by add. requisites of the nomenclature "Associated counterparty" NewEl = Selections Collection. Add(Type(" Data Layout Selection Element" ) ) ; NewEl.ComparisonType = DataCompositionComparisonType. Equals; NewEl.LeftValue = NewDataCompositionField(" Organization . [ Related counterparty]" ) ; NewEl. Usage = False ;

Then if we look at the report selection in the 1C:Enterprise mode, we will see the following picture:

Thus, we programmatically added selection by additional characteristics of the "Organizations" directory, despite the fact that these fields were not available in the ACS constructor. Notice the syntax for defining a data composition field.

namely, the text "[Related counterparty]". If we write like this:

then, when running the report, the ACS will incorrectly determine the layout fields. In the settings, the selection fields will be highlighted as incorrect:

For additional properties and attributes that are not available in the ACS constructor, the following syntax must be used when programmatically accessing:

Thus, we can set report settings even if the fields are not available in the ACS constructor.

Conclusion

Using the characteristics setting in the ACS makes it possible to significantly simplify the development of complex reports. Despite some shortcomings in the work, such as the inability to configure the selection for additional. properties in the constructor, etc., the characteristics mechanism can be considered a significant step in simplifying the development of reports in the 1C:Enterprise system.

In the article, we have considered far from all the possibilities of characteristics in ACS. The following possibilities remained outside the scope of the article: arbitrary definition of data sources, both for properties and for characteristic values, as well as selection by owner for all available characteristics in the infobase, and much more. The topic is large, there is much to expand the circle of their knowledge.

One of the most important areas of business software is reporting. How easy it is to customize an existing report to the changing needs of business (and legislation) or make a new one may depend (and not in a figurative sense!) The fate of the business, 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 right data from the system, present it in an understandable form, allowing the end user to reconfigure the 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 (ACS for short) 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 designed to build reports and display 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 row formatting, grouping, etc.).

A bit of 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 cannot 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 ACS was the flexibility and customization of reports, which is 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 participation of the end user, it means that the use of programming in them should be removed to a minimum (best of all, completely eliminated), and visual settings should be used to the maximum.

Formulation of the problem

Two languages

Query Language

It is easy to see analogues of standard SQL query sections - 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 the maximum reduction in efforts to develop applied solutions:

• Referencing fields through a dot. If the fields of any table are of a reference type (they store links to objects of another table), the developer can refer to them in the query text through ".", while the number of nesting levels of such links is not limited by the system (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 bypassed in an arbitrary order with summing up 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 application tasks without the need to write complex queries. Thus, a virtual table can provide data on the balance of goods in the context of periods at a certain 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 batch are executed one after the other. The result of executing a batch query, depending on the method used, will be either the result returned by the last query of the batch, or an array of the results of all the batch requests in the order in which the requests in the batch follow.
• Getting reference field views. Each object table (in which a directory or document is stored) has a virtual field - "Representation". This field contains a textual representation of the object and makes the work of the reporter 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 SKD. The expansion is carried out using special syntax instructions enclosed in curly braces and placed directly in the request text. With the help of extensions, the developer determines what operations the end user will be able to perform by 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, which will be available for customization, are listed separated by commas. Example: (SELECT Nomenclature, Warehouse)
• WHERE. Describes the fields on which the user can apply selection. This proposal uses table fields. The use of select list field aliases is not allowed. Each union part can contain its own WHERE element. Examples: (WHERE Nomenclature.*, Warehouse ), (WHERE Document.Date >= &StartDate, Document.Date<= &ДатаКонца}
• and etc.

Data Composition Expression Language

Example:

The process of creating a report on the ACS

• request text with data composition system instructions;
• description of several data sets;
• a detailed description of the available fields;
• description of relationships between several 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 compose a query of arbitrary complexity:

The result of running the query builder will be the text of the query (in the 1C:Enterprise query language). This text can be manually adjusted if necessary:

There can be several data sets in a data composition scheme, data sets 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 eventually translated into a dialect of SQL specific to the DBMS that the application is directly working with. 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 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 data composition scheme level, 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 the values ​​of calculated fields for each record, whose formulas are written in 1C language.

Adding Custom Fields

You can add any number of tables and charts to the report:

Report Designer

Report at run time

With the help of ACS, the user can add complex selections to the report (which will be added to the query in the right places), conditional design (allowing you to format differently - font, color, etc. - output fields depending on their values) and much more .

Briefly describe the process of building and generating a report as follows:

• The developer at design time with the help of the designer (or at runtime with the help of code) defines the data layout scheme:
  • Text of request/requests
  • Description of calculated fields
  • Relationships between queries (if there are more than one)
  • Report Options
  • Default settings
  • Etc.
• The above settings are saved in the layout
• User opens a report
  • Possibly makes additional settings (for example, changes parameter values)
  • Presses the "Generate" button
• User settings apply to the data composition scheme defined by the developer.
• An intermediate layout of the data composition is formed, containing instructions on where to get the data from. In particular, queries specified in the layout are corrected. So, 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 used in calculated field formulas are added to the query.
• The data composition processor is included in the case. The layout processor executes queries, links data sets, calculates the values ​​of calculated fields and resources, performs grouping. In a word, it does all the calculations that were not performed at the DBMS level.
• The data output processor launches a query for execution and outputs the received data to a spreadsheet document, chart, etc.

The process of generating a report by the ACS mechanism

We try to minimize the amount of reporting 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.

User 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