Working With Sum Variables

Sum variables offer another way of creating totals and counters and work fundamentally across tables.

They are therefore a good choice whenever you want to create totals across different table hierarchies.

In all other cases, we recommend the use of the aggregate functions Sum(), Count() and CountIf() for totals and counters. Aggregate functions are always table-specific. You can even produce statistical analyses directly with aggregate functions e.g. Median(), Variance(), StdDeviation(). You will find a list of all functions in the "Aggregate functions" function category in the formula wizard.

Sum variables can be used to create totals over data sets, e.g. to add up the "Item.UnitPrice" fields in a table in order to calculate the total price. Such totals are permitted for all numeric variables or for expressions that return a numeric value as the result.

But sum variables are also a convenient way of defining a counter which can be updated accordingly for each data record that is printed.

You can create a total across all data sets of a printed page (page totals) or across the entire project (grand totals).

Figure 7.20: Totaling with sum variables.

Proceed as follows to define the variables:

1.  Choose Project > Sum Variables or the "Edit sum variables" button in the formula wizard.

2.  In the dialog that follows, create a new sum variable via the "Insert a new variable" button.

3.  An input dialog appears where you can enter a description of the new variable. Give the variable a meaningful name, the "@" character will be added automatically as a prefix.

4.  Click the "Edit" button to open the formula wizard and assign a field or an expression to the new sum variable.

For example, select the numeric field "Item.UnitPrice" if you want to add up the "Item.UnitPrice" column.

You can, however, also perform aggregations with complex expressions provided that the result is a numeric value. For instance, you can add up the gross price from the net price and the VAT. Enter the following expression, for example, in the "Sums over" field:

5.  If you don't want to add up any values but merely want to create a counter or a numeration, the definition is simple: In the "Sums over" field, simply enter the value that is to be added to the existing counter.

The simplest case is a consecutive number that is increased by 1 for each data record. Just enter the value "1".

6.  The "Page sum" checkbox lets you specify whether the totals are to be set to 0 at the end of a page. In this way, you can define page totals and counters.

7.  Once you have defined which sums are to be stored in which sum variables, you can use these sum variables in your objects. In the formula wizard, you will find the sum variables at the end of the variable list in the "Sum variables" folder.