1C:Enterprise 8.3. Practical Developer’s Guide. Lesson 13 (4:30). Reports. Displaying data as a spreadsheet

Displaying data as a spreadsheet

Let us create a general-purpose report. Using this report as an example, we will show how to output data to a table (fig. 13.108).

Lesson 13 (4:30). Reports / Displaying data as a spreadsheet 
Fig. 13.108. Resulting report

We will show how to make a report as flexible as possible, so that users can edit the report structure and appearance in 1C:Enterprise mode without using the full report settings (the Select variant command on the More menu). For example, users can change the order of spreadsheet columns and rows or change the data displayed in spreadsheet cells.

In Designer mode

Let us begin the report creation.

  1. Create a report configuration object named GenericReport.
  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. In the Services section, on the Report menu, click Generic report.
  3. Click Create.

    You can see that the resulting report is empty because row groupings, column groupings, and the list of selected fields are empty. Let us specify these quick user settings.
  4. In the Selection field, click the Select Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In 1C:Enterprise mode button and select Revenue Turnover.
  5. In the Rows field, click the Select Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In 1C:Enterprise mode button and add a grouping by the Material or service field with the Hierarchy type.
  6. In the Columns field, click the Select Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In 1C:Enterprise mode button and add a grouping by the Technician field.
  7. Click Create.

    The report should look as shown in fig. 13.112.

    Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In 1C:Enterprise mode
    Fig. 13.112. Resulting report

    Note that scrolling the report horizontally or vertically does not move the report header and its leftmost column. The platform automatically freezes the top and left report areas for usability purposes. Alternatively, you can specify the rows and columns to freeze using the FixLeft and FixTop output parameters. 
  8. Add Cost Turnover to the list of selected fields.
  9. Change the row grouping from Material or service to Customer and create the report.

    The report should look as shown in fig. 13.113.

    Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In 1C:Enterprise mode
    Fig. 13.113. Resulting report 
  10. Exclude Cost Turnover from the list of selected fields.
  11. Change the row grouping to Material or service with the Hierarchy only type.
  12. Add a column grouping by the Customer field, move it to the beginning of the grouping list, and click Create.

    The report should look as shown in fig. 13.114.

    Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In 1C:Enterprise mode
    Fig. 13.114. Resulting report

So you have created a report that offers users the option to create a variety of custom reports based on the Sales register.

Query for a data set

Let us specify the query data set.

  1. Select the Sales.Turnovers virtual table as the data source.
  2. Select all fields from this table (fig. 13.109).

    Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In Designer mode / Query for a data set
    Fig. 13.109. Selected fields
  3. Click OK.

Analyzing query text

Let us check the generated query text (listing 13.15).

Listing 13.15. Query text

SELECT
    SalesTurnovers.MaterialOrService,
    SalesTurnovers.Customer,
    SalesTurnovers.Technician,
    SalesTurnovers.QuantityTurnover,
    SalesTurnovers.QuantityRevenue,
    SalesTurnovers.CostTurnover
FROM
    AccumulationRegister.Sales.Turnovers AS SalesTurnovers

Resources

Let us add the resources.

  • Click the Resources tab and click Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In Designer mode / Resources.

    This adds all available resources.

Settings

Let us add a table to the report structure.

  1. On the Settings tab, in the command bar, click the Add Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In Designer mode / Settings button and then click New table (fig. 13.110).

    Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In Designer mode / Settings
    Fig. 13.110. Adding a table to report structure

    Let us skip specifying spreadsheet rows and columns and the list of selected fields because our goal is to provide users with full freedom in their actions.
  2. In the report item structure, click the Table item and then click the Custom settings item properties Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In Designer mode / Settings button on the command bar.

    This opens the window where you can edit the assortment of user settings for the spreadsheet.
  3. Select the Selected Fields, String groupings, and Column groupings check boxes, and in the Edit mode column, leave their default Quick access values (fig. 13.111).

    Lesson 13 (4:30). Reports / Displaying data as a spreadsheet / In Designer mode / Settings
    Fig. 13.111. Editing user settings

    So you gave users the option to define the assortment of selected fields, as well as spreadsheet row and column groupings, directly in the report form prior to report generation.

    Finally, let us specify the subsystems where the report is available.
  4. Close the data composition schema wizard and then, in the editor of the GenericReport configuration object, click the Subsystems tab.
  5. Select the Services check box.

Leave a Reply

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

1C:Enterprise Developer's Community