Difference Between Sum Variables and Aggregate Functions

Valid from List & Label 23

You probably know the problem: You want to form a simple sum, but the values are calculated twice? The calculation is carried out in a specially created sum variable?
It is important to know the functional principle of sum variables. Sum variables always work across tables and not table-specific. In addition, the sum variables are also updated and calculated at different times. The principle can be explained in a simple and understandable way as follows:

Starting Position
A table with a nesting (or a report container with two tables) of Header Data > Detailed Data is used. You want to output a simple total in the lower table 'Detailed Data'. The sum variable or the total to be output is incremented once for both the Header Data table and the Detail Data table. Since a value to be totaled has already been defined at the start of printing, the total returns twice the value - the total from the header data and the total from the detail data.

a) Using table names, add up the values only for the desired table
In this case, at least the Detail Data table must be given a name in the table properties, such as "Details". The formula for the sum variable can then be adjusted accordingly. The internal List & Label variable LL.CurrentContainerItem is used for this purpose, which returns the current table name:

Cond(LL.CurrentContainerItem="Details", [Field for sum], 0)

The summation for this sum variable is now only carried out if the current table with the name 'Details' is also printed. The calculation for the header table is bypassed.

b) Use of an aggregate function
Simply use the Sum() aggregate function in the footer of the Detail Data table to output the total:

Sum([Field for sum],[optional: True/False])

In this case, only the values of the Detail Data table are totaled for the total, since aggregation functions work in contrast to sum variables, which are table-specific. You can use an optional second parameter of the aggregate function Sum() to decide whether the calculated values should be deleted after output or whether you want to continue using them. This is useful, for example, if you want totals to be output in addition to group-specific totals.

IDKBTE001349 KBTE001349

© combit GmbH