1s scd value. Using characteristics in SCD. Place of poverty in an intelligent building

That's all. The report functionality now allows you to select fields for additional characteristics in the report (output fields, selections, 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 information base.

But if necessary, we can add characteristics fields, for example, to the selection before opening it. Let's look at a small example.

Programmatic work with characteristics

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

CurrentSettings = ComposerSettings. Settings; CollectionCollection = CurrentSettings. Selection. Elements; // Add selection by additional. to the item details "Main warehouse". [ Main warehouse] // Add selection by additional. to the nomenclature details "Country of placement" NovEl = CollectionSelections. Add(Type(" Selection ElementData Layout" ) ) ; NewEl.ComparisonType = DataCompositionComparisonType. Equals; NewEl.LeftValue = . [Country of posting]" ) ; NewEl. Usage = False ; // Add selection by additional. to the item details "Related counterparty" NovEl = CollectionSelections. Add(Type(" Selection ElementData Layout" ) ); NewEl.ComparisonView = DataCompositionComparisonView. Equals; NewEl.LeftValue = NewDataCompositionField("Organization . [Related Counterparty]" ) ; NewEl. Usage = False ;

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

Thus, we programmatically added a selection based on additional characteristics of the “Organizations” directory, despite the fact that these fields were not available in the ACS designer. Note the syntax for defining a data composition field.

namely the text “[Linked counterparty]”. If we write like this:

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

For additional properties and details that are not available in the ACS designer, when accessing programmatically, you must use the following syntax:

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

Conclusion

Using characteristics settings in ACS can significantly simplify the development of complex reports. Despite some shortcomings in the work, such as the lack of ability to configure selection by additional. properties in the designer, 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 did not consider all the capabilities of characteristics in ACS. Beyond the scope of the article there are such possibilities as: arbitrary definition of data sources, both for properties and for characteristic values, as well as selection by owner for all available characteristics in the information base, and much more. The topic is big, there is room to expand your circle of knowledge.

One of the most important areas of business software is reporting. The fate of a business, be it a report for 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 short description ideas 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 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

In the query designer, when it is called from the data source setup form, for the data composition schema. There is a “characteristics” tab, the use of which is not clearly described in the documentation. In this article I will try to explain how and why characteristics are used in ACS.

Typical configurations actively use the mechanism of properties and property values, available for almost any object. Primitively, in reference books, this mechanism was implemented in configurations 7.7. Now this mechanism is implemented using a plan of characteristics types and an information register, but the idea remains the same.

When I first encountered the need to use this mechanism in an access control scheme, I struggled for a very long time, organizing nested queries, joining them to the main selection and racking my brains over how to take into account the possibility of the emergence of new types of properties that did not exist at the time of report development. The entire mechanism of properties, being simple and logical from the user’s point of view, did not lend itself to any normal processing until I figured out the “Characteristics” tab.

The table on the tab is very capricious, either you enter the entire line correctly, or refuse to enter the line at all; the system will not allow you to leave an incompletely filled line “for later.”

So, let's get down to specifics. First column: Type– here we select the type of object to which the characteristics will be attached, for example “DirectoryLink.Nomenclature”

This means that it will now be possible to obtain property values ​​for all objects of the specified type.

Further in the next column Source of species we must set the property view source parameters. Possible options table m request, why do we need an option? request I'll tell you later, now let's select an item table.

In a collumn Types of characteristics we must select the infobase table in which the required types of characteristics are stored, in our example it will be “Plan of Types of Characteristics.Properties of Objects”.

Next, the values ​​available to us for selection in the columns Key field, Name field And Value type field, directly depend on the fields of the table we select. IN Key field we choose Link, V Name field – Performance(the user will see it as the name of the attribute), and in Type field respectively TypeValue.

Now let's move on to the source of values. Our source of values ​​will be the information register “ObjectPropertyValues”, so we select in the column Source of values – table, and in the column Attribute Values– “Information Register.Values ​​of Object Properties.” In columns An object, Property, Meaning, select the appropriate register fields An object, Property, Meaning.

It would seem that that's all. We go to the schema settings, add a grouping by products, and add a subordinate grouping, for example by Brands, we have such a property.

We expand the list of details of the Nomenclature group and... we don’t see any properties there:

The fact is that we are in the configurator, from where there is no access to the data. How to make the necessary settings? The most convenient way to do this is to use the data composition console, the one on the ITS disk, or the one included in the “Developer Tools” subsystem. But you can simply open the report settings in enterprise mode.

So, let's open the same setting, but in enterprise mode:

As you can see, we have added new “Details”, and the property “ Brand” outwardly does not differ from the usual directory details. And the property “ Product type” is in square brackets because the property representation contains a space.

However, we also have the property “ Type of agreement” which is linked to the directory “ Treaty” and has nothing to do with “ Nomenclature“. If not used in the setting “ Type of agreement” then everything will work correctly, but if you select it, then as a result it will turn out to be unfilled, because not a single item in the nomenclature has this property actually filled in. But how can you filter out unnecessary properties so that they don’t get under your feet?

To do this, we need to change the view source setting in the query designer, on the “Characteristics” tab. Remember, at the beginning of the article I promised to tell you why the view source type is needed request? Now is just such a case. Change the view source type to request. In the column of types of characteristics, click the “[...]” button and a new query designer window opens.

Enter the following query there:

CHOOSE
Object Properties.Ref.
Object Properties. Name + “(property)” AS Name,
Object Properties.TypeValues
FROM
Plan of Types of Characteristics. Properties of Objects AS Properties of Objects
WHERE
Object Properties. Purpose of Properties = VALUE (Plan of Types of Characteristics. Purpose of Properties of Object Categories. Directory_Nomenclature)
AND (NOT ObjectProperties.DeletionMark)
AND (NOT ObjectProperties.Category)

In columns Key field, Name field And Value type field, select the appropriate selection fields: Link, Name And TypeValue. It will turn out like this:

Now, when we move on to setting up the report, the picture in the list of Nomenclature details will change:

Now the product only has those properties that are assigned to it, moreover, they are now noticeably different from the usual details, thanks to the postscript (property), which we added to the property name in the request.

That's all, but many may be confused by the impossibility of setting it up in the configurator. There's really nothing wrong with it. It is enough to save the setting (or the entire circuit) to a file and restore it in the configurator.

The configurator will display details that it does not understand with red crosses as unavailable:

But this is no longer scary, because a report with such settings can be saved in the configuration and it will work correctly when opened by the user.