1C:Enterprise 8.3. Practical Developer’s Guide. Lesson 13 (4:30). Reports. Using calculated fields in reports

Using calculated fields in reports

Let us create the next report: Profit by customer. It visualizes the lifetime profit of Jack of All Trades by customer (fig. 13.99).

Lesson 13 (4:30). Reports / Using calculated fields in reports

Fig. 13.99. Resulting report

Using this report as an example, we will show how to use calculated fields and how to display a report as a pie chart and as a bar chart.

In Designer mode

Let us begin the report creation.

  1. Create a report configuration object named ProfitByCustomer.
  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 Accounting section, on the Reports menu, click Profit by customer.
  3. Click Create.

    You can see that the profit generated by rendering services to specific clients is displayed as a pie chart (fig. 13.106).

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In 1C:Enterprise mode
    Fig. 13.106. 3D pie chart
  4. In the report form, change Chart type to Column 3D and create the report. 

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In 1C:Enterprise mode
    Fig. 13.107. 3D column report

So we have shown how you can use various chart types for viewing report data.

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 the following fields from this table (fig. 13.100):
    • SalesTurnovers.Customer
    • SalesTurnovers.RevenueTurnover
    • SalesTurnovers.CostTurnover
    Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Query for a data set
    Fig. 13.100. Fields selected for the query
  3. Click the Unions/Aliases tab.
  4. Change RevenueTurnover to Revenue and change CostTurnover to Cost.
  5. Click OK.

    This completes the query creation. Since the query contains only expressions that you already know, we will not explain it in detail. Let us proceed to editing the data composition schema.

Calculated fields

This is the first time you need to display a field that is not available in the data set. Previously you always used fields defined in the data sets. To display the profit for rendered services by customer, you need an additional field calculated as revenue minus service cost.

For this purpose the data composition system provides the option to define a calculated field.

Calculated fields are additional data composition schema fields whose values are calculated according to the specified formula.

  1. Click the Calculated fields tab.
  2. Click the Add Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Calculated fields button.

    This adds a calculated field.
  3. In the Data path column, type Profit.
  4. In the Expression column, type the expression for calculating the field value (listing 13.14).

    Listing 13.14. Expression for calculating the Profit field value

    Revenue - Cost
    The platform fills the calculated field title (which is displayed in the report) automatically, you have the option to change it (fig. 13.101).

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Calculated fields
    Fig. 13.101. Creating a calculated field

You can add a calculated field to report resources in order to calculate group and overall totals by this field.

Resources

Let us add the resources.

  • Click the Resources tab and click Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Resources.

    You can see that the calculated field is included in the list of resources (fig. 13.102).

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Resources
    Fig. 13.102. Data composition schema resources

Settings

Let is add a chart to the report structure.

  1. On the Settings tab, in the command bar, click the Add Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Settings button and then click New chart (fig. 13.103).

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Settings
    Fig. 13.103. Adding a chart to report structure
  2. Click the Points branch and add a grouping by the Customer field to this branch.

    Keep the chart series unchanged. A pie chart is perfect for displaying profit by customers and this chart type only requires specifying points, so you do not have to specify series.

    The chart values always represent one or several report resources.
  3. Click the Selected fields tab and include the Profit field in the report.

    The report structure should look as shown in fig. 13.104.

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Settings
    Fig. 13.104. Report structure and chart settings
  4. Click the Other settings tab, set Chart type to Pie 3D and include Chart type in the list of quick user settings (fig. 13.105).

    Lesson 13 (4:30). Reports / Using calculated fields in reports / In Designer mode / Settings
    Fig. 13.105. 3D pie chart
  5. Enter the Profit by customer report title.

    Finally, let us specify the subsystems where the report is available.
  6. Close the data composition schema wizard and then, in the editor of the ServiceList report configuration object, click the Subsystems tab.
  7. Select the Accounting and Services check boxes.

Leave a Reply

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

1C:Enterprise Developer's Community