Calculation and Row Total

In Corporate Design, simple calculations can be used, for example, to create totals or to multiply two fields.

Use calculation - multiplication of columns

To illustrate the setup for this, the gross weight for each item line in a sales shipment is calculated and printed as an example. The item line only contains the gross weight per unit, but not the total weight. For this reason, the quantity is multiplied by the gross weight per unit.

For this, a new Text Constant _GROSSWEIGHT with the Type Calculation must first be created in CD Labels. This will be used for the result of the calculation.

CD Calculation

After that several lines have to be created in the CD Field Configuration.

Line 1 assigns the first field, Gross Weight per unit, to the calculation equation. The assignment is made using the Label and Calculation Operator fields.

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Type Field
  • Field No. 34
  • Caption Gross Weight
  • Section Calculation
  • Position 9
  • Sorting empty
  • Label _GROSSWEIGHT
  • Calculation Operator Assignment

CD Calculation-line 1a CD Calculation line 1b

Line 2 multiplies the assigned Gross Weight per unit by the Quantity field. This is again done using the Label and Calculation Operator fields. It is important that the Position of line 1 and 2 match. The Sorting field decides the order of the arithmetic operation.

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Type Field
  • Field No. 15
  • Caption Quantity
  • Section Calculation
  • Position 9
  • Sorting 1
  • Label _GROSSWEIGHT
  • Calculation Operator one * for multiplication

CD Calculation-line 2a CD Calculation line 2b

Line 3 prints the result of the calculation on the document as a column, and determines the column label.

  • CD Report ID 5517508
  • Source type Table
  • Source No. 111
  • Source Sub Type -1
  • Type Calculation
  • Field No. _GROSSWEIGHT
  • Source Table Relation Table Type Calculation
  • Section Line
  • Position 9
  • Output Type 9
  • Use as Caption Yes
  • Label Gross Weight
  • Label Font Style Bold
  • Value Format 10

CD Calculation line 3a CD Calculation Line 3b

The gross weight of the items is now printed on the sales shipment.

CD Calculation Example

Calculate Row Total - based on a calculated column

To print the row total for the gross weight, as shown in the previous screenshot, the following assets are necessary. The summation of the net weight is done in the same way as for the gross weight.

Line 1 sums the individual values from the item lines of the variable _GROSSWEIGHT and prints them as Row Total at the end of the line section.

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Source Sub Type -1
  • Type Calculation
  • Field No. _GROSSWEIGHT
  • Source Table Relation Table Type Calculation
  • Source Table Relation Table empty
  • Section RowTotals
  • Position 9
  • Sorting empty
  • Output Type 9
  • Label empty
  • Value Format 10

CD Row total 1a CD Row total 1b

Line 2 adds the identifier Sum to the first column of the sum line. Using the Value Append Type field and the Label as Value option, the label is inserted in the line, not in the line header.

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Source Sub Type -1
  • Type empty
  • Field No. empty
  • Source Table Relation Table Type empty
  • Section RowTotals
  • Position 1
  • Sorting empty
  • Output Type 9
  • Label Total
  • Label Font Style Bold
  • Value Format empty
  • Value Append Type Label as Value

CD Row total 2a CD Row total 2b

Calculate Row Total - based on a not calculated column

To calculate and print the Row Totals for a normal, non-calculated column, three setup lines are necessary in CD Field Configuration.

To explain the necessary setup, the Quantity of all lines in a Transfer Order is summed up and printed as an example.

For this a new Text Constant _QUANTITY_SUM with the Type Calculation must first be created in CD Labels. This will be used for the result of the Calculation. Furthermore a Label Quantity_Total of the Type Field Caption must be created, with a value in a Language, which can be used as a Label for the Row Total.

CD Row Total

Line 1 in the Field Configurator assigns the values in the Quantity field to the _QUANTITY_SUM variable.

  • CD Report ID 5517509
  • Source Type Table
  • Source No. 5741
  • Source Sub Type empty
  • Type Field
  • Field No. 4
  • Caption Quantity
  • Source Table Relation Table Type empty.
  • Section *Calculation
  • Position 4
  • Sorting empty
  • Output Type empty.
  • Use as Caption No
  • Label _QUANTITY_SUM
  • Calculation Operator Assignment

CD RowTotals CD RowTotals

Line 2 sums the individual values from the item lines of the variable _QUANTITY_SUM and prints them as RowTotals at the end of the Line Section.

  • CD Report ID 5517509
  • Source Type Table
  • Source No. 5741
  • Source Sub Type -1
  • Type Calculation
  • Field No. _QUANTITY_SUM
  • Source Table Relation Table Type Calculation
  • Source Table Relation Table empty
  • Section RowTotals
  • Position 4
  • Sorting empty
  • Output Type 9
  • Label empty

CD RowTotals CD RowTotals

Line 3 adds the created label Quantity_Total to the first column of the summary row. Using the Value Append Type field and the Label as Value option, the label is inserted in the Line, not in the Line header.

  • CD Report ID 5517509
  • Source Type Table
  • Source No. 5741
  • Source Sub Type empty
  • Type empty
  • Field No. empty
  • Source Table Relation Table Type empty
  • Section RowTotals
  • Position 1
  • Sorting empty
  • Output Type 9
  • Label Quantity_Total
  • Label Font Style Bold
  • Value Format empty
  • Value Append Type Label as Value

CD RowTotals CD RowTotals

The result is the Total Quantity as Row Total in the Transfer Order.

CD RowTotals

Note

From Version 10.4 onward, calculated values can be printed in the Section Line Extension. For this to work, the field Position must have the same value as the field Position used for the calculation.

Filter Row Totals by Line Type

By default, the row total calculation includes all line types. This is expressed by setting Source Sub Type to -1 in the RowTotals section lines, as shown in the examples above.

To restrict the row total to specific line types only — for example, to sum quantities for item lines only while excluding comment or charge lines — set Source Sub Type to the numeric value corresponding to the desired line type. This applies to all lines in the RowTotals section that contribute to the sum.

For the list of available Source Sub Type values and their meanings, see CD Field Configuration.

Note

If the row total should only include item lines, set Source Sub Type to 2 on the corresponding RowTotals lines. Lines of other types, such as comments or item charges, are then excluded from the total.

Calculation Using a Fixed Parameter – Convert Gross Weight from KG to LB

This example shows how to multiply the gross weight from KG to LB. The gross weight is stored exemplary in KG in the system and should be converted to LB on the Sales Shipment report by a fixed conversion factor, defined as a parameter. The parameter is set up in the CD Labels table and linked to its value via the CD Rec. Extensions table.

Step 1 – Create the Parameter Label

In CD Labels, create a new entry for the conversion factor:

  • Type Parameter
  • Text Constant CONV_KG_LB
  • Value Conversion KG to LB
Note

Currently, the Text Constant for a parameter in the CD Label table must be entered in uppercase letters, must not contain spaces, and must not be longer than 20 characters. If this is not followed, the parameter may not be selected in the CD Rec. Extensions table.

Step 2 – Set Up the CD Rec. Extension

In CD Rec. Extensions, create the following entry to define the value of the conversion factor:

  • Source Type Parameter
  • Source No. CONV_KG_LB
  • Value 2.20462

Step 3 – Create the Calculation Label

In CD Labels, create a second entry for the output variable that will store the result:

  • Type Calculation
  • Text Constant _GROSSWEIGHT_LB
  • Value Gross Weight LB

Step 4 – Set Up the CD Field Configuration

In CD Field Configuration, create the following three lines.

Line 1 assigns the Gross Weight per unit to the calculation variable using the Assignment operator:

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Type Field
  • Field No. 34
  • Caption Gross Weight
  • Section Calculation
  • Position 9
  • Sorting empty
  • Label _GROSSWEIGHT_LB
  • Calculation Operator Assignment

Line 2 multiplies the assigned value by the fixed parameter CONV_KG_LB. The Field No. is set to 5002 by selecting Parameter from the lookup. The Source Table Relation Table is then set to the parameter name CONV_KG_LB. The Position must match the Position from line 1. The Sorting is set to 1 to ensure that this line is processed after line 1.

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Type Extra Field
  • Field No. 5002
  • Source Table Relation Table Type Parameter
  • Source Table Relation Table CONV_KG_LB
  • Section Calculation
  • Position 9
  • Sorting 1
  • Label _GROSSWEIGHT_LB
  • Calculation Operator *** (multiplication)

Line 3 prints the result of the calculation on the sales shipment as a column:

  • CD Report ID 5517508
  • Source Type Table
  • Source No. 111
  • Source Sub Type -1
  • Type Calculation
  • Field No. _GROSSWEIGHT_LB
  • Source Table Relation Table Type Calculation
  • Section Line
  • Position 9
  • Output Type 9
  • Use as Caption Yes
  • Label (field label for the column header, e.g., Gross Weight LB)
  • Value Format 10

The gross weight is now converted from KG to LB and printed as an additional column on the sales shipment.

Note

For other types of calculations it can be useful to set the Source Sub Type to 2. This restricts processing to item lines only. For non-item lines the gross weight field is empty, which can cause division errors in subsequent calculations.