SUBTOTAL (Col A)
= optional in Column A
Parameter | Mandatory/Optional | Occurrence in column A | Comment | |
---|---|---|---|---|
FORMS | REPORT | |||
SUBTOTAL | 1 | Parameter to govern both how the subtotal should be executed and the layout of the subtotal row. |
SUBTOTAL in column A is a parameter that should be used to insert subtotal rows automatically in a document. This parameter governs how the subtotal should be executed and the actual layout of the row (if not several SUBTOTAL levels, then SUBTOTAL_GROUPn defines the actual layout of each subtotal row). The program will copy this row and insert it for each subtotal that is inserted in the document.
The following must be defined in the SUBTOTAL row:
- In column B (ROWTYPE) - define which VIEWTAG the subtotal rows should have. After the document is created this cell will be set to DESIGN and thereby the parameter row SUBTOTAL will be hidden in the generated document. If no VIEWTAG should be used, then leave column B empty.
- Define in which column the description should be viewed
- Define which column that the subtotal should be grouped by. (State GROUP in that column, if several levels state GROUP1, GROUP2 in each column)
- Define which columns that should contain subtotal sums (State SUM, AVG, MAX, MIN or COUNT in these columns)
- Govern the actual formatting of the subtotal rows (if several SUBTOTAL levels, then SUBTOTAL_GROUPn defines the actual layout of each subtotal row)
Note: The below example only shows SUBTOTAL in one level. If you want to create subtotals in several levels then go to SUBTOTAL_GROUPn.
In column B (ROWTYPE) - SUM is defined (which is a defined VIEWTAG in PARAM).
In column G - GROUP is defined, since this is the column that the subtotal should be grouped by.
In column I, L, M, O, and P - SUM is defined, since these are the columns for which a subtotal sum should be added (in addition to SUM, MAX, MIN, AVG, and COUNT can be stated).
The whole row is formatted exactly as the inserted subtotal rows should look. Background colors, borders, font color, font size, font style, and so on.
In column E - a Total text is defined, which means that it is in this column the total text will be shown for each subtotal row.
All formulas are copied from the SUBTOTAL parameter row to all inserted total rows. Therefore, the total text need to be defined as a formula, i.e ="Total". Most often, however, it is also necessary to add additional text to the total text. For example, in this case, showing for which item group the subtotal concerns. In order to do that the formula can be extended with reference to a cell(s) in which the information can be fetched.
Note: In order for the formula to return any values, the references need to be to the ROW ABOVE the SUBTOTAL parameter row, as shown below.
The total formula refers to the text in the row above. Column G and H can be hidden in the generated document, since these are unnecessary when the subtotal row shows for which item group the sum row concerns.
Generated document: