Since the overall duration of the lesson is quite long, you can go through the lesson in parts and pause after the development of each of the six reports included in the lesson.
Now it is time to learn the data composition system, which is a significant part of the 1C:Enterprise platform. In this lesson you will implement several reports for your configuration. We will explain the main features of the data composition system on the example of these reports.
As a rule, a report is based on a complex selection of data that is grouped and ordered as specified. The data composition system is a powerful and flexible tool covering all major report functionality, from obtaining data from various sources to displaying the data in a user-friendly manner.
In most scenarios the report source data is located in the database. You can use 1C:Enterprise query language for specifying the data sources for the data composition system and which data is retrieved from these sources.
On the report development stage you can define the default report settings, so that users can run the report right away. At the same time a user can change the report settings manually and then run the report. At that the data composition system generates another query and presents the result in a different manner, according to the user-specified settings.
At the beginning of the lesson we will provide general information about the 1C:Enterprise query language and the data composition system.
Then we will explain how to use the data composition system for solving practical tasks on the example of several reports.
1C:Enterprise supports two methods of accessing data stored in a database:
- Object method (used for reading and writing)
- Table method (used for reading)
The object method involves 1C:Enterprise script objects. You have encountered some of these objects in the previous lessons.
An important feature of object data access method is that when you call a 1C:Enterprise script object, you call a data set stored in the database as a whole.
For example, the DocumentObject.Services object contains the values of all attributes and tabular sections of the Services document.
The object method features object caching, ensures object integrity and execution of appropriate event handlers, and so on.
The table method involves database queries that are written in the query language. You can use this method to access specific database table fields.
The table method is intended for retrieving data from the database based on certain conditions, such as filters, grouping, ordering, merging several selections, calculation of totals, and so on. The table method is optimized for processing large volumes of data stored in the database, and for obtaining data that matches specific criteria.
Queries are based on the 1C:Enterprise script object named Query. It is intended for getting data stored in database fields in the form of selections based on the specified rules.
Query data sources
Queries obtain their source data from a set of tables. These tables provide data from actual database tables in a form that is convenient for analysis.
All the tables that can be accessed using the query language can be divided into two large groups: actual tables and virtual tables (fig. 13.1).
Fig. 13.1. Query tables
You can view the assortment of tables that can be accessed using queries along with their descriptions in the Syntax Assistant, in section Working with queries / Query tables.
Each actual table contains data from a single database table.
For example, the Catalog.Customers actual table corresponds to the Customers catalog, while the AccumulationRegister.BalanceOfMaterials table corresponds to the BalanceOfMaterials accumulation register.
Virtual tables are usually generated based on data available in multiple database tables. For example, AccumulationRegister.BalanceOfMaterials.BalanceAndTurnovers is a virtual table that is generated based on multiple tables of the BalanceOfMaterials register.
In some cases virtual tables can be generated based on a single actual table (for example, the Prices.SliceLast virtual table is generated based on the Prices information register table).
However, all the virtual tables share one common feature: they can be assigned a number of parameters that define which data is included in these tables. The assortment of parameters may vary for different virtual tables, it is defined by the data stored in the source database tables.
There are two actual table types: object (referential) and nonobject (nonreferential). Object (referential) tables store data of referential types: catalogs, documents, charts of characteristic types, and so on. Nonobject (nonreferential) tables store all the other data types: constants, registers, and so on.
The distinctive feature of object (referential) tables is the fact that they contain the Ref field, which is linked to the current record. You can obtain user object presentations from such tables as well. These tables can be hierarchical and their fields can include nested tables (tabular sections).
The algorithm that selects data from query source tables is defined using the query language.
A query text can include several parts:
- Query definition
- Query union
- Ordering query result
- Description of totals
The only mandatory part is the first one, the query definition. All the other parts are optional.
A query definition specifies data sources, selection fields, groupings, and so on.
A query union specifies how the results of several queries are merged.
Ordering query result specifies how query result rows are ordered.
Autoordering enables automatic ordering of query result rows.
Description of totals specifies which totals are calculated in the query and how the results are grouped.
Note that when the query language is used to describe data sources in the data composition system, the description of totals query part is not used because the data composition system calculates the totals on its own based on the settings provided by the developer or by the user.
The query language syntax structures are described in detail in the built-in Designer help.
To open the built-in help
- On the Help menu, click Help contents.
- In the help contents, open section 1C:Enterprise / Script / Working with queries.
Another source of information about query language syntax is chapter "Work with Queries" of the 1C:Enterprise Developer Guide.
We will discuss the query language in more detail later, during the creation of specific reports.
You will not create any queries manually. For the majority of reports developed using the data composition system, you can create a query using the query wizard.
Your goal for this lesson is to learn how to read and understand query text, so that you can edit queries in the future.
Data composition system
The data composition system is intended for creating custom reports in 1C:Enterprise system. It consists of several major parts.
A data composition schema stores the source data for report composition, which includes data sets and data processing methods (fig. 13.2).
Fig. 13.2. General data composition system flowchart
A developer creates a data composition schema, which includes the query text, data sets and connections between them, available fields, data access parameters, and also default composition settings, such as report structure or data appearance template.
For example, a composition schema can include a data set as shown in fig. 13.3.
Fig. 13.3. Sample composition schema (a data set and a query that uses the set)
The figure shows the data composition schema wizard window, which contains the data source, the query text, and the fields selected by the query.
When a user runs a report based on the data composition system, what they get is not simply a table of records that satisfy the query conditions. A composition system report has a complex hierarchical structure, which can include a variety of items: groupings, tables, charts, and more.
A user can edit the report structure or create an entirely new report structure. A user can also customize the selection used in the report and the appearance of report structure items, view details of each item, and more.
For example, a report structure can include a single table and a single chart, as shown in fig. 13.4.
Fig. 13.4. Sample report structure
In this case the generated report looks as shown in fig. 13.5.
Fig. 13.5. Sample report
The sample report contains customer and service data from the SalesTurnover accumulation register, which is displayed as a table and as a chart.
As we have mentioned earlier, the data composition system is an aggregate of multiple objects. When a user runs a report, data is transferred consecutively from one data composition system object to another until the resulting report (a document displayed to the user) is generated.
The interaction between the objects is based on the following algorithm:
- A developer creates a data composition schema and the default settings. Usually a single data composition schema can serve as a basis for a large variety of reports, while data composition settings (either created by a developer or modified by a user) define the report layout.
- The template composer creates a template based on the composition schema and available settings. This step is a preparation for report execution. A data composition template is a complete assignment for the composition processor. It includes the required queries, report area templates, and more.
- The data composition processor selects data from the infobase based on the composition template, then aggregates and arranges that data.
- The output processor processes the composition result and the user obtains the resulting spreadsheet document.
This algorithm can be presented as a flowchart (fig. 13.6).
Fig. 13.6. Flowchart of composition system operations