Group, sort and totalise a report

You can group the data in a report to make it easier to read. You can sort the grouped data and calculate totals, the average, the highest value and the lowest value.

Description

If you add a new report, you only have the Detail section. If you position fields in this section and then display the report, the print preview will consist of one long list. You can apply a classification to this list by adding additional sections to the report. This also enables you to work with subtotals.

Extended example

Below you see an example of a report with outstanding items, without any grouping, sorted by voucher date.

By adding a section/group based on the debtor code, the outstanding items are grouped by debtor and you can have the totals per debtor calculated.

The following image shows a part of the report:

Because the code and name of the debtor no longer appear in the Detail section, this section now has more room for other fields.

Add a section more than once

You can group by the same field more than once. For example, you can add two groupings by the debtor code. You use the first group for a header to be shown on every page and you use the second group for a header to be included once only at the start of the group. Whether a header should be repeated on every page, is determined by your settings in the properties of the corresponding section.

Print totals reports

If you only want to print totals, deselect the Visible check box in the properties of the Detail section. You can also use a script to set the 'Visible' property to the value 'False'.

Note:

If you use this solution, all detail lines must be read, which could negatively impact the performance of the report. The preferred method is to compress the detail lines in the data collection.

Procedure

• Sort the detail section

  You can sort the lines in the detail section in ascending or descending order. You can also sort on multiple fields.

• Add a section

  You use sections to group the date in a report, so the report is more clearly organised. Per group you can add subtotals.

• Group a section on multiple fields

  You use sections to group the date in a report, so the report is more clearly organised.

• Add calculation fields to a section

  Using calculation fields, you can determine the following values for each section: totals, average, number of lines, highest value and lowest value.

• Add calculation fields to a report

  You can use the document footer for calculation fields that refer to the report in its entirety, for example a grand total.

• Specify the section properties

  For each section, you can specify properties, including the size and the background colour.

• Conditional formatting using a script

  Using a report script, you can format report fields based on a condition (comparison). This allows you to, for example, emphasize certain data in a report.

• Add totals to carry forward

  Per page, the total carried forward shows the current total for a group or the entire report.