Built-in language. Built-in language of the 1C: Enterprise system Creating a “Universal” report

20.01.2024

To work with queries, a built-in language object is used Request. It allows you to receive information stored in database fields in the form of a sample formed according to specified rules.

The query receives initial information from a set of tables. These tables represent data from real database tables in an easy-to-analyze form. They can be divided into two large groups: real And virtual.

Real tables, in turn, can be object (reference) or non-object (non-reference), Fig. 97.

Rice. 97. Query tables

A distinctive feature of real tables is that they contain data from any one real table stored in a database. For example, a real table is Directory.Clients, corresponding to the Clients directory, or the Accumulation Register.Material Remaining table, corresponding to the accumulation register of Material Remaining.

Virtual tables are formed primarily from data from multiple database tables. For example, the virtual table is Accumulation Register.Material Remainings.RemainingsAndTurnover, formed from several tables of the Material Remaining accumulation register.

Sometimes virtual tables can be formed from one real table (for example, the Prices.SliceLast virtual table is formed based on the Prices information register table). However, what all virtual tables have in common is that they can be given a number of parameters that determine what data will be included in these virtual tables. The set of such parameters may be different for different virtual tables, and is determined by the data stored in the source database tables.

Real tables are divided into object (reference) and non-object (non-reference) tables.

IN object (reference) tables information of reference data types is presented (directories, documents, plans of characteristic types, etc.). And in non-object (not reference)– all other data types (constants, registers, etc.).

Distinctive feature object (reference) tables is that they include a Link field containing a link to the current record. In addition, for such tables it is possible getting a custom representation of an object. These tables can be hierarchical, and the fields of such tables can contain nested tables (table parts).

The algorithm by which data will be selected from the source tables of the request is described in the text of the request in a special language - query language . The request text consists of several parts:

· description of the request;

· merging queries;

· ordering of results;

· AUTO ORDER;

· description of the results.

The only mandatory part of the request is the first one – the description of the request. All others are present as needed.

The query description defines the data sources, selection fields, groupings, etc.

Query merging defines how the results of multiple queries are combined.

Result ordering defines the ordering conditions for the query result rows.

AUTO ORDER allows you to enable automatic ordering of query result rows.

The summary description determines which totals should be calculated in the query and how the result should be grouped.

It should be noted that when a query language is used to describe data sources in a data composition system, the summary section of the query language is not used. This is due to the fact that the data composition system independently calculates the results based on the settings made by the developer or user.

Data composition system designed for creating custom reports in the 1C:Enterprise 8.1 system and consists of several main parts.

The initial data for the report layout contains data layout diagram – these are data sets and methods of working with them(Fig. 98).

The developer creates a data composition scheme in which he describes the request text, data sets, connections between them, available fields, data retrieval parameters and sets the initial layout settings - report structure, data layout, etc.

For example, a layout diagram may contain the following set of data (Figure 99).

Rice. 98. General scheme of working with the data composition system

Rice. 99. Example of a layout diagram (data set and query using it)

In Fig. Figure 99 shows the data composition schema designer window, which contains the data source, query text, and fields selected by the query.

The data composition system report that the user receives is not just a table of records that satisfy the query. It has a complex hierarchical structure and can consist of various elements, such as groupings, tables and charts. In this case, the user can change the existing one or even create a completely new report structure, configure the selection and design of the elements of the report structure he needs, get a transcript for each element, etc.

For example, the following report structure can be specified (Fig. 100):

Rice. 100. Structure of a possible report

The generated report may look like this (Fig. 101).

In the presented report, the table will consist of accumulation register entries SalesTurnover about clients and services provided to them. These records are grouped by the craftsmen who completed the orders. And in the grouping a list of masters will be displayed.

In general, a data composition system is a collection of several objects. When generating and executing a report, data is sequentially transferred from one object of the data composition system to another, until the final result is obtained - a document shown to the user.

The interaction algorithm of these objects is as follows: the developer creates a data layout scheme and default settings. In general, a large number of different reports can be created based on one data composition scheme. Data composition settings—either created by the developer or modified by the user—determine which report will be produced in a particular case.

Rice. 101. Sample report

Based on the layout design and the available settings, the layout builder creates a data layout layout. This is the stage of preparing for the execution of the report. The data composition layout is a ready-made task for execution by the composition processor. The layout layout contains the necessary queries, report area layouts, etc.

The data composition processor selects data from the infobase according to the layout layout, aggregates and formats this data.

The layout result is processed by the output processor, and as a result, the user receives the resulting spreadsheet document.

The sequence of operation of the layout system can be represented as the following diagram (Fig. 102):

Rice. 102. Scheme of operation of the layout system

3.12.1. Creating a report "Register of documents Provision of services"

Report Register of documents Provision of services will display a list of Service Provision documents existing in the database in the order of their dates and numbers.

Report. Give it a name Register of Documents Provision of Services. On the bookmark Basic click " Ready". In the data composition schema designer, add Dataset - Query Query constructor".

Select the object (reference) document table as the data source for the query Provision of Services. From this table, select the following fields (Fig. 103):

Date of; Number; Stock; Master; Client.

Rice. 103. Selected fields

After that, go to the bookmark Order and specify that the query result should be ordered by field value first date, and then - by field value Provision of Services. Link(Fig. 104).

Rice. 104. Order of query result output

Click " OK" and see what query the query constructor generated.

Go to bookmark Settings and add a new grouping to the report structure (Fig. 105).

Rice. 105. Command panel of the Settings tab

In the grouping field selection window, click the " OK" and on the bookmark Selected fields set the fields that will be displayed in the report:

Date of; Number; Stock; Master; Client (Fig. 106).

Rice. 106. Setting up the report structure

Register of documents for provision of services. Click " Form" and look at the result of the report (Fig. 107).

Rice. 107. Report Register of documents for provision of services

Report. Name it Rating of Services. On the bookmark Basic click " Open data composition diagram". In the Layout Designer dialog box that opens, click the " Ready".

In the data composition schema designer, create Dataset - Query(DataSet1) and click " Query constructor".

Nomenclature and virtual table of the accumulation register Sales.Turnover. To eliminate ambiguity of names in the query, rename the Nomenclature table to SprNomenclature(right-click context menu – Rename table).

Then select from the fields tables RefNomenclature.Link And SalesTurnover.RevenueTurnover(Fig. 108).

Rice. 108. Selected fields

Go to bookmark Connections and note that the designer has already created a connection between the two selected tables - the value of the Nomenclature register change must be equal to the reference to the Nomenclature directory element.

The only thing to do is reset the flag All at the register table and set it at the directory table SprNomenclature(Fig. 109).

Setting the flag All in the directory table will mean that all elements will be selected from the directory and these elements will be assigned the value of revenue turnover from the register. Thus, as a result of the request, all services will be present, and for some of them the revenue turnover will be indicated. For those services that were not provided in the selected period, nothing will be indicated.

Rice. 109. Establishing table links

Go to bookmark Conditions and set the conditions for selecting elements from the Nomenclature directory (Fig. 110). You will need to use query parameters when setting selection conditions. The first condition must be that the selected element is not a group (to do this, switch to the mode Arbitrary condition– set flag free). Then into the field condition enter the following text:

SprNomenclature.ThisGroup = FALSE

The second condition must be that the selected element is a service (this is Simple condition) (Fig. 110).

Rice. 110. Conditions for selecting elements

Later, before executing the request, in the parameter TypeNomenclature you need to pass the corresponding enumeration value.

Go to bookmark Unions/Aliases and specify that the directory item view will have an alias Service, and the register field will have an alias Revenue(Fig. 111).

Rice. 111. Unions/Aliases

Go to bookmark Order and specify that the query result should be sorted in descending order of the field value Revenue. Creation of the request is completed, click the button " OK". Look at the request text generated by the constructor.

Go to bookmark Resources and click " >> ", so that the designer selects all available resources for which totals can be calculated. In your case, this is the only resource Revenue(Fig. 112).

Rice. 112. Data composition schema resources

Go to bookmark Options(Fig. 113).

Rice. 113. Data composition options

On this tab you will see three options: Beginning of period, End of Period And TypeNomenclature. The first parameter is the beginning of the period for calculating the totals, the second is the end of the period. As a result, the source table will contain only the turnover calculated in the transferred period.

For parameter Beginning of period set the title that will be displayed to the user - start date.

Here you should always remember that if you pass a date as these parameters, then the date also contains the time accurate to the second.

Let's say it is known in advance that the user will not be interested in the results of the report in the periods specified with an accuracy of up to a second. In this case, two features should be taken into account.

Firstly, the user should be spared the need to specify the time when entering the date. To do this, change the existing type description for the parameter Beginning of period. Double click in a cell Available types, corresponding to the parameter Beginning of period, press the select button " … " and at the bottom of the data type editing window set Composition of dates in meaning date(Fig. 114).

Click " OK".

The second feature is that by default the time in the date is set to 00:00:00. Therefore, if the user specifies a report period from 10/01/2009 to 10/31/2009, the register totals will be calculated from the start of the day 10/01/2009 00:00:00 to the start of the day 10/31/2009 00:00:00. Thus, data for the 31st day, other than the beginning of the day, will not be included in the calculation, which will greatly surprise the user. To eliminate this situation, add another parameter in which the user will enter the end date. Parameter value End of Period will be calculated automatically so that it indicates the end of day of the date entered by the user. Therefore, for the parameter End of Period set the flag Availability limitation.

Rice. 114. Editing date composition

Using the command bar button, add a new parameter named End Date (Figure 115).

Rice. 115. Adding the End Date parameter

For this parameter, the platform will automatically generate a title - expiration date. Leave it unchanged. Set the parameter value type – date. In this case, as for the parameter Beginning of period, indicate the composition of the date – date.

Please note that by default the parameter you added is available to the user (the accessibility restriction in the last column is cleared).

Go to parameter End of Period. The system has set an availability restriction flag for it. Do not uncheck it because the value of this parameter will be calculated based on the value set by the user for the parameter Expiration date.

To set the formula by which the parameter value will be calculated End of Period, use the expression language of the data composition system. It contains the function EndPeriod(), which allows you to get the date corresponding to the end of a period, for example, a specified day.

In a cell Expression set the parameter End of Period the following expression:

As a result of the above actions, the layout parameters will look like this (Fig. 116):

Rice. 116. Layout System Options

Finally, configure the parameter TypeNomenclature. Since the report should display revenue received only from the sale of services, the value of the parameter TypeNomenclature the user should not change it. It must be specified directly in the layout diagram as . Parameter usage restriction flag TypeNomenclature the platform installed by default, so you just need to specify the desired enumeration value TypesNomenclature in a cell Meaning, corresponding to the parameter TypeNomenclature(Fig. 117).

Rice. 117. Setting the value of the Nomenclature Type parameter

Proceed to creating the report structure. On the bookmark Settings add a grouping and again leave out the grouping field. On the bookmark Selected fields specify fields Service And Revenue(Fig. 118).

Then go to the bookmark Other settings and set the report title - Rating of Services(Fig. 119).

Rice. 119. Setting the report title

Start 1C:Enterprise in debug mode.

Select a report Service rating, but before pressing the " Form", open Settings and on the bookmark Data Options set the reporting period from 03/01/2004 to 04/30/2004 (Fig. 120). If the creation dates of your documents differ from those given in the example, then the reporting period should be set differently.

Rice. 120. Report settings

Click " OK" and generate a report. The result will look like this (Fig. 121):

Rice. 121. Result of report execution

Now change the end date to 03/31/2004. Data for March 31 is included in the report (Fig. 122).

Rice. 122. Result of running the report

3.12.3. Creating a report "Revenue of craftsmen"

Report Craftsmen's revenue will contain information about how much revenue was received by Master LLC thanks to the work of each of the masters, with details for all days in the selected period and a breakdown for clients served on each day. Using this report as an example, you will learn how to build multi-level groupings in a query and how to traverse all dates in the selected period.

Create a new configuration object Report. Name it RevenueMasters and run the main data composition schema designer.

Add Dataset - Query(DataSet1) and click " Query constructor". Select the virtual table of the accumulation register Sales.Turnover.

Set one of the parameters of this virtual table − Periodicity. To do this, go to the field Tables and press the "" button (Fig. 123).

Rice. 123. Changing virtual table parameters

In the parameters window that opens, set the parameter value Frequency – Day(Fig. 124). Press the button " OK".

Rice. 124. Virtual table parameters

After that, select the following fields from the table:

· SalesTurnover.Master;

· SalesTurnover.Period;

· SalesTurnover.Client;

· SalesTurnover.RevenueTurnover (Fig. 125).

Rice. 125. Selected fields

Now go to the bookmark Unions/Aliases and set an alias Revenue for field SalesTurnover.RevenueTurnover(Fig. 126).

Rice. 126. Unions/Aliases

On the bookmark Grouping define that grouping will be done by fields Master, Period And Client, and the field values RevenueTurnover will be summed up (Fig. 127).

Rice. 127. Grouping fields

On the bookmark Order specify that the query result will be sorted in ascending order of the field value Period. Click " OK". Consider the request text generated by the constructor.

Now move on to editing the data layout schema. On the bookmark Resources click " >> " and make sure that the constructor has selected the only available resource - Revenue.

On the bookmark Options follow the same steps as when creating the previous report.

For parameter Beginning of period set the title start date. In field Available types set the date composition – date.

For parameter End of Period set the expression:

EndPeriod(&EndDate, "Day")

In field Limitation availability set the availability restriction flag.

Finally, add one more parameter - Expiration date, set its type as date, date composition – date.

As a result of these actions, the data composition parameters will look like this (Fig. 128):

Rice. 128. Data composition options

Now you need to create the report structure. On the bookmark Settings sequentially create two nested groupings: top level - by field Master, embedded in it - by field Period. Then add another grouping nested within the grouping by field Period, – Detailed entries(without specifying the grouping field) (Fig. 129).

Rice. 129. Report structure

Now, being at the global report level, go to the tab Selected fields and add fields to the list Client And Revenue.

Finally, from the global report level, go to the tab Other settings and change the following settings. For parameter set value Separately and only as a result; for parameter set the value Start and for the parameter Heading set the value Craftsmen's revenue(Fig. 130).

Rice. 130. Setting the output of general totals to the beginning

Run 1C:Enterprise in debug mode and look at the result of the report “Foremen's Revenue” for the period from 03/01/2004 to 04/30/2004 (Fig. 131).

Rice. 131. Result of running the report

If you remember, this report should show data with detail for all days in the selected period. Now only those days for which there are non-zero entries in the accumulation register table are displayed. Therefore, now it is necessary to change the report settings so that the report includes every date from the period for which the report was generated.

To do this, return to the configurator mode and fine-tune the report structure. So far, all the structure settings you have made have applied to the entire report as a whole. But the data composition system also allows you to customize each element of the structure separately.

You will need to change the grouping setting Period. In order to go to the settings of this particular grouping, place the cursor on this grouping in the structure field, and then click the " Period" in the command panel (Fig. 132).

Rice. 132. Grouping settings Period

The settings available for this grouping will be displayed at the bottom of the form.

Go to bookmark Grouping fields. For field Period install Add-on type – Day(Fig. 133).

Rice. 133. Setting the period addition type

Thus, you indicated that for this grouping, existing records with a non-zero resource value will be supplemented with records for each of the days.

After this, you should indicate in what period such an addition will be performed. In the fields located one line below, you can enter the start and end dates of this period. But specifying dates explicitly is not suitable, because... the user can generate a report for an arbitrary period. It is necessary that date addition be performed not in some fixed period, but precisely in the period that the user selected for the entire report.

In order to ensure that the report works exactly like this, enter the editing mode of the first field (for example, by double-clicking on it) and click on the clear button " X". After that, click on the button " T", you will be able to select the type of data displayed in this field. Select Data composition field(Fig. 134).

Rice. 134. Selecting a data type

Click " OK". Now press the selection button in the input field " … " and in the field selection window that opens, select the option Beginning of period(Fig. 135). Click " OK".

Rice. 135. Field selection

For the second input field, similarly indicate that the end date of the period will be obtained from the parameter Expiration date(Fig. 136).

Rice. 136. Grouping settings Period

Start 1C:Enterprise in debug mode and run the report Craftsmen's revenue for the period from March 20, 2004 to April 20, 2004 (Fig. 137).

Rice. 137. Result of running the report

3.12.4. Creating a "List of Services" report

Report Service list will contain information about what services Master LLC provides and at what price. Using his example, you will become familiar with the possibility of obtaining the latest values from a periodic register of information and displaying hierarchical directories.

Create a new configuration object Report. Name it Service list and run the Data Composition Schema Designer. Create a new one Dataset - Query(DataSet1) and call the query constructor.

Select the object (reference) directory table Nomenclature and virtual information register table Prices.CuttingLatest. To eliminate the ambiguity of names in the query, rename the Nomenclature table to SprNomenclature.

Call the dialog for entering virtual table parameters PricesCuttingLatest and indicate that the period will be passed in the parameter Report Date(Fig. 138).

Rice. 138. Virtual table parameters

Then select the following fields from the tables:

· SprNomenclature.Parent;

· PricesSlice of Latest.Price (Fig. 139).

Rice. 139. Selected fields

Go to bookmark Connections, reset the flag All at the register table and set it at the directory table.

On the bookmark Conditions set the condition for selecting directory elements Nomenclature– the selected elements must correspond to the type of item passed in the request parameter TypeNomenclature(Fig. 140).

Rice. 140. Conditions for selecting elements

On the bookmark Unions/Aliases indicate that the field Parent will have a nickname Group of Services, and the field Link – Service(Fig. 141).

Rice. 141. Unions/Aliases

Go to bookmark Grouping and specify that the grouping will be done by field SprNomenclature.Parent. There is no need to specify the values of the summed fields (Fig. 142).

Fig. 142. Grouping fields

Click " OK". Look at the text of the request.

Proceed to edit the data layout diagram. On the bookmark Resources by pressing the " >> " select the only available resource Price.

On the bookmark Options set the parameter value TypeNomenclature How Transfer. Types of Nomenclature. Service. Additionally, remove the accessibility restriction for the parameter Report Date and give it a title - Report date. In field Available types set the date composition – date. For parameter Period, on the contrary, set the availability limit (Fig. 143).

Rice. 143. Layout scheme parameters

Proceed to creating the report structure. Go to bookmark Settings and create a grouping by field Group of Services, indicating the grouping type Hierarchy. Within this grouping, create another grouping without specifying a group field. It will contain detailed report entries (Fig. 144).

Rice. 144. Report structure and highlighted fields

Go to bookmark Selected fields and specify that the Service and Price fields will be displayed in the report (Fig. 145).

Rice. 145. Report structure

Finally, customize the appearance of the report on the tab Other settings. Since the report will simply be a list of services provided, in which prices for specific services are interesting, display the resource values Price for each of the groupings and for the entire report as a whole does not make sense.

To prevent grand totals from being displayed in a report, set the option Vertical arrangement of grand totals in meaning No.

Then go to the specific grouping settings - Group of Services. For parameter Location of totals indicate the value of this grouping No. Return to the settings for the entire report as a whole.

For parameter Location of grouping fields enter the value Separately and only as a result(this way the report will be better “readable”). Finally, set the title of the report - Service list.

Start 1C:Enterprise in debug mode and first of all open the periodic register Prices.

Add another value for the service to it Diagnostics: new service price as of April 1, 2004 – 350 (Fig. 146). This will allow you to test the report.

Rice. 146. Records of the "Prices" register

Now run the report Service list as of March 31, 2004 (Fig. 147).

Rice. 147. Result of running the report

Your report correctly reflects the price of the Diagnostics service as of March 31, 2004 – 200 rubles.

Run the report again, but now for a different date - 04/01/2004 (Fig. 148).

Rice. 148. Result of running the report

As you can see, the new price for the Diagnostics service is shown - 350 rubles.

Report Customer rating will show what the income from the provision of services to each of the clients is for the entire period of operation of Master LLC. This example will demonstrate the possibility of using a diagram to display the result of a query.

Diagram is a control element designed for placement in tables and forms of charts and graphs of various types of the 1C:Enterprise system.

Logically diagram is a collection of points, series and series values at a point(Fig. 149).

As a rule, moments or objects for which you obtain characteristic values are used as points, and characteristics whose values interest you are used as series.

For example, a diagram of sales of product types by month will consist of points - months, series - product types and values - sales turnover.

A diagram as an object of the built-in language has three areas that allow you to control the design of the diagram: the plotting area, the title area and the legend area (Fig. 150).

The diagram can be inserted into the report structure as a separate element. When creating a report Customer rating the diagram will be used in the data composition schema settings structure.

Rice. 149. Example diagram

Rice. 150. Chart Areas

Create a new configuration object in the configurator Report. Name it Customer Rating, open its main data layout schema.

Create data set - query(DataSet1) and call the query constructor. Select virtual accumulation register table Sales.Turnover and from it there is one field - SalesTurnover.Client.

Then add a new field (icon Add in the command panel above the list of fields) and using the expression builder, define it as the difference between revenue and cost (Fig. 151).

Rice. 151. Custom Field Expression Editor

As a result, the list of selected fields will look like this (Fig. 152):

Rice. 152. Selected fields

On the bookmark Unions/Aliases specify that the calculated field will have an alias Income(Fig. 153).

Rice. 153. Unions/Aliases

On the bookmark Order specify that the result rows should be ordered in descending order of the field value Income. Click " OK" and see what text the query constructor generated.

Go to bookmark Resources and add a field Income to layout diagram resources. Then go to the bookmark Settings in order to create the report structure.

Unlike all previous reports, the structure of which contained groupings, this time add a diagram to the report structure (Fig. 154).

Rice. 154. Report structure

Add grouping by field to chart points Client. Leave the diagram series unchanged.

Now go to the bookmark Selected fields and select the field Income for output to the report. The structure of the report should take the following form (Fig. 155):

Rice. 155. Report structure and chart settings

On the bookmark Other settings set the report title – Customer rating, and also select the chart type – Circular volumetric.

Start 1C:Enterprise in debug mode and open the report Customer rating(Fig. 156).

Rice. 156. Pie 3D chart in a report

Please note that when you hover your cursor over a sector of the chart, a tooltip appears.

Open the report settings and change the chart type to Measuring. Regenerate the report (Fig. 157).

Rice. 157. Measuring diagram in the report

3.12.6. Create a Generic report

Using the example of creating a universal report, you will learn how a data composition system can interact with a pivot table.

Create a new configuration object in the configurator Report. Give it a name Universal. Open its main data composition schema and create a new one data set - query(DataSet1).

Call the query designer and select the accumulation register virtual table Sales.Turnover. From this table, select all fields (Fig. 158).

Rice. 158. Selected fields

On the Grouping tab using the buttons " >> " select all fields available for grouping, and add all resources to the Summarized fields (Fig. 159).

Rice. 159. Grouping fields and resources

Click " OK" and look at the text generated by the query constructor.

Finish creating the data composition diagram with the following on the layout diagram designer tab Resources select all available resources (click " >> "). At this point, work with the data composition scheme is completed, close it and return to the window for editing the configuration object Report Universal.

Go to bookmark Forms and create the main report form by clicking on the view button next to the table field Main report form. Since there is no main form yet, the system will call the form designer. Agree with what the system offers by default and immediately click the " Ready".

The main report form will open on the screen. As you can see, the system has already inserted the spreadsheet document field for you and assigned it a name Result(Fig. 160).

Rice. 160. Editing the report form

You need to insert a pivot table into this field. But the spreadsheet document field cells Result are now in mode View only. In order to insert a pivot table, you need to remove this mode, so click on the spreadsheet document field and do Table –> View –> View Only. You can now edit the table field.

Place the cursor in the top left cell of the spreadsheet document field and execute Table –> Built-in tables –> Insert pivot table. The system will add a pivot table to the table field and open the pivot table fields window (Fig. 161).

Rice. 161. Pivot table

In order to display data in a pivot table, you will need to write several lines in the form module. Open the report form module and add the variable you need to the text:

Variable PivotTableSource;

After that, create an event handler for the report form When opening(In the properties window of the Universal Report form) and add the following text to it:

Procedure OnOpen()

PivotTableSource = newDataCompositionPivotTableDataSource;

PivotTableSource.SetSchema(DataCompositionSchema);

Form Elements.Result.EmbeddedTables.PivotTable1.DataSource =

PivotTableSource;

PivotTableSource.SetSettings(SettingsComposer.Settings);

EndProcedure

With this text, you have set the PivotTable as a data source to the data layout schema and set the settings builder settings for it.

Start 1C:Enterprise in debug mode and open the report Universal. A report form and a window for selecting pivot table fields will appear on the screen (Fig. 162).

Rice. 162. Universal report

Place the resource value RevenueTurnover in Data area, dimension Nomenclature– in the Row area, and the dimension Master– in the Columns area. The report will take the following form (Fig. 163):

Rice. 163. Result of running the report

Now in the pivot table field selection window, expand the group Nomenclature and add the value (No hierarchy) to the rows (in the drop-down list of the "Pivot Table Fields" window, select Place in lines), and the measurement Client add to columns (in the drop-down list of the "Pivot Table Fields" window, select Place in columns). The report will change its appearance (Fig. 164).

Rice. 164. Result of running the report

Thus, using these data composition schemes, you provided the user with an alternative opportunity to independently generate a report on the register Services provided.

3.12.7. Creating a "Universal 2" report

Using the example of creating a second universal report, you will learn how to directly control the layout system settings and create layouts based on standard design options.

Create a new configuration object Report With name Universal2. Open its main data composition schema, create a new data set - query(DataSet1) and run the query designer. Select all fields from the accumulation register virtual table Sales.Turnover and click " OK". On the bookmark Resources Data Composition Schema Designer, click " >> " and make sure that the system has selected three resources: RevenueTurnover, QuantityTurnover And CostTurnover.

On the bookmark Settings create a new group Detailed entries(without selecting a grouping field).

Now bookmarked Forms report editing windows Universal2 Using the designer, create the main report form and edit it.

Stretch the shape and place two inscriptions with names in it Fields And Order and with headings Fields: And Order: accordingly (Fig. 165).

Under the inscription Fields CommandBarFields. Under the inscription Order position the command bar with the name CommandBarOrder. Under the command panels, place table fields with names accordingly TabularFieldFields And TableFieldOrder.(Fig. 166). In the Command Panel Properties window CommandBarFields set the flag AutoFill And Action Source – TabularField. In the Command Panel Properties window CommandBarOrder set the flag AutoFill And Action Source – TableFieldOrder.

Rice. 165. Editing the report form

Rice. 166. Report form Universal2

Now for the table field TabularFieldFields set the data source as ReportObject.SettingsLinker.Settings.Selection(Fig. 167).

Rice. 167. Set the data source for the table field

Similar to a table field TableFieldOrder indicate the data source ReportObject.SettingsLinker.Settings.Order.

Having completed the described steps, you have associated the controls located in the form with the settings of the report layout system - Choice And Order. Choice allows you to manage the list of fields that will be included in the report result, and Order specifies the order in which the result lines are displayed. The user will need command panels to configure the values of these fields. Using command bar properties Autofill And Action Source, automatic generation of commands was obtained based on the type of data contained in each of the table fields.

The 1C:Enterprise 8 system has a small set of ready-made design layouts for use by the data composition system. They are contained in the object LibraryLayoutDesignLayoutData.

Place another inscription with the name Decor and title Decor:, and to the right of it place a selection field with the name Selection FieldDesign(Fig. 168) without signature.

Rice. 168. Editing the report form

In the properties of the selection field, open the window Pick List Lines and fill it with the names of layouts from the data layout layout library (see Figure 169).

Rice. 169. Filling a list of values for a selection field

To have a select box change the applied layout of the layout system, create a handler When it changes selection fields:

Procedure SelectionFieldDesignOnChange(Element)

OutputParameters = ComposerSettings.Settings.OutputParameters;

Design Layout Parameter = Output Parameters. FindParameterValue(New

DataLayoutParameter("DesignLayout"));

AppearanceLayoutParameter.Usage = True;

AppearanceLayoutParameter.Value = SelectionFieldAppearance;

EndProcedure

This text for the data composition parameter LayoutDesign you specify the name of the layout that should be used to display the report, and set the flag for using the design layout.

Start 1C:Enterprise in debug mode and open the report Universal2.

Add the following fields to the selected fields:

· Master;

· Nomenclature;

· RevenueTurnover.

Set the sort order:

· Ascending Master;

· Nomenclature.Type of Nomenclature in descending order;

· Nomenclature in ascending order.

Select a design, for example Green, and click the " Form". The result will look like this (Fig. 170):

Rice. 170. Result of running the report

Now change the report generation conditions. Select fields Client, Nomenclature And RevenueTurnover. The sort order will be in ascending order of the field value Client, and the design option is Sea. Run the report (Fig. 171).

Rice. 171. Result of running the report

3.13. Optimization of the document "Provision of Services"

Let us assume that the management of Master LLC has made a final decision on the procedure for calculating the cost of materials consumed in the process of providing services. Namely, the current cost of the material will be determined as the quotient of the total cost of the balance of this material and its remaining quantity in the warehouse.

In addition, 1C:Enterprise 8.1 tools allow you to control the speed of the document posting procedure through the use of a request mechanism.

Thus, document optimization Provision of Services, has two goals:

· determination of the cost of consumable materials when conducting a document;

· increasing the speed of document processing procedures.

Delete in document Provision of Services table section details Price, which will no longer be needed. Also remove the corresponding column from the table field located in the document form Provision of Services.

After this, completely remove the contents of the event handler ProcessingConducting in the document module Provision of Services and create a template for the implementation procedure in it. The text of a request executed in the operational mode (i.e., for the current date and time) will differ from the request executed in a non-operational mode (i.e., “backdated”). Therefore, the formation of the request text must be included in the If... Else... EndIf condition:

Request = New Request;

If Mode = DocumentConductMode.Operational Then

Request.Text =

Otherwise

Request.Text =

endIf;

First, create a request that will be executed during the operational processing of the document. The value of the Query.Text variable will be generated automatically. To do this, place the cursor in front of the semicolon, call the context menu by clicking the right mouse button, and select Query constructor. Agree to create a new request. The Query Builder dialog box appears on the screen. Expand the table List of Nomenclatures tabular part of the document Provision of Services and select the fields from it:

· Nomenclature;

· Quantity;

· Nomenclature.Type of Nomenclature;

These fields will be needed to set the values of register dimensions and their resources. In addition, the field TypeNomenclature you will need to analyze what the nomenclature specified in the document is: a material or a service (Fig. 172).

Rice. 172. Selected fields

To specify resource values Price registers Cost of materials And Sales you will need to calculate the current cost of the item as the quotient of the cost of the remainder of this material and its remaining quantity.

Therefore, add two more tables to the list of selected tables (Fig. 173).

RegisterAccumulations.Remaining Materials.Remainings.

Rice. 173. Selected tables

You must set the same parameters for these virtual tables. First select the virtual table Remaining MaterialsRemaining Materials, call the context menu by right-clicking and select Virtual Table Options. The parameters will include the point in time at which the remainder of these registers should be received, and the condition for receiving the data.

The condition for receiving data indicates that balances should be received only for those item items that are contained in the posted document (before executing the request, a list of all item items contained in the posted document must be passed to the Document Item List parameter) - Fig. 174.

Rice. 174. Virtual table parameters

Set the same parameters for the second virtual table Cost of Materials Remaining. Next, select the CostRemaining and QuantityRemaining fields from these virtual tables (Fig. 175).

Rice. 175. Selected fields

Select the virtual accumulation register table again Remaining MaterialsRemaining Materials and rename it to Remaining MaterialsRemainingStock(Fig. 176).

Rice. 176. Selected tables

For this virtual table, also specify the Time Point parameter, and in the condition write that the material must be in the item list and the warehouse must be equal to the warehouse specified in the document (Fig. 177).

Rice. 177. Parameters of the table Material RemainingStockRemaining

Now from this virtual table select the QuantityRemaining field (Fig. 178).

Rice. 178. Selected fields

At this point, the formation of the list of selected fields is completed, and you can proceed to setting the connection conditions between the selected tables. To do this, go to the bookmark Connections. Each of the virtual tables must be linked to the document table in such a way that for all records in the document table the existing records in the virtual table must be selected, and the nomenclature in the document table must be equal to the material from the virtual table (Fig. 179, a–b).

Rice. 179a. Selected tables

Rice. 179b. Relationship conditions between tables

Now go to the bookmark Additionally and set the flag For change. Flag For change allows you to pre-lock the reading of specified data (which can be read by a transaction on another connection) already during reading, in order to avoid deadlocks during writing. This clause makes it possible to specify in a query the tables whose read data is intended to be modified.

Since you are planning to write the accumulation registers Remaining Materials And Cost of materials, specify the tables of these registers as tables to change (Fig. 180).

Rice. 180. Specify tables to change

Go to bookmark Conditions and set a condition for selecting only the rows of the document being posted from the document table (a link to it will be passed to the Link query parameter) - fig. 181. To do this, double-click the left mouse button on the field Link.

Rice. 181. Condition for selection from the document table

Go to bookmark Unions/Aliases and set the following field aliases: ItemItemView –> ItemType; QuantityRemaining1 –> QuantityInStock.

Click " OK" and look at the request text generated by the constructor (the value of the Request.Text variable):

Provision of ServicesList of Nomenclature.Nomenclature.Type of Nomenclature AS Type of Nomenclature,

Cost of Materials Remaining. Cost Remaining,

Remaining MaterialsRemainingInStock.QuantityRemaining AS QuantityInStock

LEFT CONNECTION Register Accumulations. Cost of Materials. Remainings (&Moment of Time, Material B (& List of Document Nomenclature)) AS Cost of Materials Remaining

Material B (&List of Document Nomenclature)) AS Remaining MaterialsRemaining

Software Provision of ServicesList of Nomenclature.Nomenclature =

LEFT CONNECTION Register Accumulations. Material Remainings. Remainings (&Point of Time,

Material B (&Document Nomenclature List) AND Warehouse = &WarehouseInDocument)

HOW TO REMAIN MATERIALS REMAINS IN STOCK

Software Provision of ServicesList of Nomenclature.Nomenclature =

Remaining MaterialsRemaining Stock.Material

FOR CHANGE

Accumulation Register. Cost of Materials. Balances,

RegisterAccumulations.Remains of Materials.Remains

The text of the request for the case of non-operative document posting will be almost the same, except that it will lack the third left join and, accordingly, the field Quantity in stock, because In this case, there is no need to check the balances. Place the cursor before the semicolon located in the section Otherwise(see page 135), and paste the following query text:

Provision of ServicesList of Nomenclature.Nomenclature,

Provision of ServicesList of Nomenclature.Quantity,

Provision of ServicesList of Nomenclature.Nomenclature.Type of Nomenclature HOW

Type of Nomenclature,

Provision of ServicesList of Nomenclature.Amount,

Remaining MaterialsRemaining.QuantityRemaining,

Cost of Materials Remaining. Cost Remaining

Document.Provision of Services.List of Nomenclatures HOW to Provide ServicesList of Nomenclatures

LEFT CONNECTION Register Accumulations. Cost of Materials. Remainings (&Moment of Time, Material B (&List of Document Nomenclature))

HOW TO CostMaterialsRemaining

Software Provision of ServicesList of Nomenclature.Nomenclature =

Cost of Materials Remaining Material

LEFT CONNECTION Register Accumulations. Material Remainings. Remainings (&Point of Time,

Material B (&Document Nomenclature List))

HOW TO LEAVE MATERIALS Leftovers

Software Provision of ServicesList of Nomenclature.Nomenclature =

Remaining MaterialsRemaining Material

FOR CHANGE

Accumulation Register.Cost of Materials.Remains;

Now add the request parameters to the handler text (add only those commands in bold to the corresponding lines):

Request = New Request;

Request.SetParameter("WarehouseInDocument", Warehouse);

If Mode = DocumentConductMode.Operational Then

Request.Text =

| Accumulation Register. Cost of Materials. Remainings";

endIf;

Request.SetParameter("TimePoint", TimePoint());

Request.SetParameter("List of Document Nomenclature",

List of Nomenclature.UnloadColumn("Nomenclature"));

Requests are designed to extract and process information from the database to provide it to the user in the required form. Processing here means grouping fields, sorting rows, calculating totals, etc. You cannot change data using queries in 1C!

The request is executed as per the given instructions − request text. The request text is compiled in accordance with the syntax and rules query language. The 1C:Enterprise 8 query language is based on the standard SQL, but has some differences and extensions.

Scheme of working with a request

The general scheme of working with a request consists of several successive stages:

Creating a Request object and setting the request text;
Setting request parameters;
Executing a request and getting the result;
Bypassing the request result and processing the received data.

1. Object Request has the property Text, to which you need to assign the request text.

// Option 1
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM

|WHERE
;

// Option 2
Request = New Request("CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency");

2. Setting parameter values is carried out using the method SetParameter(< Имя>, < Значение>) . Parameters in the request text are indicated by the symbol “ & " and are usually used in selection conditions (WHERE section) and in virtual table parameters.

Request);

3. After assigning the text and setting the parameters, the request must be executed and the execution result obtained. Execution is carried out by the Execute() method, which returns an object Query Result. From the query result you can:

get a selection using the Select method (< ТипОбхода>, < Группировки>, < ГруппировкиДляЗначенийГруппировок>) ;
upload values to a value table or value tree using the Upload method (< ТипОбхода>) .

// Receive a sample

Sample = Query Result. Choose();

// Getting a table of values
RequestResult = Request. Run();
Table = Query Result. Unload();

4. You can bypass the query result selection using a loop:

Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

A complete example of working with a request might look like this:

// Stage 1. Creating a request and setting the request text
Request = New Request;
Request . Text =
"CHOOSE
| Currency rates.Period,
| Currency rates.Currency,
| Currency rates.Rate
|FROM
| Register of Information.Currency Rates AS Currency Rates
|WHERE
| Currency rates.Currency = &Currency";

// Stage 2. Setting parameters
Request . SetParameter("Currency" , SelectedCurrency);

// Stage 3. Executing the query and getting the sample
RequestResult = Request. Run();
Sample = Query Result. Choose();

// Traversing the selection
Bye Sample.Next() Loop
Report(Selection.Course);
EndCycle;

Composition of the request text

The request text consists of several sections:

Request Description— list of selectable fields and data sources;
Merging queries— expressions “UNITE” and “UNITE ALL”;
Organizing results— the expression “ORDER BY...”;
Auto-order— expression “AUTO ORDERING”;
Description of results- the expression “RESULTS ... BY …”.

Only the first section is mandatory.

Temporary tables and batch queries

1C query language supports the use temporary tables— tables obtained as a result of a query and stored on a temporary basis.

You can often encounter a situation where you need to use not database tables as the source of a query, but the result of executing another query. This problem can be solved using nested queries or temporary tables. The use of temporary tables allows you to simplify the text of a complex query by dividing it into its component parts, and also, in some cases, speed up query execution and reduce the number of locks. To work with temporary tables, use the object TimeTable Manager. A temporary table is created using the PLACE keyword followed by the name of the temporary table.

ManagerVT = New TemporaryTablesManager;
Request = New Request;
Request . ManagerTemporaryTables = ManagerVT;

Request . Text =
"CHOOSE
| Currencies.Code,
| Currencies.Name
|Place in Currency
|FROM
| Directory.Currencies AS Currencies";

RequestResult = Request. Execute();

To use the VTVcurrency temporary table in other queries, you need to assign a common temporary table manager to these queries—VT Manager.

Batch request is a request that contains several requests separated by the “;” character. When executing a batch query, all queries included in it are executed sequentially, and the results of all temporary tables are available to all subsequent queries. Explicitly assigning a temporary table manager to batch queries is not necessary. If a temporary table manager is not assigned, then all temporary tables will be deleted immediately after the query is executed.

For batch queries, the ExecuteBatch() method is available, which executes all queries and returns an array of results. Temporary tables in a batch query will be represented by a table with one row and one column “Count”, which stores the number of records. To debug batch requests, you can use the method Execute Batch WITH INTERMEDIATE DATA() : It returns the actual contents of temporary tables, not the number of records.

// Example of working with a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Currencies.Name
|FROM
| Directory.Currencies AS Currencies
|;
|SELECT
| Nomenclature.Name
|FROM
| Directory. Nomenclature AS Nomenclature";

Batch Result = Request. ExecuteBatch();

TZCurrencies =PacketResult[ 0 ]. Unload();
TZNomenclature = Package Result[ 1 ]. Unload();

// An example of using temporary tables in a batch request
Request = New Request;
Request . Text =
"CHOOSE
| Products. Link HOW TO Product
|PLACE VTProducts
|FROM
| Directory.Nomenclature HOW Products
|WHERE
| Products.Manufacturer = &Manufacturer
|;
|SELECT
| VTTProducts.Product,
| Vocational school. Quantity,
| Vocational school.Price,
| Vocational school.Link AS DocumentReceipts
|FROM
| VT Products AS VT Products
| LEFT CONNECTION Document. Receipt of Goods and Services. Goods AS PTU
| Software VTProducts.Product = PTU.Nomenclature";

Request . SetParameter( "Manufacturer", Manufacturer);

RequestResult = Request. Run();
Sample = Query Result. Choose();

Bye Sample.Next() Loop

EndCycle;

Virtual tables

Virtual tables- these are tables that are not stored in the database, but are generated by the platform. At their core, these are nested queries against one or more physical tables executed by the platform. Virtual tables receive information only from registers and are mainly intended for solving highly specialized problems.

The following virtual tables exist (possible parameters are indicated in parentheses):

For information registers:
- SliceFirst(<Период>, <Условие>) — the earliest records for the specified date;
- SliceLast(<Период>, <Условие>) — the latest records for the specified date;
For accumulation registers:
- Leftovers(<Период>, <Условие>) — balances as of the specified date;
- Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <Условие>) - Period transactions;
- RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <Условие>) — balances and turnover for the period;
For accounting registers:
- Leftovers(<Период>, <УсловиеСчета>, <Субконто>, <Условие>) — balances as of the specified date by account, dimensions and sub-accounts;
- Revolutions(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчета>, <Субконто>, <Условие>, <УсловиеКорСчета>, <КорСубконто>) — turnover for the period in the context of accounts, measurements, corr. accounts, subconto, cor. subconto;
- RemainsAndTurnover(<НачалоПериода>, <КонецПериода>, <Периодичность>, <МетодДополненияПериодов>, <УсловиеСчета>, <Субконто>, <Условие>) — balances and turnover in the context of accounts, measurements and sub-accounts;
- TurnoverDtKt(<НачалоПериода>, <КонецПериода>, <Периодичность>, <УсловиеСчетаДт>, <СубконтоДт>, <УсловиеСчетаКт>, <СубконтоКт>, <Условие>) — turnover for the period by account Dt, account Kt, Subconto Dt, Subconto Kt;
- MovementsSubconto(<НачалоПериода>, <КонецПериода>, <Условие>, <Порядок>, <Первые>) — movements together with subconto values;
For calculation registers:
- Base(<ИзмеренияОсновногоРегистра>, <ИзмеренияБазовогоРегистра>, <Разрезы>, <Условие>) — basic data of the calculation register;
- DataGraphics(<Условие>)—graph data;
- ActualActionPeriod(<Условие>) is the actual period of validity.

When working with virtual tables, you should apply selections in the parameters of virtual tables, and not in the WHERE condition. The query execution time greatly depends on this.

Query constructor

To speed up the input of query texts, the platform has special tools: Query constructor And Query constructor with result processing. To call constructors, you need to right-click and select the required item:

Constructors can also be called from the main menu Text.

Using the query builder, the programmer can interactively construct the query text. To do this, select the necessary tables and fields with the mouse, establish relationships, groupings, totals, etc. This approach saves time and eliminates possible errors. As a result of its work, the query constructor generates the query text.

The query constructor with result processing, in addition to generating the query text, creates a ready-made code fragment for receiving and processing data.

RequestSchema object

The platform allows you to programmatically create and edit the request text using the object Request Schema. An object has a single property Batch of Requests, in which the object stores the properties of all queries currently being edited. The RequestSchema object supports the following methods:

SetQueryText(< Текст>) — fills the Query Packet property based on the submitted request text;
GetQueryText() - returns the request text generated based on the Request Packet property;

FindParameters() - returns the request parameters.

Let's look at an example of working with the RequestSchema object. To programmatically generate the request text

SORT BY
Currencies.Code

The embedded language code might look like this:

RequestScheme = New RequestScheme;
Package 1 = RequestScheme. RequestBatch[ 0 ];
Operator1 = Package1. Operators[ 0 ];
// adding source
RegisterTable = Operator1. Sources. Add( "Directory.Currencies", "Currencies" );
// adding fields
FieldLink = Operator1. SelectableFields. Add("Currencies.Link" , 0 );
FieldCode = Operator1. SelectableFields. Add("Currencies.Code", 1);
// specifying field aliases
Package 1 . Columns[ 0 ]. Alias = "Currency" ;
Package 1 . Columns[ 1 ]. Alias = "Code" ;
// adding a condition
Operator1 . Selection. Add( "NOT FlagDeletion");
// add ordering
Package 1 . Order. Add(FieldCode);
RequestText = RequestScheme. GetQueryText();