The Service list report displays the list of services provided by Jack of All Trades with their prices (fig. 13.85).

Fig. 13.85. Resulting report

Using this report as an example, we will show how to obtain the latest values from a periodic information register and how to display hierarchical catalogs.

Let us begin the report creation.

1. Create a report configuration object named ServiceList.
2. Run the Data composition schema wizard.
3. Add Data set – query and run the Query builder.

Let us check the report.

1. Run 1C:Enterprise in the debug mode.
2. Open the Prices periodic register.
3. Add a record for the Diagnostics service: price 350 as of 7/10/2014 (fig. 13.96).
   
   This is required for testing the report.
   
   
   Fig. 13.96. Adding a Prices register record for the Diagnostics service
4. Run the Service list report as of 7/7/2014 (fig. 13.97).
   
   
   Fig. 13.97. Resulting report
   
   The report displays the Diagnostics service price as of 7/7/2014, which is 200 dollars.
5. Run the report as of 7/10/2014 (fig. 13.98).
   
   
   Fig. 13.98. Resulting report
   
   The report displays the new Diagnostics service price: 350 dollars.

This example shows how the data composition system gets the latest values from a periodic information register and how to display groupings based on the catalog hierarchy.

Let us create a query for a data set.

1. As a data source for the query, select the object (referential) table of the MaterialsAndServices catalog and the virtual table of the Prices.SliceLast information register.
   
   To avoid ambiguous names in the query, let us change the name of the MaterialsAndServices table.
2. In the Tables list, right-click MaterialsAndServices, click Rename Table, and type MaterialsAndServicesCat.

Let us analyze the generated query text (listing 13.13).

Listing 13.13. Query text

SELECT
    MaterialsAndServices.Parent AS ServiceGroup,
    MaterialsAndServicesCat.Ref AS Service,
    PricesSliceLast.Price
FROM
    Catalog.MaterialsAndServices AS MaterialsAndServicesCat
        LEFT JOIN InformationRegister.Prices.SliceLast(&ReportDate, ) AS PricesSliceLast
        ON (PricesSliceLast.MaterialOrService = MaterialsAndServicesCat.Ref)
WHERE
    MaterialsAndServicesCat.MaterialServiceType = &MaterialServiceType

Almost all of the clauses used in this query are already familiar to you. Let us proceed to editing the data composition schema.

Let us add a resource.

1. Click the Resources tab and click .
   
   The wizard selects the only available resource: Price.
2. In the Calculate by column, click the Select  button and select the Service field (fig. 13.91).
   
   
   Fig. 13.91. Data composition resources
   
   This is required to have the price totals displayed for specific services, since calculating prices for groupings and overall totals does not make sense.

Let us specify the data composition parameters. 

1. On the Parameters tab, set the MaterialServiceType parameter value to Enum.MaterialServiceTypes.Service.
2. For the ReportDate parameter, clear the Availability restriction check box.
3. For the ReportDate parameter, in the Type field, select the Date date format.
4. For the Period parameter, select the Availability restriction check box (fig. 13.92).
   
   
   Fig. 13.92. Composition schema parameters

Let us specify the report structure.

1. On the Settings tab, create a grouping by the ServiceGroup field with the Hierarchy type (fig. 13.93).
   
   
   Fig. 13.93. Selecting grouping field and type
   
   Note. Previously you always used the default hierarchy type: Elements. The following hierarchy types are available for report groupings:
   • Elements. The grouping only includes nonhierarchical records.
   • Hierarchy. The grouping includes both hierarchical and nonhierarchical records.
   • Hierarchy only. The grouping only includes hierarchical records.
2. Create another grouping inside this one, without specifying the grouping field.
   
   It will contain detailed report records.
3. Click the Selected fields tab and specify that the Service and Price fields are included in the report (fig. 13.94).
   
   
   Fig. 13.94. Report structure and fields
   
   Let us customize the report appearance.
4. Click the Other settings tab and set Vertical arrangement of overalls to None.
   
   This disables the display of overall totals in the report.
5. Set Grouping field placement to Separately and only in totals (fig. 13.95).
   
   This improves the report usability.
6. Set the report title to Service list.
   
   
   Fig. 13.95. Parameters of report display settings
7. Include the Report date parameter in the user settings and set its Edit mode to Quick access.
   
   Finally, let us specify the subsystems where the report is available.
8. Close the data composition schema wizard and then, in the editor of the ServiceList report configuration object, click the Subsystems tab.
9. Select the Accounting and Services check boxes.

Let us specify virtual table parameters.

1. In the Tables list, click PricesSliceLast and then click the Virtual table parameters  button.
2. Specify that the period is passed in the ReportDate parameter (fig. 13.86).
   
   
   Fig. 13.86. Virtual table parameters
3. Select the following fields from the tables (fig. 13.87):
   • MaterialsAndTablesCat.Parent
   • MaterialsAndTablesCat.Ref
   • PricesSliceLast.Price
   
   13.87. Selected fields

Let us specify the table join.

1. Click the Links tab.
   
   The platform automatically generates a link condition stating that the value of the MaterialsAndServices dimension of the information register is equal to the reference to the MaterialsAndServices catalog item. It is okay with us.
2. Clear the All check box for the register table and select the All check box for the catalog table.
   
   This is how you specify Left join as the link type for the catalog table (fig. 13.88).
   
   
   Fig. 13.88. Link between the tables in a query
3. On the Conditions tab, specify the criteria for selecting items from the MaterialsAndServices catalog: select only items that have the type passed in the MaterialServiceType parameter (fig. 13.89).
   
   
   Fig. 13.89. Item selection condition

Let us set aliases for the catalog item presentation (Ref field) and for the register field.

1. Click the Unions/Aliases tab.
2. In the Field name column, change Ref to Service and change Parent to ServiceGroup (fig. 13.90).
   
   
   Fig. 13.90. Specifying query field aliases
3. Click OK.
   
   You have completed the query creation.