Report Designer - Using Reset Section and Evaluate Section to ensure totals calculate correctly

In Report Designer, to ensure that report totals calculate correctly, the Reset Section and Evaluate Section settings must be set correctly. This article explains how to use these settings and gives examples of how they affect your report totals. 

How to use Reset Section

This option determines at what point on the report a total starts calculating again from zero.

To set this option > select the relevant total > Properties > Reset Section > choose from the following:

  • (None) - The total is cumulative for the whole report.
  • Group header or footer - Choose from a list of the group headers and footers on the report. The total then calculates separately for each instance of the group you choose.

Generally, the Reset Section option should be set to the section that contains the total.

Example 1

On a sales transaction report, grouped by SALES_LEDGER.ACCOUNT_REF, you add a total in the ACCOUNT_REF Footer section:

Report Structure Reset section = None Reset section = ACCOUNT_REF Footer
Report title

Customer 1

Total 1

Customer 2

Total 2

Customer 3

Total 3

End of report

 

Total 1 is the total for customer 1.

Total 2 is the cumulative total for customers 1 and 2.

Total 3 is the cumulative total for customers 1, 2 and 3.

The total is a running total to the end of the report.

 

Total 1 is the total for customer 1.

Total 2 is the total for customer 2.

Total 3 is the total for customer 3.

The total is reset after each account footer.

Example 2

On an invoice sales report, grouped by INVOICE.ACCOUNT_REF and then by INVOICE_ITEM.STOCK_CODE, you add a total in the STOCK_CODE Footer section:

Report Structure Reset Section = None Reset Section = ACCOUNT_REF Footer Reset Section = STOCK_CODE Footer
Report title
Customer 1

     Product 1

Total 1

     Product 2

Total 2

Customer 2

     Product 1

Total 3

     Product 2

Total 4

End of report

 

Total 1 is the total for customer 1, product 1.

Total 2 is the cumulative total for customer 1, products 1 and 2.

Total 3 is the cumulative total for customer 1, products 1 and 2, plus customer 2, product 1.

Total 4 is the cumulative total for customer 1, products 1 and 2, plus customer 2, products 1 and 2.

The total is a running total to the end of the report.

 

Total 1 is the total for customer 1, product 1.

Total 2 is the cumulative total for customer 1, products 1 and 2.

Total 3 is the total for customer 2, product 1

Total 4 is the cumulative total for customer 2, products 1 and 2.

The total is reset after each account reference footer.

 

Total 1 is the total for customer 1, product 1.

Total 2 is the total for customer 1, product 2.

Total 3 is the total for customer 2, product 1.

Total 4 is the total for customer 2, product 2.

The total is reset after each stock code footer.

How to use Evaluate Section

When a combination of variables from different tables are used in the same section on a report, it can cause duplicate values. These duplicate values are then included the report totals, making them incorrect. The Evaluate Section option prevents this by controlling how many times a value is included in the total.

To set this option > select the relevant total > Properties > Evaluate Section > choose from the following:

  • (None) - The total includes all instances of the value on the report.
  • Header or footer - Choose from a list of the headers and footers on the report. The total then includes only one instance of the value for each instance of that group.

Example

You post 2 transactions made up of 2 splits each as follows:

Transaction number Date Ref Extra Ref Net
1 15/07/13 1 1A 50
1 15/07/13 1 1B 50
2 15/07/13 1 2A 50
2 15/07/13 1 2B 50

On a summary invoice day book report which uses AUDIT_HEADER variables, this shows 2 transactions for £100 each. If you then add the AUDIT_SPLIT_EXTRA_REF variable, when you run the report, each transaction appears twice, once for each extra reference.

Report Structure Evaluate Section = None Evaluate Section = AUDIT_SPLIT.HEADER_NUMBER Footer
Report title

Header
No.

Ex Ref Header
Net.

1

1A 100

1

1B 100

2

2A 200

2

2B 200

 

  Total
End of report





Total shows £600 which is incorrect.

It includes the full net amount for header transactions 1 and 2 twice because there are 2 extra references for each transaction.

Note: This setting uses groups. To include the transaction values in the total once per header number, add a group for AUDIT_SPLIT.HEADER_NUMBER.

Total shows £300 which is correct.

The header net values are only included in the total once per header number.

Further information

17546 - Report Designer - Reasons why totals may appear incorrect on a report or layout
12654 - Report Designer - How to add groups to reports and layouts
12658 - Report Designer - Numeric Formatting

How to use Reset Section

This option determines at what point on the report a total starts calculating again from zero.

To set this option > select the relevant total > Properties > Reset Section > choose from the following:

  • (None) - The total is cumulative for the whole report.
  • Group header or footer - Choose from a list of the group headers and footers on the report. The total then calculates separately for each instance of the group you choose.

Generally, the Reset Section option should be set to the section that contains the total.

Example 1

On a sales transaction report, grouped by SALES_LEDGER.ACCOUNT_REF, you add a total in the ACCOUNT_REF Footer section:

Report Structure Reset section = None Reset section = ACCOUNT_REF Footer
Report title

Customer 1

Total 1

Customer 2

Total 2

Customer 3

Total 3

End of report

 

Total 1 is the total for customer 1.

Total 2 is the cumulative total for customers 1 and 2.

Total 3 is the cumulative total for customers 1, 2 and 3.

The total is a running total to the end of the report.

 

Total 1 is the total for customer 1.

Total 2 is the total for customer 2.

Total 3 is the total for customer 3.

The total is reset after each account footer.

Example 2

On an invoice sales report, grouped by INVOICE.ACCOUNT_REF and then by INVOICE_ITEM.STOCK_CODE, you add a total in the STOCK_CODE Footer section:

Report Structure Reset Section = None Reset Section = ACCOUNT_REF Footer Reset Section = STOCK_CODE Footer
Report title
Customer 1

     Product 1

Total 1

     Product 2

Total 2

Customer 2

     Product 1

Total 3

     Product 2

Total 4

End of report

 

Total 1 is the total for customer 1, product 1.

Total 2 is the cumulative total for customer 1, products 1 and 2.

Total 3 is the cumulative total for customer 1, products 1 and 2, plus customer 2, product 1.

Total 4 is the cumulative total for customer 1, products 1 and 2, plus customer 2, products 1 and 2.

The total is a running total to the end of the report.

 

Total 1 is the total for customer 1, product 1.

Total 2 is the cumulative total for customer 1, products 1 and 2.

Total 3 is the total for customer 2, product 1

Total 4 is the cumulative total for customer 2, products 1 and 2.

The total is reset after each account reference footer.

 

Total 1 is the total for customer 1, product 1.

Total 2 is the total for customer 1, product 2.

Total 3 is the total for customer 2, product 1.

Total 4 is the total for customer 2, product 2.

The total is reset after each stock code footer.


How to use Evaluate Section

When a combination of variables from different tables are used in the same section on a report, it can cause duplicate values. These duplicate values are then included the report totals, making them incorrect. The Evaluate Section option prevents this by controlling how many times a value is included in the total.

To set this option > select the relevant total > Properties > Evaluate Section > choose from the following:

  • (None) - The total includes all instances of the value on the report.
  • Header or footer - Choose from a list of the headers and footers on the report. The total then includes only one instance of the value for each instance of that group.

Example

You post 2 transactions made up of 2 splits each as follows:

Transaction number Date Ref Extra Ref Net
1 15/07/13 1 1A 50
1 15/07/13 1 1B 50
2 15/07/13 1 2A 50
2 15/07/13 1 2B 50

On a summary invoice day book report which uses AUDIT_HEADER variables, this shows 2 transactions for £100 each. If you then add the AUDIT_SPLIT_EXTRA_REF variable, when you run the report, each transaction appears twice, once for each extra reference.

Report Structure Evaluate Section = None Evaluate Section = AUDIT_SPLIT.HEADER_NUMBER Footer
Report title

Header
No.

Ex Ref Header
Net.

1

1A 100

1

1B 100

2

2A 200

2

2B 200

 

  Total
End of report





Total shows £600 which is incorrect.

It includes the full net amount for header transactions 1 and 2 twice because there are 2 extra references for each transaction.

Note: This setting uses groups. To include the transaction values in the total once per header number, add a group for AUDIT_SPLIT.HEADER_NUMBER.

Total shows £300 which is correct.

The header net values are only included in the total once per header number.


Further information

17546 - Report Designer - Reasons why totals may appear incorrect on a report or layout
12654 - Report Designer - How to add groups to reports and layouts
12658 - Report Designer - Numeric Formatting