1C:Enterprise 8.3. Practical Developer’s Guide. Lesson 13 (4:30). Reports. Getting current values from a periodic information register

Getting current values from a periodic information register

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

Lesson 13 (4:30). Reports / Getting current values from a periodic information register
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.

In Designer mode

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.

In 1C:Enterprise mode

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.

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In 1C:Enterprise mode
    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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In 1C:Enterprise mode
    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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In 1C:Enterprise mode
    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.

Query for a data set

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.

Analyzing query text

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.

Resources

Let us add a resource.

  1. Click the Resources tab and click Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Resources.

    The wizard selects the only available resource: Price.
  2. In the Calculate by column, click the Select Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Resources button and select the Service field (fig. 13.91).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Resources
    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.

Parameters

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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Parameters
    Fig. 13.92. Composition schema parameters

Settings

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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Settings
    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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Settings
    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.

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Settings
    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.
Virtual table parameters

Let us specify virtual table parameters.

  1. In the Tables list, click PricesSliceLast and then click the Virtual table parameters Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Query for a data set / Virtual table parameters button.
  2. Specify that the period is passed in the ReportDate parameter (fig. 13.86).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Query for a data set / Virtual table parameters
    Fig. 13.86. Virtual table parameters
  3. Select the following fields from the tables (fig. 13.87):
    • MaterialsAndTablesCat.Parent
    • MaterialsAndTablesCat.Ref
    • PricesSliceLast.Price
    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Query for a data set / Virtual table parameters
    13.87. Selected fields
Left join of tables

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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Query for a data set / Left join of tables
    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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Query for a data set / Left join of tables
    Fig. 13.89. Item selection condition
Field aliases

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).

    Lesson 13 (4:30). Reports / Getting current values from a periodic information register / In Designer mode / Query for a data set / Field aliases
    Fig. 13.90. Specifying query field aliases
  3. Click OK.

    You have completed the query creation. 

Leave a Reply

Your email address will not be published. Required fields are marked *

 

1C:Enterprise Developer's Community