Report Builder and Query Builder working with a custom data source. Report Builder and Query Builder working with a custom data source Saving and restoring builder settings

When developing reports, sometimes you need to display a report for which data cannot be obtained using a query language. This situation may arise, for example, if some complex algorithm is used to calculate data, or if the data for outputting the report is not obtained from information base, and, for example, from an external file. The Report Builder provides the ability to display a report from an arbitrary data source.
The following can be used as a data source for report output:

Value Table,
Request Result,
Area of Tabular Document Cells,
Tabular part,
Accumulation RegisterRecord Set,
Information RegisterRecordSet,
Accounting RegisterSet of Records,
Calculation RegisterSet of Records.

In order for the report builder to display a report for an arbitrary data source, it is enough to set the description of the data source in the DataSource builder property. The report builder property DataSource can contain a value of type DataSourceDescription. The DataSourceDescription object contains the data source itself and also contains a description of the columns of the data source. Each data source column description contains:

Name - contains the name of the column in the data source,
DataPath - contains a description of the dependence of the columns on each other. A column whose data path is obtained through a dot from the data path of another field is considered an attribute of the other column. If a field contains the word Representation separated by a dot, then this field is considered a representation for the column from the path to the data of which the representation is obtained. Examples. If the "Nomenclature" column has a path to the "Nomenclature" data, and the "Code" column has a path to the "Nomenclature.Code" data, then this column will be considered an attribute of the "Nomenclature" column. The column with the path to the data "Nomenclature.Representation" will be considered a representation for the column "Nomenclature",
Field - a sign that this column can be used as a report field,
Order - a sign that ordering is possible for a given column,
Selection is a sign that it is possible to impose selection on a given column,
Dimension is a sign that this column can be used as a report grouping,
Total - a line containing an expression for calculating the total. To the report builder, the expression of this line corresponds to the expression for calculating the total used in the query language,

When you set the data source to the DataSourceDescription object, column descriptions are created and filled in automatically.

The DataSourceDescription object has a constructor, as a parameter of which the data source for which a description is being created can be passed, while the DataSource property will be filled with the passed data source, column descriptions will be filled with information about the columns from the data source.

An example of setting a data source for the report builder:

ReportBuilder.DataSource = NewDataSourceDescription(ValueTableResult);

Further work with the report builder is no different from working with the report builder in query mode: the report builder provides its full functionality, with the exception of displaying hierarchical totals. In addition, when working with a custom data source, you cannot obtain a query from the report builder that will be used to obtain data from the infobase.

The work of the query builder with a custom data source is identical to the work with a custom source of the report builder.

1C programming consists of more than just writing a program. 1C is an ingot of user actions and data with which he works.

The data is stored in a database. 1C queries are a way to retrieve data from a database in order to show it to the user in a form or to process it.

The fundamental part of the report is the 1C request. When SKD report is the bulk of the report.

Sit down. Take a breath. Calm down. Now I will tell you the news.

To program in 1C, it is not enough to know the 1C programming language. You also need to know the 1C query language.

The 1C query language is a completely separate language that allows us to specify what data we need to get from the database.

He is also bilingual - that is, you can write in Russian or English. It is extremely similar to the SQL query language and those who know it can relax.

How 1C Requests are used

When a user launches 1C in Enterprise mode, there is not a single gram of data in the running client. Therefore, when you need to open a directory, 1C requests data from the database, that is, it makes a 1C request.

1C queries are:

Automatic queries 1C
Generated automatically by the system. You have created a document list form. Added a column. This means that when you open this form in Enterprise mode, there will be a query and the data for this column will be requested.
Semi-automatic queries 1C
There are many methods (functions) in the 1C language, when accessed, a query is made to the database. For example.GetObject()
Manual 1C queries (written by the programmer specifically as a query)
You can write a 1C request yourself in code and execute it.

Creating and executing 1C queries

A 1C request is the actual text of the request in the 1C request language.
The text can be written with pens. That is, take it and write it (if you know this language).

Since 1C promotes the concept visual programming, where much or almost everything can be done without writing code by hand - there is a special Query Constructor object, which allows you to draw the query text without knowing the query language. However, miracles do not happen - for this you need to know how to work with the constructor.

Once the text of the 1C request is ready, it needs to be executed. For this purpose there is an object in the 1C code Request(). Here's an example:

As you can see in the example, after executing the 1C request, the result comes to us and we must process it. The result is one or several rows of the table (in a special form).

The result can be uploaded to a regular table:
Fetch = Query.Run().Unload(); //Result – table of values

Or just go around line by line.
Select = Query.Run().Select();
While Select.Next() Loop
//Do something with the query results
EndCycle;

Working with 1C requests

Basic principles of 1C queries

Basic principles of constructing a 1C request –
SELECT List of Fields FROM Table Title WHERE Conditions

An example of constructing such a 1C request:

CHOOSE
//list of fields to select
Link,
Name,
Code
FROM
//name of the table from which we select data
//list of tables is a list of objects in the configurator window
Directory.Nomenclature
WHERE
//indicate selection
Product Type = &Service //selection by external value
Or Service // “Service” attribute of type Boolean, selection by value True
SORT BY
//Sorting
Name

List of 1C tables

You can see the table names in the configurator window. You just need to write “Directory” instead of “Directories”, for example “Directory.Nomenclature” or “Document.Sales of Goods and Services” or “Register of Accumulation.Sales”.

There are additional tables (virtual) for registers that allow you to get the final numbers.

Information Register.RegisterName.Last Slice(&Date) – 1C request from the information register, if it is periodic, for a specific date

Accumulation Register.Register Name.Balances(&Date) – 1C request from the register of balances for a specific date

Accumulation Register.Register Name.Turnover (&Start Date, &End Date) – 1C request from the turnover register for the period from the start date to the end date.

Additional principles

When we request a list of some data, the basic principles work. But we can also request numbers and the request can count them for us (add them, for example).

CHOOSE
//Quantity(FieldName) – counts the quantity
//Field AS OtherName – renames the field
Quantity (Link) AS Quantity of Documents Posted
FROM

WHERE
Conducted

This 1C request will return us the total number of documents. However, every document has an Organization field. Let’s say we want to count the number of documents for each organization using a 1C query.

CHOOSE
//just a document field
Organization,
//count the quantity
Quantity(Link) AS QuantityBy Organizations
FROM
Document. Sales of Goods and Services
WHERE
Conducted
GROUP BY

Organization

This 1C request will return us the number of documents for each organization (also called “by organization”).

Let us additionally calculate the amount of these documents using a 1C request:

CHOOSE
//just a document field
Organization,
//count the quantity

//count the amount

FROM
Document. Sales of Goods and Services
WHERE
Conducted
GROUP BY
//must be used if the list of fields has a count() function and one or more fields at the same time - then you need to group by these fields
Organization

This 1C request will also return the amount of documents to us.

CHOOSE
//just a document field
Organization,
//count the quantity
Quantity(Link) AS QuantityBy Organizations,
//count the amount
Amount(DocumentAmount) AS Amount
FROM
Document. Sales of Goods and Services
WHERE
Conducted
GROUP BY
//must be used if the list of fields has a count() function and one or more fields at the same time - then you need to group by these fields
Organization
SOFTWARE RESULTS General

The 1C query language is extensive and complex, and we will not consider all its capabilities in one lesson - read our next lessons.

Briefly about additional features 1C query language:

Joining data from multiple tables
Nested Queries
Batch request
Creating your own virtual tables
Query from value table
Using built-in functions for getting values and manipulating values.

1C Query Builder

In order not to write the request text by hand, there is a 1C request designer. Just right-click anywhere in the module and select 1C Query Designer.

Select the desired table on the left in the 1C query designer and drag it to the right.

Select the required fields from the table in the 1C query designer and drag to the right. If you would like not only to select a field, but to apply some kind of summation function to it, after dragging, click on the field twice with the mouse. On the Grouping tab, you will then need to select (drag) the required fields for grouping.

On the Conditions tab in the 1C query designer, you can select the necessary selections in the same way (by dragging the fields by which you will make the selection). Be sure to select the correct condition.

On the Order tab, sorting is indicated. On the Results tab – summing up the results.

Using the 1C query designer, you can study any existing query. To do this, right-click on the text of an existing request and also select 1C query designer - and the request will be opened in the 1C query designer.

The query language in 1C 8 is a simplified analogue of the well-known “structured programming language” (as it is more often called, SQL). But in 1C it is used only for reading data; an object data model is used to change data.

Another interesting difference is the Russian syntax. Although in fact you can use English-language constructions.

Example request:

CHOOSE
Banks.Name,
Banks.CorrAccount
FROM
Directory.Banks HOW Banks

This request will allow us to see information about the name and correspondent account of all banks existing in the database.

The query language is the simplest and effective method obtaining information. As can be seen from the example above, in the query language you need to use metadata names (this is a list of system objects that make up the configuration, i.e. directories, documents, registers, etc.).

Description of query language constructs

Query structure

To obtain data, it is enough to use the “SELECT” and “FROM” constructions. The simplest request looks like this:

SELECT * FROM Directories.Nomenclature

Where “*” means selecting all fields of the table, and Directories.Nomenclature – the name of the table in the database.

Let's look at a more complex and general example:

CHOOSE
<ИмяПоля1>HOW<ПредставлениеПоля1>,
Sum(<ИмяПоля2>) HOW<ПредставлениеПоля2>
FROM
<ИмяТаблицы1>HOW<ПредставлениеТаблицы1>
<ТипСоединения>COMPOUND<ИмяТаблицы2>HOW<ПредставлениеТаблицы2>
BY<УсловиеСоединениеТаблиц>

WHERE
<УсловиеОтбораДанных>

GROUP BY
<ИмяПоля1>

SORT BY
<ИмяПоля1>

RESULTS
<ИмяПоля2>
BY
<ИмяПоля1>

IN this request we select the data of the fields “FieldName1” and “FieldName1” from the tables “TableName1” and “TableName”, assign synonyms to the fields using the “HOW” operator, and connect them using a certain condition “TableConnectionCondition”.

From the received data, we select only data that meets the condition from “WHERE” “Data Selection Condition”. Next, we group the request by the field “Field Name1”, while summing “Field Name2”. We create totals for the field “Field Name1” and the final field “Field Name2”.

The last step is to sort the request using the ORDER BY construct.

General designs

Let's look at the general structures of the 1C 8.2 query language.

FIRSTn

Using this operator, you can get the n number of first records. The order of the records is determined by the order in the query.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks
SORT BY
Banks.Name

The request will receive the first 100 entries of the “Banks” directory, sorted alphabetically.

ALLOWED

This design is relevant for working with the mechanism. The essence of the mechanism is to restrict reading (and other actions) to users for specific records in a database table, and not the table as a whole.

If a user tries to use a query to read records that are inaccessible to him, he will receive an error message. To avoid this, you should use the “ALLOWED” construction, i.e. the request will read only records that are allowed to it.

SELECT ALLOWED
Repository of Additional Information. Link
FROM
Directory.Repository of Additional Information

VARIOUS

Using “DIFFERENT” will prevent duplicate lines from entering the 1C query result. Duplication means that all request fields match.

SELECT FIRST 100
Banks.Name,
Banks. Code AS BIC
FROM
Directory.Banks HOW Banks

EmptyTable

This construction is used very rarely to combine queries. When joining, you may need to specify an empty nested table in one of the tables. The “EmptyTable” operator is just right for this.

Example from 1C 8 help:

SELECT Link.Number, EMPTY TABLE.(No., Item, Quantity) AS Composition
FROM Document.Expense Invoice
COMBINE EVERYTHING
SELECT Link.Number, Contents.(LineNumber, Product, Quantity)
FROM Document.Invoice Document.Invoice.Composition.*

ISNULL

A very useful feature that allows you to avoid many mistakes. YesNULL() allows you to replace the NULL value with the desired one. Very often used in checking for the presence of a value in joined tables, for example:

CHOOSE
Nomenclature Ref. Link,
IsNULL(Item Remaining.QuantityRemaining,0) AS QuantityRemaining
FROM

Can be used in other ways. For example, if for each row it is not known in which table the value exists:

ISNULL(InvoiceReceived.Date, InvoiceIssued.Date)

HOW is an operator that allows us to assign a name (synonym) to a table or field. We saw an example of use above.

These constructions are very similar - they allow you to get a string representation of the desired value. The only difference is that REPRESENTATION converts any values to a string type, while REPRESENTATIONREF converts only reference values. REFERENCE REPRESENTATION is recommended to be used in data composition system queries for optimization, unless, of course, the reference data field is planned to be used in selections.

CHOOSE
View(Link), //string, for example “Advance report No. 123 dated 10/10/2015
View(DeletionMark) AS DeleteMarkText, //string, “Yes” or “No”
ViewReferences(DeletionMark) AS DeleteMarkBoolean //boolean, True or False
FROM
Document.Advance Report

EXPRESS

Express allows you to convert field values to the desired data type. You can convert a value to either a primitive type or a reference type.

Express for a reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS(TableCost.Subconto1 AS Directory.Cost Items).Type of ActivityForTaxAccountingCosts

For primitive types, this function is often used to limit the number of characters in fields of unlimited length (such fields cannot be compared with). To avoid the error " Invalid parameters in comparison operation. You can't compare fields
unlimited length and fields of incompatible types", you need to express such fields as follows:

EXPRESS(Comment AS Line(150))

DIFFERENCEDATE

Get 267 video lessons on 1C for free:

An example of using IS NULL in a 1C request:

CHOOSE FROM
Ref
LEFT CONNECTION RegisterAccumulations.ProductsInWarehouses.Remaining AS Product Remaining
Software NomenclatureRef.Link = Sold GoodsCommitteesRemains.Nomenclature
WHERE NOT Remaining Products. QuantityRemaining IS NULL

The type of data in a query can be determined by using the TYPE() and VALUETYPE() functions, or by using the logical REFERENCE operator. The two functions are similar.

Predefined values

In addition to using passed parameters in queries in the 1C query language, you can use predefined values or . For example, transfers, predefined directories, charts of accounts, and so on. For this, the “Value()” construct is used.

Usage example:

WHERE Nomenclature.Type of Nomenclature = Value(Directory.Types of Nomenclature.Product)

WHERE Counterparties.Type of Contact Information = Value(Enumeration.Types of Contact Information.Phone)

WHERE Account Balances.Accounting Account = Value(Chart of Accounts.Profit.ProfitsLoss)

Connections

There are 4 types of connections: LEFT, RIGHT, COMPLETE, INTERNAL.

LEFT and RIGHT CONNECTION

Joins are used to link two tables based on a specific condition. Feature when LEFT JOIN is that we take the first specified table in its entirety and conditionally bind the second table. The fields of the second table that could not be bound by condition are filled with the value NULL.

For example:

It will return the entire table of Counterparties and fill in the “Bank” field only in those places where the condition “Counterparties.Name = Banks.Name” will be met. If the condition is not met, the Bank field will be set to NULL.

RIGHT JOIN in 1C language absolutely similar LEFT connection, with the exception of one difference - in RIGHT OF CONNECTION The “main” table is the second, not the first.

FULL CONNECTION

FULL CONNECTION differs from left and right in that it displays all records from two tables and connects only those that it can connect by condition.

For example:

FROM

FULL CONNECTION
Directory.Banks HOW Banks

BY

The query language will return both tables completely only if the condition to join the records is met. Unlike a left/right join, it is possible for NULL to appear in two fields.

INNER JOIN

INNER JOIN differs from full in that it displays only those records that could be connected according to a given condition.

For example:

FROM
Directory. Counterparties AS Clients

INNER JOIN
Directory.Banks HOW Banks

BY
Clients.Name = Banks.Name

This query will return only rows in which the bank and counterparty have the same name.

Associations

The JOIN and JOIN ALL constructs combine two results into one. Those. the result of performing two will be “merged” into one, common one.

That is, the system works exactly the same as regular ones, only for a temporary table.

How to use INDEX BY

However, one point should be taken into account. Building an index on a temporary table also takes time to complete. Therefore, it is advisable to use the “ ” construction only if it is known for sure that there will be more than 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance of indexed fields does not compensate for the time it takes to build the index.

CHOOSE
Currency rates Latest cross-section. Currency AS Currency,
Currency rates Latest cross-section.
PUT Currency Rates
FROM
Information Register.Currency Rates.Last Slice(&Period,) AS Currency RatesLast Slice
INDEX BY
Currency
;
CHOOSE
PricesNomenclature.Nomenclature,
PricesNomenclatures.Price,
PricesNomenclatures.Currency,
Currency rates.Rate
FROM
Information Register.Nomenclature Prices.Last Slice(&Period,
Nomenclature B (&Nomenclature) AND PriceType = &PriceType) AS PriceNomenclature
LEFT JOIN Currency Rates AS Currency Rates
Software PricesNomenclatures.Currency = Currency Rates.Currency

Grouping

The 1C query language allows you to use special aggregate functions when grouping query results. Grouping can also be used without aggregate functions to “eliminate” duplicates.

The following functions exist:

Amount, Quantity, Number of different, Maximum, Minimum, Average.

Example #1:

CHOOSE
Sales of Goods and Services Goods. Nomenclature,
SUM(Sales of GoodsServicesGoods.Quantity) AS Quantity,
SUM(Sales of GoodsServicesGoods.Amount) AS Amount
FROM

GROUP BY
Sales of Goods and Services Goods. Nomenclature

The request receives all lines with goods and summarizes them by quantity and amounts by item.

Example No. 2

CHOOSE
Banks.Code,
QUANTITY(DIFFERENT Banks.Link) AS Number Of Duplicates
FROM
Directory.Banks HOW Banks
GROUP BY
Banks.Code

This example will display a list of BICs in the “Banks” directory and show how many duplicates exist for each of them.

Results

Results are a way to obtain data from a system with a hierarchical structure. Aggregate functions can be used for summary fields, just as for groupings.

One of the most popular ways to use results in practice is batch write-off of goods.

CHOOSE

FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
SORT BY

RESULTS
SUM(Quantity),
SUM(Sum)
BY
Nomenclature

The result of the query will be the following hierarchical:

General results

If you need to get totals for all “totals”, use the “GENERAL” operator.

CHOOSE
Sales of Goods and Services Goods. Nomenclature AS Nomenclature,
Sales of Goods and Services Goods. Link AS Document,
Sales of Goods and Services Goods. Quantity AS Quantity,
Sales of Goods and Services Goods. Amount AS Amount
FROM
Document. Sales of Goods and Services. Goods HOW to Sale of Goods and Services Goods
SORT BY
Sales of Goods and Services Goods. Link. Date
RESULTS
SUM(Quantity),
SUM(Sum)
BY
ARE COMMON,
Nomenclature

As a result of executing the request, we get the following result:

In which 1 level of grouping is the aggregation of all necessary fields.

Arranging

The ORDER BY operator is used to sort the result of a query.

Sorting for primitive types (string, number, boolean) follows the usual rules. For fields of reference types, sorting occurs according to the internal representation of the reference ( unique identificator), and not by code or by link presentation.

CHOOSE

FROM
Directory.Nomenclature AS Nomenclature
SORT BY
Name

The request will display a list of names in the nomenclature directory, sorted alphabetically.

Auto-order

The result of a query without sorting is a chaotically presented set of rows. 1C platform developers do not guarantee that rows will be output in the same sequence when executing identical queries.

If you need to display table records in a constant order, you must use the Auto-Order construct.

CHOOSE
Nomenclature.Name AS Name
FROM
Directory.Nomenclature AS Nomenclature
AUTO ORDER

Virtual tables

Virtual tables in 1C are unique feature 1C query language, which is not found in other similar syntaxes. Virtual table – quick way obtaining profile information from registers.

Each register type has its own set of virtual tables, which may differ depending on the register settings.

cut of the first;
cut of the latter.

leftovers;
revolutions;
balances and turnover.

movements from subconto;
revolutions;
speed Dt Kt;
leftovers;
balances and turnover
subconto.

base;
graph data;
actual period of validity.

For the solution developer, the data is taken from one (virtual) table, but in fact the 1C platform takes it from many tables, transforming them into the required form.

CHOOSE
Products in Warehouses Remains and Turnover. Nomenclature,
ProductsInWarehousesRemainingAndTurnover.QuantityInitialRemaining,
ProductsInWarehousesRemainsAndTurnover.QuantityTurnover,
GoodsInWarehousesRemainsAndTurnover.QuantityIncoming,
ProductsIn WarehousesRemainsAndTurnover.QuantityConsumption,
ProductsInWarehousesRemainingsAndTurnover.QuantityFinalRemaining
FROM
RegisterAccumulations.GoodsInWarehouses.RemainsAndTurnover AS GoodsInWarehousesRemainsAndTurnover

This query allows you to quickly retrieve a large amount of data.

Virtual Table Options

Very important aspect working with virtual tables - using parameters. Virtual table parameters – specialized parameters for selection and configuration.

For such tables, it is considered incorrect to use selection in the “WHERE” construction. In addition to the fact that the query becomes suboptimal, it is possible to receive incorrect data.

An example of using these parameters:

Register of Accumulations. Goods in Warehouses. Balances and Turnovers (& Beginning of the Period, & End of the Period, Month, Movements and Borders of the Period, Nomenclature = & Required Nomenclature)

Algorithm for virtual tables

For example, the most used virtual table of the “Remains” type stores data from two physical tables – balances and movements.

When using a virtual table, the system performs the following manipulations:

We get the closest calculated value in terms of date and measurements in the totals table.
We “add” the amount from the movement table to the amount from the totals table.

Such simple actions can significantly improve the performance of the system as a whole.

Using the Query Builder

Query Builder– a tool built into the 1C Enterprise system that greatly facilitates the development of database queries.

The query constructor is quite simple and intuitive clear interface. Nevertheless, let's look at using the query constructor in more detail.

The query text constructor is launched from the context menu (right mouse button) in the desired place in the program code.

Description of the 1C request constructor

Let's look at each tab of the designer in more detail. The exception is the Builder tab, which is a topic for another discussion.

Tables and Fields tab

This tab specifies the data source and fields that need to be displayed in the report. In essence, the constructions SELECT.. FROM are described here.

The source can be a physical database table, a virtual register table, temporary tables, nested queries, etc.

In the context menu of virtual tables, you can set virtual table parameters:

Connections tab

The tab is used to describe connections of several tables and creates constructions with the word CONNECTION.

Grouping tab

On this tab, the system allows you to group and summarize the required fields of the table result. Describes the use of the constructions GROUP BY, SUM, MINIMUM, AVERAGE, MAXIMUM, QUANTITY, NUMBER OF DIFFERENT.

Conditions tab

Responsible for everything that comes in the request text after the WHERE construction, i.e. for all the conditions imposed on the received data.

Advanced tab

Tab Additionally replete with all sorts of parameters that are very important. Let's look at each of the properties.

Grouping Selecting records:

First N– a parameter that returns only N records to the query (the FIRST operator)
No duplicates– ensures the uniqueness of received records (DIFFERENT operator)
Allowed– allows you to select only those records that the system allows you to select taking into account (ALLOWED construction)

Grouping Request type determines what type of query will be: retrieving data, creating a temporary table, or destroying a temporary table.

Below there is a flag Lock received data for later modification. It allows you to enable the ability to set data locking, which ensures the safety of data from the moment it is read until it is changed (relevant only for Automatic mode interlocks, design TO CHANGE).

Joins/Aliases Tab

On this tab of the query designer, you can set the ability to join different tables and aliases (the HOW construct). The tables are indicated on the left side. If you set the flags opposite the table, the UNITE construction will be used, otherwise - UNITE ALL (differences between the two methods). On the right side, the correspondence of fields in different tables is indicated; if the correspondence is not specified, the query will return NULL.

Order tab

This specifies the order in which the values are sorted (ORDER BY) - descending (DESC) or ascending (ASC).

There is also an interesting flag - Auto-order(in the request - AUTO ORDERING). By default, the 1C system displays data in a “chaotic” order. If you set this flag, the system will sort data by internal data.

Query Batch tab

On the query designer tab, you can create new ones, and also use it as a navigation. In the request text, packets are separated by the symbol “;” (comma).

“Query” button in the query designer

In the lower left corner of the request designer there is a Request button, with which you can view the request text at any time:

In this window, you can make adjustments to the request and execute it.

Using the Query Console

The Query Console is a simple and convenient way to debug complex queries and quickly obtain information. In this article, I will try to describe how to use the Query Console and provide a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C query console

First of all, to start working with the query console, you need to download it from somewhere. Treatments are usually divided into two types - controlled forms and regular (or sometimes called 8.1 and 8.2/8.3).

I tried to combine these two types in one treatment - in desired mode operation, the desired form opens (in managed mode, the console only works in thick mode).

Description of the 1C query console

Let's start looking at the query console with a description of the main processing panel:

In the query console header, you can see the execution time of the last query with millisecond accuracy, this allows you to compare different designs in terms of performance.

The first group of buttons in the command bar is responsible for saving current queries to an external file. This is very convenient; you can always return to writing a complex request. Or, for example, store a list of typical examples of certain designs.

On the left, in the “Request” field, you can create new requests and save them in a tree structure. The second group of buttons is responsible for managing the list of requests. Using it you can create, copy, delete, move a request.

Executerequest– simple execution and results
Execute package– allows you to view all intermediate queries in a batch of queries
Viewing temporary tables– allows you to see the results that temporary queries return on a table

Request parameters:

Allows you to set the current parameters for the request.

In the query parameters window, the following is interesting:

Button Get from request automatically finds all parameters in the request for the convenience of the developer.
Flag Common parameters for all requests– when installed, its processing does not clear the parameters when moving from request to request in the general list of requests.

Set a parameter with a list of values It’s very simple, just when choosing a parameter value, click on the clear value button (cross), the system will prompt you to select the data type, where you need to select “Value List”:

also in top panel There is a button for calling up the query console settings:

Here you can specify parameters for autosaving queries and query execution parameters.

The request text is entered into the console request field. This can be done by simply typing a query test or by calling a special tool - the query designer.

The 1C 8 request constructor is called from context menu(right mouse button) when clicking on the input field:

Also in this menu there are such useful features, like clearing or adding line breaks (“|”) to the request, or getting the request code in this convenient form:

Request = New Request;
Request.Text = ”
|SELECT
| Currencies.Link
|FROM
| Directory.Currencies AS Currencies”;
RequestResult = Request.Execute();

The lower field of the query console displays the query result field, which is why this processing was created:

Also, the query console, in addition to the list, can display data in the form of a tree - for queries containing totals.

Query optimization

One of the most important points in increasing the productivity of 1C enterprise 8.3 is optimizationrequests. This point is also very important when passing the certification. Below we will talk about typical reasons for not optimal performance queries and optimization methods.

Selections in a virtual table using the WHERE construct

It is necessary to apply filters to the virtual table details only through the VT parameters. Under no circumstances should you use the WHERE construct for selection in a virtual table; this is a serious mistake from an optimization point of view. In the case of selection using WHERE, in fact, the system will receive ALL records and only then select the necessary ones.

RIGHT:

CHOOSE

FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (
,
Organization = &Organization
AND Individual = &Individual) HOW Mutual settlements with Participants of Organizations Balances

WRONG:

CHOOSE
Mutual settlements with Participants of Organizations Balances. Amount Balance
FROM
Register of Accumulations. Mutual settlements with Participants of Organizations. Balances (,) HOW Mutual settlements with Participants of Organizations Balances
WHERE
Mutual settlements with Participants of Organizations Balances. Organization = & Organization
AND Mutual settlements with Participants of Organizations Balances. Individual = &Individual

Getting the value of a field of a complex type using a dot

When receiving data of a complex type in a query through a dot, the system connects with a left join exactly as many tables as there are types possible in the field of the complex type.

For example, it is highly undesirable for optimization to access the register record field – registrar. The registrar has a composite data type, among which are all possible document types that can write data to the register.

WRONG:

CHOOSE
Record Set.Recorder.Date,
RecordSet.Quantity
FROM
RegisterAccumulations.ProductsOrganizations AS SetRecords

That is, in fact, such a query will access not one table, but 22 database tables (this register has 21 registrar types).

RIGHT:

CHOOSE
CHOICE
WHEN ProductsOrg.Registrar LINK Document.Sales of Products and Services
THEN EXPRESS(ProductsOrg.Registrar AS Document.Sale of GoodsServices).Date
WHEN GoodsOrg.Registrar LINK Document.Receipt of GoodsServices
THEN EXPRESS(GoodsOrg.Registrar AS Document.Receipt of GoodsServices).Date
END AS DATE,
ProductsOrg.Quantity
FROM
RegisterAccumulations.GoodsOrganizations AS GoodsOrganization

Or the second option is to add such information to the details, for example, in our case, adding a date.

RIGHT:

CHOOSE
ProductsOrganizations.Date,
ProductsOrganizations.Quantity
FROM
Register of Accumulations. Goods of Organizations AS Goods of Organizations

Subqueries in a join condition

For optimization, it is unacceptable to use subqueries in join conditions; this significantly slows down the query. It is advisable to use VT in such cases. To connect, you need to use only metadata and VT objects, having previously indexed them by connection fields.

WRONG:

CHOOSE …

LEFT JOIN (
SELECT FROM RegisterInformation.Limits
WHERE …
GROUP BY...
) BY …

RIGHT:

CHOOSE …
PUT Limits
FROM Information Register.Limits
WHERE …
GROUP BY...
INDEX BY...;

CHOOSE …
FROM Document. Sales of Goods and Services
LEFT JOIN Limits
BY …;

Joining Records with Virtual Tables

There are situations when, when connecting a virtual table to others, the system does not work optimally. In this case, to optimize the performance of the query, you can try placing the virtual table in a temporary table, not forgetting to index the joined fields in the temporary table query. This is due to the fact that VTs are often contained in several physical DBMS tables; as a result, a subquery is compiled to select them, and the problem turns out to be similar to the previous point.

Using selections based on non-indexed fields

One of the most common mistakes when writing queries is using conditions on non-indexed fields, this contradicts query optimization rules. The DBMS cannot execute a query optimally if the query includes selection on non-indexable fields. If you take a temporary table, you also need to index the connection fields.

There must be a suitable index for each condition. A suitable index is one that satisfies the following requirements:

The index contains all the fields listed in the condition.
These fields are at the very beginning of the index.
These selections are consecutive, that is, values that are not involved in the query condition are not “wedged” between them.

If the DBMS does not select the correct indexes, the entire table will be scanned - this will have a very negative impact on performance and can lead to prolonged blocking of the entire set of records.

Using logical OR in conditions

That's all, this article covered the basic aspects of query optimization that every 1C expert should know.

A very useful free video course on query development and optimization, I strongly recommend for beginners and more!

1. What is a report builder?

The report builder is an object that allows, based on the specified query text and settings, to obtain a result and display it in spreadsheet document or diagram.

Report Builder settings include:

groupings (example: “Nomenclature”, “Counterparty”...),
indicators (example: “Quantity”, “Amount”...),
additional fields (example: “Item.Service”, “Account.Code”...),
filter (example: by specific item, by group of counterparties...) and
sorting (example: “Nomenclature.Code”, “Counterparty.Name”...)

The builder settings have visualization tools, which allows the programmer to provide the user with an interface for setting them with minimal effort.

Before output, you can style the report builder layout using an appearance layout.

The output of the report builder query result is performed with one command.

2. Where can I use the report builder?

Using the report builder, you can design the bulk of reports (example: “Inventory balances”, “Sales for the period”...). At the same time, the time for their development and program code will be significantly reduced, and the quality of design and functionality will increase.

In addition, the report builder can be used in cases where the output of the result is not needed, but the user needs to customize the query elements, for example, groupings or a filter (example: uploading an item to an xml file or processing the posting of documents according to a given filter).

Since the report builder is not available on the 1C:Enterprise server and is not used in the module outer join, then in these cases it is necessary to use query builder, which is similar to the report builder, but does not have visual properties and methods (“Layout”, “OutputReportTitle”, “Output()”, “DesignLayout()”...).

3. A little about concepts

Compared to version 7.7, version 8.0 has undergone a slight change in concepts, so it’s worth dwelling on this a little.

Concept	Description
Row measurements	List of groupings for which output will be carried out by row (example: “Item”, “Account”...)
Column measurements	List of groupings for which output will be carried out by columns (example: “Warehouse”, “Month”...).
Selection	A set of various filters (example: by group of counterparties, by a given organization...).
Order	A set of sorting fields (example: “Item. Code”, “Sales Document. Responsible”…).
Field	One of the query selection fields (example: “Item”, “Account.Code”, “Quantity”, “Amount”...).
Selected fields	The list of selected fields includes indicators (example: “Quantity”, “Amount”...) and additional fields (grouping details) (example: “Nomenclature.Group”, “Account.Code”...).
Available fields	List of fields available for adding to grouping, filter, sorting, and selected fields. It is filled, as a rule, with a list of query selection fields, but can be edited programmatically (you can add, change and delete available fields).

It is important to understand that group, filter, sort, and selected fields are not directly related to available fields; they are different objects.

The main difference is that the available field does not have the “DataPath” property (example: “Nomenclature.Code”, “Quantity”), since this is simply a kind of description of the field by which an attempt will be made to find a match among the request sample fields at the moment adding it to the list of groupings, filters, sorts or selected fields.

At the same time, it is not possible to programmatically add one of the available fields to any of the above lists, since, on the one hand, the available field does not have methods a la “AddToSelection()” or “AddToSelectedFields()”, and, on the other hand , when adding a new element to any of the above lists, you need to specify the “DataPath” property, which does not always coincide with the field name (example: the fields “Nomenclature.Parent” and “Nomenclature.Parent.Parent” have same name"Parent").

4. Typical use case for the report builder

In general, the sequence of actions when working with the report builder is as follows:

Create a new object “Report Builder”;
Assign the text of the request to the “Text” property;
If the report builder settings were not specified in the request text, then execute the “FillSettings()” method;
Provide the user with the ability to change report builder settings;
If necessary, you can refuse the auto-generation of the layout by the report builder and assign a fixed layout to the “Layout” property;
If necessary, you can assign the “Design Layout” property to one of the standard ones or your own layout, and if the auto-generated layout of the report builder has been replaced with a fixed one, then execute the “DesignLayout()” method;
Call the “Run()” method;
Call the “Output()” method.

Builder = New "Report Builder"; Builder.Text = "SELECT ALLOWED | Company CashRemains.StructuralUnit, | AMOUNT(CompanyCashRemains.AmountRemaining) AS Amount | |FROM | RegisterAccumulations.CompanyCash.Remains(, (StructuralUnit.*)) | StructuralUnit | TOTAL SUM (Amount) BY | GENERAL, | StructuralUnit"; Builder.FillSettings(); //Here you can give the user the opportunity //to change the report builder settings Builder.Layout = GetLayout("CustomLayout");
Builder.AppearanceLayout = GetAppearanceLayout(StandardAppearance.Classic);
Builder.DesignLayout();
Builder.Run();
Builder.Output();

5. Description of some properties of the report builder

Property	Description
Text	Contains the text of the report builder request. If the report builder query text does not contain “(…)” setting elements, then you can use the “FillSettings()” method to automatically fill in the settings based on the query text.
Title Text	The text that will be displayed in the report header (example: “Inventory balances as of 01/01/2004”, “Sales for January 2004”...).
OutputReportTitle OutputTable Header OutputGeneralResults OutputFooterTables OutputReportFootment	These properties determine whether the report title, table header, grand totals, table footer, and report footer are displayed, respectively.
OutputDetailedRecords	The property is responsible for forcing the output or prohibiting the output of detailed records.
AutoDetailedRecords	If the “AutoDetailed Records” property is set to True, then each detailed record will be displayed only if it differs from the group record.
AvailableFields	Contains a list of fields available for adding to groupings, filters, selected fields, and sorting.
DimensionsStrings DimensionsColumns	Contains a list of groupings, respectively, by rows and columns.
Selection	Contains a list of filters specified for the report builder.
SelectedFields	contains a list of selected indicators and grouping details.
Order	contains a list of sort fields.
Options	Contains a list of builder parameters (declared in the body of the builder request using the “&” character).
Result	Contains the result of executing a report builder query (an object of type Query Result).

6. Processing Transcripts using the Report Builder

The “Decryption Filling” property contains one of three options for filling out the transcript during output:

Option 1: do not fill out (do not use transcript).

2nd option: grouping values (when decoding a cell, the grouping value displayed in it will open).

3rd option: decryption (the decryption will be filled with a structure containing the values of all groupings). In this case, you need to process the spreadsheet document event “Decryption Processing,” where you need to analyze the transferred structure and perform certain actions.

An important point is that in the third option it is impossible to output to the generated spreadsheet document, since then it is impossible to process the transcript, the event processing of which occurs in the module of the form on which the spreadsheet document field is located.

If the third option for filling out the transcript is used, then you can use the “SetupTranscript()” method, designed to execute and display the report taking into account the transcript performed by the user.

The report builder can be the same or a new one. When you configure a transcript, all custom report builder filters will be removed and all installed filters from the original query builder will be added, plus filters based on the grouping values from the transcript.

7. Report preparation

The “Layout” property contains the layout that is used when outputting the result to a spreadsheet document. If Undefined, the layout is generated automatically.

The “ReportHeader Layout”, “TableHeaderLayout”, “DetailedRecordsLayout”, “TotalTotalsLayout”, “TableFooterLayout” and “ReportFooterLayout” properties contain the name of the area in the report builder layout or individual layouts used when displaying, respectively, the report header, table header, detailed records, general totals, table footer and report footer into a spreadsheet document. If the scope names match the names in the Report Builder layout, then you do not need to fill out the properties.

The Appearance Layout property contains the layout used to style the report builder layout. The appearance of the cells in the appearance layout will be applied to the cells in the report builder layout.

To get one of the standard design options, you can use the global context method “GetDesignLayout()”.

If the “Layout” property of the report builder has been assigned a fixed layout, to design it you need to execute the “DesignLayout()” method.

The “Placement of Dimensions in Rows” property defines one of three options for displaying groupings by rows.
1st option: together (in one column).
Example:

2nd option: separately (in different columns).
Example:

Example:

The “Placement of Dimensions in Columns” property defines one of three options for displaying groupings by columns.
1st option: together (in one line).
Example:

2nd option: separately (in different lines).
Example:

3rd option: separately and only in the end.
Example:

The “Place Totals in Rows” property defines one of four ways to display totals in rows.
1st option: in the header (only above subsequent groupings and detailed records).

2nd option: in the header and footer (above and below subsequent groupings and detailed entries).

3rd option: in the basement (below subsequent groupings and detailed records, above them only a description of the grouping is displayed).

4th option: only in the basement (only below subsequent groupings and detailed records).

Property “ Placement of Totals in Columns” defines one of four ways to display totals in columns.

1st option: in the header (only to the left of subsequent groupings and detailed entries).

2nd option: in the header and footer (to the left and right of subsequent groupings and detailed entries).

3rd option: in the basement (to the right of subsequent groupings and detailed records, to the left of them only a description of the grouping is displayed).

4th option: only in the basement (only to the right of subsequent groupings and detailed records).

Property “ Placing Details of Dimensions in Rows” defines one of three options for displaying grouping details by row.

1st option: together (in one additional column).
Example:

2nd option: together with measurements (in columns with groupings).
Example:

3rd option: separately (in different additional columns).
Example:

Property “ Placing Details of Measurements in Columns” defines one of three options for displaying grouping details by columns.

1st option: together (in one additional line).
Example:

2nd option: together with dimensions (in rows with groupings).
Example:

3rd option: separately (in different additional lines).
Example:

8. Report Builder Output

The “Run()” method executes the report builder request and populates the “Result” property.
Using the “GetQuery()” method, you can get the report builder query that is executed when the “Run()” method is called.

The “Output()” method displays the result in a spreadsheet document or chart. If you do not specify the object to which you want to output, a new spreadsheet document will be created.

Example of output to a new spreadsheet document:
Builder.Output();

Example of output to an existing spreadsheet document:
Builder.Output(FormElements.ResultTable);

Example of output to a chart:
Builder.Output(FormElements.ResultDiagram, "Quantity");

If you want to display the result of the report builder in a pivot table or pivot chart, then you do not need to use the “Output()” method, but fill in the “Data Source” property of the pivot table or pivot chart. The source can be the result of the report builder or the report builder itself. The result of the report builder does not require re-executing the query, but the report builder allows you to change the composition of the groupings.

9. Saving and restoring builder settings

The “GetSettings()” method allows you to get the current settings of the report builder with the ability to specify which ones (example: filter only, filter and sort...).

The “SetSettings()” method allows you to load the report builder settings with the ability to specify which ones (example: filter only, filter and sort...).

10. Report Builder Groupings and Fields

A report builder dimension is a grouping by rows or columns.

The main details of the group are

“Name” (detail identifier) (example: “Item”, “Counterparty”...),
“Representation” (presentation during output) (example: “Sales document”, “Unit of measure”...),
“PathtoData” (path to the query selection field) (example: “Nomenclature”, “DocumentSales.Organization”...)

and, if the grouping is built according to the directory, then

“Dimension Type” (one of the filter options by field values: hierarchy (elements and groups), hierarchy only (groups only) or elements (elements only)).

In addition, there are several properties that allow you to assign layouts to a group that will be used when output to a spreadsheet document.

“Layout” is the name of the area in the report builder layout or a separate layout used when displaying a grouping.
“Hierarchy Layout” is the name of an area in the report builder layout or a separate layout used when displaying a grouping hierarchy.
“Footer Layout” is the name of the area in the builder layout or a separate layout used when displaying the group footer.
“HierarchyFooterLayout” is the name of the area in the builder layout or a separate layout used when rendering the footer of a grouping hierarchy.
“Level Basement Layouts” - an array of basement layouts for different grouping levels.
“Level Layouts” - an array of layouts for different levels of groupings.

The report builder field is one of the selected fields. The main field details are

“Name” (field identifier) (example: “Quantity”, “Nomenclature Code”, “Period”...),
“Representation” (representation during output) (example: “Quantity”, “Code”, “Period”...) and
“PathKData” (path to the query selection field) (example: “Quantity”, “Nomenclature. Code”, “Period”...).

You can perform a number of actions with a list of groupings or fields.

“Insert()“ - add a grouping/field to the specified position.
“Add()“ - add a grouping/field to the end of the list.
“Index()” - get the grouping/field index in the list.
“Quantity()” - get the number of groupings/fields in the list.
“Find()“ - find a grouping/field in the list by name.
“Clear()“ - clears the list of groupings/fields.
“Get()” - get a grouping/field by index.
“Shift()” - shift the grouping/field by the specified number of positions.
“Delete()” - remove a grouping/field from the list.

11. Builder language

The report builder has its own language. This is a regular query language, supplemented with “(…)” constructs. Using these constructs, you can abandon the “FillSettings()” method and configure the report builder in the request body.
This way you can customize the “SELECT”, “WHERE”, “ORDER BY”, “SUMMARY BY” elements, and also do a few other interesting things. In this case, the body of the builder request will be the default settings, while the constructs (...) will describe the available settings.

If the selection field allows you to get something through a dot, then you can specify “.*” after the field, then the user will be able to select the field details. For example, “Nomenclature.*” allows you to sort by “Nomenclature.Code” or select by “Nomenclature.Service”. But “Quantity.*” does not make sense, since the “Quantity” field does not have details, which means it is enough to simply indicate “Quantity”.

The fields specified in the “SELECT” construct limit the list of fields available to the user.
The fields specified in the “WHERE” construct limit the list of fields that can be used in the filter.
The fields specified in the “ORDER BY” construct limit the list of fields to be sorted.
The fields specified in the “PO RESULTS” construction limit the list of fields for grouping totals.

Now for the interesting stuff.

Firstly, in the main text of the request you can also use constructions (...) in the source parameters. For example, if instead
|FROM
| RegisterAccumulations.Remaining Products of the Company.Remainings(&End Date)
indicate
|FROM
| RegisterAccumulations.Remaining Products of the Company.Remainings((&End Date))
then in this case it will be possible to set a filter by the “End Date” field.

At the same time, in the first case it is necessary to set the report builder parameter “End Date”, in the second case this is not required, in addition, if the selection by the “End Date” field is not set, then this parameter of the table “Accumulation Register. Remaining Products of the Company. Remaining Remains” will not be used at all. will not be taken into account.

Secondly, sources in the request text can be marked as optional by placing them in the “(…)” construction. For example, if you compose a request text like this

|SELECT

| Lots of GoodsCompanyRemains.QuantityRemaining AS QuantityByLots
|(SELECT
| Remaining Products of the Company Remaining Products. Nomenclature AS Nomenclature,
| Remaining Products of the CompanyRemainings.QuantityRemaining AS Quantity,
| Lots of GoodsCompanyRemains.QuantityRemaining AS QuantityByLots)
|FROM
| RegisterAccumulations.Remaining Products of the Company.Remainings AS Remaining Products of the CompanyRemainings
| (LEFT CONNECTION Register Accumulations. Company Goods Lots. Remains AS Company Goods Lots Remains
| Software Remaining Products of the CompanyRemainings.Nomenclature = Lots of Products of the CompanyRemainings.Nomenclature)
|RESULTS SUM(Quantity), SUM(QuantityByBatch) ON
| ARE COMMON,
| Nomenclature

then the left join by batch register will be performed only if the “QuantityByBatch” field is present in the list of selected fields.

Third, in the source parameters you can specify that if filters are specified, if possible, apply them not to the result of the query using the “WHERE” element, but to the source itself. So instead of
| RegisterAccumulations.CompanyProductRemains.Remains()

it makes sense to write

| Accumulation Register.Company Goods Remains.Remains(, (Nomenclature.*, Company Warehouse.*, Order.*, Retail Price, Nomenclature Characteristics.*))

Well, fourthly, the most delicious, in my opinion. If the “Frequency” parameter is specified in the source (for tables that allow you to obtain turnover), it can also be set as “(...)”, and, as a result of a little manipulation with the builder query text, we can use groupings by periods.

|FROM | RegisterAccumulation.Company Goods Lots.Turnover(, (&Frequency), | (Nomenclature.*)) AS Company Goods LotsTurnover |

|GROUP BY | Lots of GoodsCompanyTurnover.Nomenclature |<>|RESULTS AMOUNT(CostIncoming), AMOUNT(CostExpenditure) BY | GENERAL, | Nomenclature |<>|(SELECT | Lots of Products of the Company Turnover. Nomenclature.*, | Registrar.*, | Beginning of the Period (Period, Day) AS PeriodDay, | Beginning of the Period (Period, Week) AS PeriodWeek, | Beginning of the Period (Period, Decade) AS PeriodDecade, | Beginning of the Period (Period, Month) AS PeriodMonth, | Beginning of Period(Period, Quarter) AS PeriodQuarter, | Beginning of Period(Period, Half-Year) AS PeriodHalf-Year, | SUM(CompanyProduct LotsTurnover. CostExpense) HOW CostExpense) |<>Undefined Then Periodicity = 9;<>endIf;<>If Report Builder.DimensionsRows.Find("PeriodHalf-Year")<>Undefined OR Report Builder.DimensionsColumns.Find("PeriodHalf-Year")<>Undefined Then Periodicity = 8;<>endIf;<>If Report Builder.DimensionsRows.Find("PeriodQuarter")<>Undefined OR Report Builder.DimensionsColumns.Find("PeriodQuarter")<>Undefined Then Periodicity = 7;<>endIf;<>If ReportBuilder.DimensionsRows.Find("PeriodMonth")<>Undefined OR Report Builder.DimensionsColumns.Find("PeriodMonth")<>Undefined Then Periodicity = 6;<>endIf;<>If Report Builder.Row Dimensions.Find("PeriodDecade")<>Undefined OR Report Builder.DimensionsColumns.Find("PeriodDecade")<>Undefined Then Periodicity = 5; endIf; If ReportBuilder.DimensionsRows.Find("PeriodWeek")<>Undefined OR Report Builder.DimensionsColumns.Find("PeriodWeek")

Undefined Then Periodicity = 4;

endIf;

In addition, you can also specify “Builder.AvailableFields” as the data source and, thus, firstly, see the list of available fields, and, secondly, using the context menu, add fields to groupings, selection, selected fields or filters.

The eighth version of the 1C platform offers quite a lot of new configuration objects, each of which has its own methods and properties that make it possible to simplify the application development process and optimize performance. One of them is the report builder.

I would like to talk about what a report builder in 1C is, what functions it performs, and what application it has found in programming.

Report builder in 1C - what is it?

This is a configuration object that allows you to display data in a spreadsheet document or chart. The data source can be:

The result of the request;
Cell area of a spreadsheet document;
Tabular part of a document or reference book;
A set of register entries.

In addition, the report builder in 1C has a “Text” attribute to which the text of the request can be sent, the result of which needs to be viewed.

Figure 1 shows a section of code that allows you to use the “Report Builder” to display an arbitrary table of values

So the obvious use case for the builder is to design reports. A less obvious use for the builder is its use for the user to customize various elements of a query (groupings, selections, etc.).

By the way, the result of executing the code indicated in Fig. 1 can be seen in Fig. 2

Fig.2

Builder setup

The report builder in some sense resembles a data composition system and can be used in cases where the access control system is unavailable for one reason or another or there is a possibility that it will not work correctly.

Like the ACS builder, it includes:

Groupings (hierarchy levels when displaying information);
Indicators (values for which it is necessary to obtain the result);
Additional calculated fields (element details, results of actions with values);
Filters (selections);
Sorting.

The builder's design layout settings offer varying levels and precisions of detail.

Algorithm for using the builder

Naturally, depending on the situation, the program code for using the report builder can vary significantly. The basic algorithm of use does not change:

We declare the creation of a new object of the ReportBuilder type;
At this stage, you can either use the Query Builder to create the selection text, or, using the DataSourceDescription object, use an existing selection;
Report builder settings can be set using the builder object method FillSettings();
You can refuse to use a standard design layout created using the auto-generation method by defining the Layout object property of the builder;
If DataSourceDescription is not used, at this stage it is necessary to execute the request, use the Execute() method;
The last step is to launch the Output() method; the output can be done in a spreadsheet document, or in the corresponding form element.

Using decryption in the builder

The third line in Fig. 1 includes the decoding of the cells of the builder execution result.

To do this, we used the Decryption Filling property. The type of this property is ReportBuilderDecryptionFillType; it can take one of three values:

GroupingValues – when a cell is activated, the value of the grouping specified in it will be used;
Do not Fill – the default value, which means that the transcript will not be used for the final result;
Transcript – the transcript structure will be filled with the values of all groupings of the original sample.

In addition, the user has the opportunity to independently set the decryption using the ConfigureDecryption() method. The code section that runs this method is shown in Fig. 3

Fig.3

Example of using the builder to enable selections in a report

A little practical part. Sometimes there is a situation when it is necessary to create a report with some selections. You can solve this problem using ACS, or you can use the builder:

Let's start by creating an external report and adding to it an attribute with an arbitrary name and typing ReportBuilder (Fig. 4);

Fig.4

We create a report form and place the TabularField interface object on it, type it as Selection and associate it with the Selection property of the created attribute Fig. 5

Fig.5

We create a OnOpen() procedure associated with the corresponding event of our form;

Fig.6

The code written in it must necessarily contain a request for the report builder, an example of the code is shown in Fig. 6, to create the text of the builder, you can use the query constructor, in the constructor, the conditions of the builder and its additional settings are indicated on the corresponding tab in Fig. 7;

Fig.7

The selection conditions specified in curly () brackets in the request text will be executed only if the corresponding selection is activated and used by the user;
The procedure that displays the result of the builder is presented in Fig. 8 and is associated with clicking on the Generate button;

Fig.8

We save our report.

The appearance of the report we created and its result are shown in Fig. 9

Fig.9

Of course, within one article it is quite difficult to demonstrate all the capabilities of a new object, but it seems to us that it is possible to get an idea about it and start using it taking into account the knowledge gained.