Work of the Report Builder and Query Builder with an arbitrary data source. Work of the report builder and the query builder with an arbitrary data source Query builder 1c 8.2 selection

The eighth version of the 1C platform offers 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 Report Builder.

About what a 1c report builder is, what functions it performs, and what application it has found in programming, and I would like to talk in this article.

Report builder in 1C - what is it?

It is a configuration object that allows you to output data to a spreadsheet document or chart. The data source can be:

The result of the query execution;
Area of cells of a spreadsheet document;
Tabular section of a document or reference book;
A set of register entries.

In addition, the report builder in 1C has the "Text" attribute into which the query text can be sent, the result of which must be viewed.

Figure 1 shows a piece of code that allows you to display an arbitrary table of values using the "Report Builder"

Thus, the obvious use case for the builder is to design reports. Not such an obvious direction of using the builder is its use to customize various query elements (groupings, selections, etc.) by the user.

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

Fig. 2

Builder setup

The report builder, in a sense, resembles a data composition system and can be used in cases where the ACS is unavailable for one reason or another, or there is a possibility that it will work incorrectly.

Like the ACS, the builder includes:

Groupings (hierarchy levels when displaying information);
Indicators (values for which you want to get the total);
Additional calculated fields (attributes of elements, results of actions with values);
Filters (selections);
Sorting.

Builder appearance layout settings assume different levels and precision of detail.

Algorithm for using the builder

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

We declare the creation of a new object of the ReportBuild type;
At this stage, you can either use the QueryBuilder to create the selection text, or, using the DataSource Description object, use an existing selection;
Report builder settings can be set using the builder object method FillSettings ();
You can refuse to use the standard design layout created by the autogeneration method by defining the property of the Layout object in 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 run 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 in the result of the builder execution.

To do this, we used the FillDecoding property. The type of this property is ReporterPerformFillType, it can take one of three values:

Grouping Values - when activating a cell, the value of the grouping specified in it will be used;
Do not fill - the default value, which means that no decryption will be used for the final result;
Decryption - the structure of the decryption will be filled with the values of all groupings of the original sample.

In addition, the user can set the decryption on his own using the ConfigureDecryption () method. The piece of code that runs this method is shown in Fig. 3

Fig. 3

An example of using the builder to include selections in a report

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

Let's start by creating external report and adding to it a variable with an arbitrary name and typing Report Builder (Fig. 4);

Fig. 4

Create a report form and place the TabularField interface object on it, type it as Selection and link it to the Selection property of the created attribute Fig. 5

Fig. 5

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

Fig. 6

The code written in it must necessarily contain a query 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 builder conditions and its additional settings are indicated on the corresponding tab Fig. 7;

Fig. 7

Selection conditions specified in curly () brackets of the query 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 shown in Fig. 8 and is associated with pressing the Generate button;

Fig. 8

We save our report.

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

Fig. 9

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

1. What is a Report Builder?

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

Report Builder settings include:

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

The builder settings have visualization tools that allow the programmer to provide the user with an interface for their installation with minimal effort.

Before outputting, you can style the report builder layout using the layout layout.

The output of the execution result of the query of the report builder is carried out by one command.

2. Where can you use the Report Builder?

With the help of the report builder, you can design the bulk of the reports (example: “Stock 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: unloading an item in an xml file or processing documents posting by a specified filter).

Since the report builder is not available on the 1C: Enterprise server and is not used in the external connection module, 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", "OutputReportHeader", "Output ()", "CheckoutLayout ()" ...).

3. A little about concepts

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

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

It is important to understand that the grouping, filter, sorting and selected fields are not directly related to the available fields, they are different objects.

The main difference is that the available field does not have the “Data Path” property (example: “Nomenclature.Code”, “Quantity”), since this is just a kind of description of the field by which an attempt will be made to find a match among the query selection fields at the moment adding it to the list of groupings, filter, sort 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 like “AddWelected ()” or “AddInSelectedFields ()”, but, on the other hand , when adding a new item to any of the above lists, you need to specify the “Data path” property, which does not always match the field name (for example: the “Nomenclature.Parent” and “Nomenclature.Parent.Parent” fields have the same name “Parent”).

4. Typical use case of 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 query text, then execute the “FillSettings ()” method;
Provide the user with the ability to change the settings of the report builder;
If necessary, you can refuse to auto-generate the layout by the report builder and assign a fixed layout to the Layout property;
If necessary, you can assign one of the standard or your own layout to the "Layout" property, and if the autogenerated layout of the report builder has been replaced with a fixed one, then execute the "Checkout Layout ()" method;
Call the "Run ()" method;
Call the "Output ()" method.

Builder = New "Report Builder"; Postroitel.Tekst = "Select the resolution | DenezhnyeSredstvaKompaniiOstatki.StrukturnayaEdinitsa, | Amount (DenezhnyeSredstvaKompaniiOstatki.SummaOstatok) as the sum of | | FROM | RegistrNakopleniya.DenezhnyeSredstvaKompanii.Ostatki ((StrukturnayaEdinitsa *).) | | Grouped by | DenezhnyeSredstvaKompaniiOstatki.StrukturnayaEdinitsa | | TOTAL RESULTS (Amount) BY | GENERAL, | Structural Unit "; Builder.FillSettings (); // Here you can give the user the opportunity // to change the settings of the report builder Builder.Layout = GetLayout ("CustomLayout");
Builder.Layout.Layout = GetLayoutLayout (StandardLayout.Classic);
Builder.CheckLayout ();
Builder.Run ();
Builder.Display ();

5. Description of some properties of the report builder

Property	Description
Text	Contains the query text of the report builder. If the query text of the report builder does not contain setting items “(…)”, then you can use the FillSettings () method to automatically fill in the settings based on the query text.
TextHeader	The text that will be displayed in the report title (example: “Stock balances as of 01.01.2004”, “Sales for January 2004” ...).
OutputReport Title DisplayHatTable ShowGeneralResults DisplayBasementTables DisplayFooterReport	These properties determine whether the report title, table header, grand totals, table footer, and report footer are displayed, respectively.
DisplayDetailedRecords	The property is responsible for the forced display or prohibition of the display of detailed records.
AutoDetailedRecords	If the property “AutoDetailed Records” is set to True, then each detailed record will be displayed only if it differs from the group one.
Available Fields	Contains a list of fields available for adding to groupings, filter, selected fields and sorting.
MeasurementsStrings Measurements Columns	Contains a list of groupings, respectively, by row and by column.
Selection	Contains a list of filters defined by the report builder.
SelectedFields	contains a list of selected indicators and grouping attributes.
Order	contains a list of sort fields.
Options	Contains a list of builder parameters (declared in the builder query text using the “&” symbol).
Result	Contains the result of executing a query of the report builder (an object of the QueryResult type).

6. Processing Transcripts with the Report Builder

The "FillDecryption" property contains one of three options for filling in the decryption in the output:

1st option: do not fill in (do not use decryption).

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

3rd option: decryption (the decryption will be filled in with a structure containing the values of all groupings). In this case, it is required to process the event of the spreadsheet document “Processing of decryption”, where it is necessary to analyze the transmitted 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 decryption, the event of which is processed in the module of the form on which the field of the spreadsheet document is located.

If you use the third option for filling in the decryption, then you can use the "ConfigureDecryption ()" method, designed to execute and display a report taking into account the decryption performed by the user.

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

7. Reporting

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

Properties "ReportHeader Layout", "TableHeader Layout", "DetailRecords Layout", "GeneralTotals Layout", "TableFootboard Layout" and "ReportFootboard Layout" contain the name of the area in the report builder layout or individual layouts used in the output, respectively, of a table, a report title, a header grand totals, table footer, and report footer into a spreadsheet document. If the area names match the names in the report builder layout, then you do not need to fill in the properties.

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

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

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

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

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

Example:

The "PlacementDimensionsInColumns" 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 totals.
Example:

The "PlacementTotalsInLows" property defines one of four ways to display totals in rows.
1st option: in the title (only above the subsequent groupings and detailed entries).

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

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

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

Property “ Placement of Results in Columns”Defines one of four ways to display totals in columns.

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

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

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

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

Property “ Placement of Dimension Attributes in Rows”Defines one of three options for displaying grouping attributes by lines.

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 “ PlacingDimensionAttributesInColumns”Defines one of three options for displaying grouping details by columns.

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

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

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

8. Output of the report builder

The “Run ()” method executes the query of the report builder and fills in the “Result” property.
Using the GetRequest () method, you can get a report builder query that is executed when the Run () method is called.

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

An example of output to a new spreadsheet document:
Builder.Display ();

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

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

If you need to display the result of the report builder in a pivot table or pivot chart, then you should not use the “Output ()” method, but fill in the “DataSource” property of the pivot table or pivot chart. The source can be either the report builder result or the report builder itself. The result of the report builder does not require overrunning 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: only filter, filter and sorting ...).

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

10. Groupings and fields of the report builder

A report builder dimension is grouping by row or column.

The main details of the grouping are

“Name” (variable identifier) (example: “Nomenclature”, “Counterparty” ...),
"Presentation" (presentation at output) (example: "Sales document", "Unit of measure" ...),
"DataPath "(path to the request selection field) (example:" Nomenclature "," DocumentSales.Organization "...)

and, if the grouping is built according to the reference book, then also

“DimensionType” (one of the options for filtering by field values: hierarchy (members and groups), only hierarchy (only groups), or members (only members)).

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

“Layout” is the name of the region in the report builder layout or a separate layout used when displaying a grouping.
“LayoutHierarchy” is the name of the area in the report Builder layout or a separate layout used when displaying the grouping hierarchy.
“FooterLayout” is the name of the area in the builder layout or a separate layout used when displaying the grouping footer.
“HierarchyFooterLayout” is the name of the area in the builder layout, or a separate layout used when displaying the grouping hierarchy footer.
“BasementLayers” - an array of basement layouts for different grouping levels.
“Level Layouts” - an array of layouts for different grouping levels.

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

“Name” (field identifier) (example: “Quantity”, “Nomenclature Code”, “Period” ...),
"Presentation" (presentation in output) (example: "Qty", "Code", "Period" ...) and
“Data path” (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 index of the grouping / field in the list.
“Number ()“ - get the number of groupings / fields in the list.
“Find ()“ - find a grouping / field in the list by name.
“Clear ()“ - clear the list of groupings / fields.
“Get ()“ - get grouping / field by index.
"Move ()" - move the grouping / field by the specified number of positions.
“Remove ()“ - remove a grouping / field from the list.

11. Builder language

The report builder has its own language. This is a common query language, supplemented with the “(…)” constructs. Using these constructs, you can abandon the FillSettings () method and customize the report builder in the query body.
Thus, you can customize the items "SELECT", "WHERE", "ORDER BY", "RESULTS BY", and also do some other interesting things. In this case, the main text of the builder query 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 details of the field. For example, “Nomenclature. *” Allows you to sort by “Nomenclature.Code” or filter by “Nomenclature.Service”. But “Quantity. *” Does not make sense, since the “Quantity” field has no details, which means that 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” construction limit the list of fields that can be used in the filter.
The fields specified in the “ORDER BY” construction limit the list of fields to be sorted.
The fields specified in the "BY TOTAL" construct limit the list of fields for grouping totals.

And now for the interesting things.

At first, in the main text of the request, you can also use the constructions (...) in the parameters of the sources. For example, if instead of
| FROM
| Accumulation Register.Company's Commodity Balances.Remains (& End Date)
point out
| FROM
| Accumulation Register.Company's Commodity Balances.Remains ((& End Date))
then in this case it will be possible to set a filter by the "EndDate" field.

At the same time, in the first case, it is necessary to set the parameter of the report builder “End Date”, in the second case, this is not required, moreover, if the selection by the “End Date” field is not specified, then this parameter of the table “Accumulation Register. will not be counted.

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

| CHOOSE

| LotProductsCompaniesBalances.QuantityBalance AS QuantityBy
| (SELECT
| Balances of Commodities of the Company Balances. Nomenclature AS Nomenclature,
| Balance of Commodities of the Company Balance.Quantity Balance AS Quantity,
| Lots of goodsCompaniesBalances.QuantityBalance AS QuantityBy lots)
| FROM
| Accumulation Register.Company's Commodity Balances.Remains AS Commodity BalancesCompany's Balances
| (LEFT JOIN ACCUMULATION REGISTER.LOTS OF GOODSCompany. Balances AS Batch of GoodsCompanies
| By Balances of CommoditiesCompany Balances.Nomenclature = Lots ofProductsCompany Balances.Nomenclature)
| TOTALS SUM (Quantity), SUM (QuantityByLots) BY
| GENERAL,
| Nomenclature

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

Thirdly, in the parameters of sources, you can specify that in the case of setting filters, if possible, they should be applied not to the result of executing a query using the “WHERE” element, but to the source itself. Thus, instead of
| Accumulation Register.Goods BalancesCompany.Remains ()

it makes sense to write

| Accumulation Register.Company's Commodity Balances.Remains (, (Nomenclature. *, Company Warehouse. *, Order. *, Retail Price, Nomenclature Characteristic. *))

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

There is one nuance, in order for the grouping by periods to work, you need to add a filter by the “Frequency” parameter. In order not to do this manually, for convenience, you can programmatically add it before running the report builder, and delete it after execution so that it does not bother you, since it is impossible to understand such a filter from the point of view of common sense. This is done something like this

IfReportBuilder.AvailableFields.Find ("Frequency")<>Undefined Then Periodicity = Undefined; IfReportBuilder.DimensionsStrings.Find ("PeriodYear")<>Undefined OR Report Builder.DimensionsColumns.Find ("PeriodYear")<>Undefined Then Periodicity = 9; EndIf; IfReportBuilder.DimensionsRows.Find ("PeriodSemyear")<>Undefined OR Report Builder.DimensionsColumns.Find ("PeriodHalf Year")<>Undefined Then Periodicity = 8; EndIf; IfReportBuilder.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; IfReportBuilder.DimensionsRows.Find ("PeriodDecade")<>Undefined OR Report Builder.DimensionsColumns.Find ("PeriodDecade")<>Undefined Then Periodicity = 5; EndIf; IfReportBuilder.DimensionsRows.Find ("PeriodWeek")<>Undefined OR Report Builder.DimensionsColumns.Find ("PeriodWeek")<>Undefined Then Periodicity = 4; EndIf; IfReportBuilder.DimensionsRows.Find ("PeriodDay")<>Undefined OR Report Builder.DimensionsColumns.Find ("PeriodDay")<>Undefined Then Periodicity = 3; EndIf; IfReportBuilder.DimensionsStrings.Find ("Logger")<>Undefined OR Report Builder.DimensionsColumns.Find ("Logger")<>Undefined OR Report Builder.SelectedFields.Find ("Logger")<>Undefined Then Periodicity = 2; EndIf; If the frequency<>Undefined Then SelectionFieldByFrequency = ReportBuilder.Selection.Add ("Frequency"); SelectionFieldBy Periodicity.Value = Periodicity; SelectionFieldByFrequency.Usage = True; EndIf; EndIf; Builder.Run (); If Selection Field By Frequency<>Undefined ThenReportBuilder.Collection.Remove (ReportBuilt.Selection.Index (FeedFieldBy Periodic)); EndIf;

12. Customizing the builder

In order to give the user the opportunity to change the list of groupings, filter, selected fields or sorting, it is enough to create the "Builder" attribute of the report and place a table field on the report form and specify it as a data source "Builder.DimensionsRows", "Builder.DimensionsColumns" , “Builder.Choose”, “Builder.ChosenFields”, or “Builder.Order”.

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

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

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

Request example:

SELECT
Banks.
Banks.CorrAccount
FROM
Directory. Banks AS 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 you can see from the example above, in the query language, you need to appeal with the names of metadata (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 get the data, it is enough to use the "SELECT" (select) and "FROM" (from) constructions. The simplest request as follows:

SELECT * FROM Directories.Nomenclature

Where "*" means the selection of all fields of the table, and References.Nomenclature is the name of the table in the database.

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

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

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

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

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

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

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

From the received data, we select only data that meets the condition from "WHERE" "DataFeed Condition". Next, we group the request by the "FieldName1" field, while summing "FieldName2". We create totals for the "FieldNameField1" field and the final field "FieldName2".

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

General constructions

Let's consider the general constructions of the query language 1C 8.2.

FIRSTn

By using this operator you can get n the number of first records. The order of the entries is determined by the order in the request.

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

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

ALLOWED

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

If a user tries to read records that are inaccessible to him with a request, he will receive an error message. To avoid this, you should use the "ALLOWED" construction, that is, the request will read only the records allowed for it.

SELECT ALLOWED
Additional Information Store.Link
FROM
Reference.Additional Information Storage

VARIOUS

Using "DIFFERENT" will allow you to exclude duplicate strings from entering the 1C query result. Duplication means that all fields of the request match.

SELECT FIRST 100
Banks.
Banks. Code AS BIC
FROM
Directory. Banks AS Banks

EmptyTable

This construct is rarely used to combine queries. When joining, it may be necessary to specify an empty nested table in one of the tables. The "EmptyTable" operator is just suitable for this.

Example from help 1C 8:

SELECT Reference.Number, EMPTY TABLE. (Nom, Tov, Qty) AS Composition
FROM Document.Expense
COMBINE ALL
SELECT Reference.Number, Composition. (LineNumber, Item, Quantity)
FROM Document.Invoice Document.Invoice.Content. *

IS NULL

A very useful feature that avoids many mistakes. YesNULL () allows you to replace the NULL value with the desired one. It is very often used in tests for the presence of a value in attached tables, for example:

SELECT
NomenclatureRef.Ref.
There is NULL (Item Balance.Quantity Balance, 0) AS Quantity Balance
FROM

You can use it in another way. For example, if for each row it is not known in which table the value exists:

IS NULL (InvoiceReceived.Date, InvoiceEdited.Date)

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

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

SELECT
View (Link), // line, for example "Advance report No. 123 dated 10/10/2015
Representation (Deletion Mark) AS Deletion Mark Text, // string, "Yes" or "No"
Referencing View (Deletion Mark) AS Deletion Mark Boolean // 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 reference type is used to restrict the requested data types in fields of a complex type, often used to optimize system performance. Example:

EXPRESS (Cost.Subconto1 Table AS A Reference.Cost Articles) .ActivityTypeFor Tax Cost Accounting

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

EXPRESS (Comment AS Line (150))

DIFFERENCE

Get 267 1C video tutorials for free:

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

CHOOSE FROM
Spr
LEFT JOIN Accumulation Register.ProductsIn Warehouses.Left AS RemainingGoods
Software NomenclatureRef.Ref = ImplementedCommentsComponentsRemains.Nomenclature
WHERE IS THE BALANCE OF THE GOODS.Quantity The balance IS NULL

You can define the data type in a query as follows: using the TYPE () and TYPE () functions, or using logical operator LINK. These two functions are similar.

Predefined Values

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

Usage example:

WHERE Nomenclature.NomenclatureType = Value (Directory.NomenclatureTypes.Product)

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

WHERE Account Balances.Account = Value (Plan of Accounts. Self-supporting.ProfitsLoss)

Connections

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

LEFT and RIGHT JOIN

Joins are used to link two tables according to a specific condition. Feature at LEFT JOINT in that we take the first specified table in full and bind the second table by condition. 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 NULL.

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

FULL CONNECTION

FULL CONNECTION differs from the left and right in that it displays all records from two tables, joins only those that it can join by condition.

For example:

FROM

FULL CONNECTION
Directory. Banks AS Banks

ON

The query language will return both tables in full only if the condition of joining records is fulfilled. Unlike left / right join, it is possible for NULLs to appear in two fields.

INTERNAL CONNECTION

INTERNAL CONNECTION differs from the complete one in that it displays only those records that could be connected by the specified condition.

For example:

FROM
Directory. Contractors AS Clients

INTERNAL CONNECTION
Directory. Banks AS Banks

ON
Clients.Name = Banks.Name

This request will return only lines in which the bank and the counterparty have the same name.

Associations

The COMBINE and COMBINE ALL construct combines two results into one. Those. the result of performing two will be "merged" into one, common.

That is, the system works in the same way as usual, only for a temporary table.

How to use INDEX SOFTWARE

However, there is one point to consider. 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 not be 1-2 records in the temporary table. Otherwise, the effect may be the opposite - the performance from indexed fields does not compensate for the index building time.

SELECT
Currency RatesSliceLast.Currency AS Currency,
Currency RatesSliceLast.Course
POST Currency Rates
FROM
Information Register.Currency Rates.SliceLast (& Period,) AS Currency RatesSliceLast
INDEX BY
Currency
;
SELECT
Prices
PricesNomenclatures .Price,
PricesNomenclature.Currency,
Currency Rates.
FROM
Information Register.NomenclaturePrices.SliceLast (& Period,
Nomenclature B (& Nomenclature) AND PriceType = & PriceType) AS Prices
LEFT UNION Currency Rates AS Currency Rates
ON RatesNomenclature.Currency = RatesCurrency.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 are available:

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

Example # 1:

SELECT
Sale of goods, services, goods, nomenclature,
AMOUNT (Sale of Goods, Services, Goods.Quantity) AS Quantity,
AMOUNT (Sale of GoodsServicesGoods.Amount) AS Amount
FROM

LOAD BY
Realization of GoodsServicesGoods.Nomenclature

The request receives all lines with goods and summarizes them by quantity and amounts in the context of the item.

Example No. 2

SELECT
Banks. Code,
QUANTITY (DIFFERENT Banks.Link) AS QuantityDoubles
FROM
Directory. Banks AS Banks
LOAD 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.

Outcomes

Totals are a way to get data from a hierarchical system. For summary fields, aggregate functions can be used, as for groupings.

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

SELECT

FROM
Document.Realization ofGoodsServices.Goods AS Sale ofGoodsServicesGoods
SORT BY

RESULTS
SUM (Quantity),
SUM (Amount)
ON
Nomenclature

The result of the query will be the following hierarchical:

General summary

If you want to get totals for all "totals", use the "GENERAL" operator.

SELECT
Realization of GoodsServicesGoods. Nomenclature AS Nomenclature,
Realization of GoodsServicesGoods.Link AS Document,
Realization of goods, services, goods. Quantity AS Quantity,
Sale of GoodsServicesGoods.Amount AS Amount
FROM
Document.Realization ofGoodsServices.Goods AS Sale ofGoodsServicesGoods
SORT BY
Implementation of GoodsServicesGoods.Link.Date
RESULTS
SUM (Quantity),
SUM (Amount)
ON
GENERAL,
Nomenclature

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

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

Ordering

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

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

SELECT

FROM
Reference.Nomenclature AS Nomenclature
SORT BY
Name

The query will display a list of the names of the nomenclature reference book, sorted alphabetically.

Auto-ordering

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

If you need to display table records in a consistent order, you must use the "Autoorder" construct.

SELECT
Nomenclature Name AS Name
FROM
Reference.Nomenclature AS Nomenclature
AUTO ORDERING

Virtual tables

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

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

slice of the first;
a cut of the latter.

leftovers;
revolutions;
balances and turnovers.

movements from the subconto;
revolutions;
RPM Dt Kt;
leftovers;
balances and turnovers
subconto.

base;
graph data;
the actual period of validity.

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

SELECT
ProductsIn WarehousesBalances andTurnovers.Nomenclature,
ProductsIn WarehousesBalancesAndTurnovers.QuantityInitial Balance,
GoodsIn WarehousesBalances andTurnovers.QuantityTurnover,
GoodsIn WarehousesBalances andTurnovers.QuantityIncoming,
GoodsIn WarehousesBalances andTurnovers.Quantity
ProductsIn WarehousesBalancesAndTurnovers.QuantityFinal Balance
FROM
Accumulation Register.GoodsIn Warehouses.Balances AND Turnovers AS GoodsIn WarehousesBalances And Turnovers

Such a request allows you to quickly receive a large number of data.

Virtual table parameters

Highly important aspect working with virtual tables - using parameters. Virtual table parameters - specialized parameters for selection and tuning.

For such tables, it is considered incorrect to use selection in the "WHERE" construct. In addition to the fact that the request becomes not optimal, it is possible to receive incorrect data.

An example of using such parameters:

Accumulation Register.ProductsIn Warehouses.RemainsAndTurnovers (& PeriodBeginning, & PeriodEnd, Month, Movements and PeriodBorders, Nomenclature = & DesiredNomenclature)

Algorithm of virtual tables

For example, the most commonly used virtual table of the type "Residuals" stores data from two physical tables - balances and movements.

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

We get the calculated value closest in terms of date and measurements in the totals table.
“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 constructor- a tool built into the 1C enterprise system, which greatly facilitates the development of queries to the database.

The query constructor has a fairly simple, intuitive clear interface... However, let's take a closer look at using the query constructor.

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

Description of the 1C query constructor

Let's take a closer look at each design tab. The exception is the Builder tab, which is a topic for another discussion.

Tab Tables and Fields

This tab specifies the data source and fields that you want to display in the report. In fact, the constructions CHOOSE .. FROM.

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

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

Links tab

The tab is used to describe connections of several tables, 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 goes in the request text after the WHERE construction, that is, for all the conditions imposed on the received data.

Advanced tab

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

Grouping Fetching records:

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

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

Below there is a flag Block received data for later change... It allows you to enable the ability to set a data lock, which ensures the safety of data from the moment it is read until it is changed (relevant only for Automatic mode locks, design FOR CHANGE).

Unions / Aliases Tab

On this tab of the query designer, you can set the ability to combine different tables and aliases (HOW construction). Tables are indicated on the left. If you set the flags opposite the table, the UNION construction will be used, otherwise - UNION ALL (the two methods differ). The right side shows the correspondences of the fields in different tables if no match is specified, the query will return NULL.

Order tab

Here you specify the sorting order of values (ORDER BY) - descending (DESCENT) or ascending (RETURN).

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

Query Package Tab

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

Query Button in Query Builder

In the lower left corner of the query constructor there is a Query button, with which you can view the query 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 an easy and convenient way to debug complex queries and get information quickly. In this article I will try to describe how to use the Query Console and give a link to download the Query Console.

Let's take a closer look at this tool.

Download 1C Query Console

First of all, to get started with the Query Console, you need to download it from somewhere. Processes are usually divided into two types - controlled forms and regular (or, sometimes, they are called on 8.1 and 8.2 / 8.3).

I tried to combine these two types in one processing - in the required mode of operation, the required form opens (in controlled mode the console only works in bold 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 header of the query console, you can see the execution time of the last query with millisecond precision, this allows you to compare different constructs in terms of performance.

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

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

Executeinquiry- simple execution and getting the result
Run package- allows you to view all intermediate requests in the request batch
Viewing temporary tables- allows you to see the results that are returned by temporary queries in the 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 Uniform parameters for all requests- when installed, its processing does not clear the parameters when moving from request to request in general list requests.

Set parameter as a list of values it is very simple, when you select a parameter value, press the button to clear the value (cross), the system will offer to select the data type, where you need to select “List of values”:

also in top panel there is very much a button to call the settings of the query console:

Here you can specify options for autosave queries and options for executing a query.

The query text is entered in the console query field. This can be done by a simple query test suite or by calling a special tool - the query constructor.

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

Also in this menu there are such useful functions as clearing or adding line break characters (“|”) to the request, or getting the request code in such a convenient form:

Request = New Request;
Request.Text = "
| CHOOSE
| Currencies.Link
| FROM
| Directory. Currencies AS Currencies ”;
QueryResult = Query.Run ();

In the lower field of the query console, the query result field is displayed, for which this processing was created:

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

Optimizing queries

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

Selections in the virtual table using the WHERE construction

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

RIGHT:

SELECT

FROM
Accumulation Register.Mutual settlementsWith DeponentsOrganizations.Balances (
,
Organization = & Organization
And Individual = & Individual) HOW Mutual settlementsWith DeponentsOrganizations

NOT RIGHT:

SELECT
Mutual settlementsWithDeponentsOrganizationsBalances.AmountBalance
FROM
Accumulation Register.Mutual settlementsWith DeponentsOrganizations.Balances (,) AS Mutual settlementsWithDeponentsOrganizationsBalances
WHERE
SettlementsWith DeponentsOrganizationsBalances.Organization = & Organization
And Mutual settlementsWith DeponentsOrganizationsBalances.Personal = & Individual

Getting the value of a complex type field through a dot

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

For example, it is highly undesirable for optimization to refer to the register record field - the registrar. The registrar has a composite data type, including all possible types of documents that can write data to the register.

NOT RIGHT:

SELECT
Record Set.Recorder.Date,
Set of Records.Number
FROM
Accumulation Register.ProductsOrganizations AS SetRecords

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

RIGHT:

SELECT
CHOICE
WHEN GoodsOrg.Registrar LINK Document.Realization of GoodsServices
THEN EXPRESS (GoodsOrg.Registrar AS Document.Realization of GoodsServices) .Date
WHEN GoodsOrg.Registrar LINK Document.Arrival of GoodsServices
THEN EXPRESS (GoodsOrg.Registrar AS Document.Arrival of GoodsServices) .Date
END AS Date,
ProductsOrg.Quantity
FROM
Accumulation Register.ProductsOrg

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

RIGHT:

SELECT
GoodsOrganizations.Date,
ProductsOrganizations.Number
FROM
Accumulation Register.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 BT objects, having previously indexed them by the connection fields.

NOT RIGHT:

SELECT …

LEFT JOINT (
SELECT FROM INFORMATION REGISTER.Limits
WHERE …
LOAD BY ...
) ON …

RIGHT:

SELECT …
POST Limits
FROM Information Register.Limits
WHERE …
LOAD BY ...
INDEX BY…;

SELECT …
FROM Document.Realization of GoodsServices
LEFT JOIN Limits
ON …;

Joining records with virtual tables

There are situations when the system does not perform optimally when joining a virtual table with others. In this case, to optimize the query performance, you can try to put the virtual table in the temporary table, remembering to index the joined fields in the temporary table query. This is due to the fact that BTs are often contained in several physical tables of the DBMS, as a result, a subquery is made to select them, and the problem is similar to the previous point.

Using filters 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 perform a query optimally if the query overlaps non-indexed fields. If a temporary table is taken, it is also necessary to index the join fields.

There must be a suitable index for each condition. An index that meets the following requirements is suitable:

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

If the DBMS does not find the correct indexes, then 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 basics of query optimization aspects that every 1C expert should know.

A very useful free video course on query development and optimization, highly recommend for beginners and not only!

When developing reports, sometimes it becomes necessary to display a report for which data cannot be obtained using the query language. Such a situation may arise, for example, if a certain complex algorithm is used to calculate the data, or the data for the output of the report is not obtained from information base, and, for example, from external file... The report builder provides the ability to output a report from an arbitrary data source.
The following can be used as a data source for outputting a report:

Table of Values,
Result of Request,
SpreadsheetDocument Cells Region,
Tabular part,
Accumulation RegisterRecordSet,
DatasheetRecordSet,
Accounting RegisterRecordSet,
Calculation RegisterRecordSet.

In order for the report builder to display a report for an arbitrary data source, it is enough to set the data source description to the DataSource builder property. The DataSource report builder property can contain a value of the DataSource Description type. The DataSourceDescription object contains the data source itself, and also contains the 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,
PathData - contains a description of the dependence of the columns from each other. A column whose data path is obtained through a point from the data path of another field is considered an attribute of another column. If the field contains the word Representation separated by a dot, then this field is considered a representation for the column, from the data path 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 is 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 - a sign that this column can be used as a grouping of the report,
Total is a string containing an expression for calculating the total. For the report builder, the expression for this string matches the expression for calculating the total used in the query language,

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

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

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

ReportBuilder.DataSource = NewDataSourceDescription (ValueTableResult);

Further work with the report builder does not differ from working with the report builder in the query mode: the report builder provides its full functionality, except for the output of hierarchical totals. In addition, when working with an arbitrary data source, you cannot receive a query from the report builder that will be used to retrieve data from an infobase.

Working with a custom data source Query Builder is the same as working with a custom report Builder.

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

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

The fundamental part of the report is a 1C request. In the case of the ACS report, this is the majority of the report.

Sit down. Take a breath. Take it easy. 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 language of 1C requests.

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

It is also bilingual - that is, you can write in Russian or in English. It is exceptionally similar to the language SQL queries and those who know one can relax.

How 1C Requests are used

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

1C requests are:

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

Creation and execution of 1C requests

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

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

After the text of the 1C request is ready, it must be executed. For this, 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 dumped into a regular table:
Fetch = Query.Run (). Unload (); // Result is a table of values

Or just go around the lines.
Selection = Query.Run (). Select ();
While Fetch.Next () Loop
// Do something with the query results
End of Cycle;

Working with 1C requests

Basic principles of 1C requests

The basic principles of building a 1C query are -
SELECT ListFields FROM TABLE TABLE WHERE Conditions

An example of building such a 1C request:

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

List of tables 1C

You can see the table names in the configurator window. It is only necessary to write "Directory" instead of "Directories", for example "Directory.Nomenclature" or "Document.Realization of GoodsServices" or "Accumulation Register.Sales".

For registers, there are additional tables (virtual) that allow you to get the total figures.

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 balance register for a certain date

Accumulation Register.RegisterName.Turnovers (& 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 calculate them for us (add for example).

SELECT
// Quantity (FieldName) - counts the quantity
// Field AS OtherName - renames the field
Number (Link) AS Number of Documents Posted
FROM

WHERE
Carried out

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

SELECT
// just a document field
Organization,
// count the amount
Quantity (Reference) AS Quantity by organization
FROM
Document.Realization ofGoodsServices
WHERE
Carried out
LOAD BY

Organization

This 1C request will return us the number of documents for each organization (they also say "in the context of organizations").

We will additionally calculate the sum of these documents using a 1C request:

SELECT
// just a document field
Organization,
// count the amount

// calculate the amount

FROM
Document.Realization ofGoodsServices
WHERE
Carried out
LOAD BY
// must be used if the list of fields contains a counting 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.

SELECT
// just a document field
Organization,
// count the amount
Quantity (Link) AS Quantity By Organization,
// calculate the amount
Amount (Document Amount) AS Amount
FROM
Document.Realization ofGoodsServices
WHERE
Carried out
LOAD BY
// must be used if the list of fields contains a counting function () and one or more fields at the same time - then you need to group by these fields
Organization
RESULTS FOR 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 a table of values
Using the built-in functions for getting a value and manipulating values.

1C Query Builder

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

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 just to select a field, but to apply a summation function to it - after dragging, double-click on the field with the mouse. After that, on the Grouping tab, you will need to select (drag and drop) the required fields to group.

On the Conditions tab in the 1C query constructor, you can select the required filters in the same way (by dragging the fields by which you will make the selection). Don't forget to choose the correct condition.

On the Order tab, sorting is indicated. On the Totals tab - summing up totals.

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