Subtotal

version 3

Subtotal (data{; pageBreak}) Number

Subtotal returns the subtotal for data for the current or last break level. Subtotal works only when a sorted selection is being printed with PRINT SELECTION or when printing using Print in the User environment. The data parameter must be of type real, integer, or long integer. Assign the result of the Subtotal function to a variable placed in the Break area of the form.

Warning: In compiled mode, you must execute BREAK LEVEL and ACCUMULATE before every form report for which you want to do break processing and calculate subtotals. See discussion at the end of the description of this command.

Subtotal should be in the form method or an object method for the form. 4th Dimension scans the form method and object methods before printing; if Subtotal is present, break processing will be initiated (in interpreted mode only).

The second, optional, argument to Subtotal is used to cause page breaks during printing. If pageBreak is 0, Subtotal does not issue a page break. If pageBreak equals 1, Subtotal issues a page break for each level 1 break. If pageBreak equals 2, Subtotal issues a page break for each level 1 and level 2 break, and so on.

To have breaks on N sort levels, you must sort the current selection on N + 1 levels (unless you use BREAK LEVEL or ACCUMULATE, in which case N levels is sufficient). This lets you sort on a last field, so that the field does not create unwanted breaks. To have the last sort field generate a break, sort the field twice.

Tip: If you execute Subtotal from within an output form displayed at the screen, an error will be generated, triggering an infinite loop of updates between the form and the error window. To get out of this loop, press Alt+Shift (Windows) or Option-Shift (Macintosh) when you click on the Abort button in the Error window (you may have to do so several times). This temporarily stops the updates for the form's window. Select another form as the output form so the error will occur again. Go back to the Design Environment and isolate the call to Subtotal into a test Form event=On Printing Break if you use the form both for display and printing.

The following example is a one-line object method in a Break area of a form (B0, the area above the B0 marker). The vSalary variable is placed in the Break area. The variable is assigned the subtotal of the Salary field for this break level:

   Case of
      : (Form event=On Printing Break)
         vSalary:=Subtotal ([Employees]Salary)
   End case

For more information about designing forms with header and break areas, see the 4th Dimension Design Reference manual.

Break processing in form reports can be activated in two ways:

• The first uses the Subtotal function.

• The second uses the BREAK LEVEL and ACCUMULATE commands.

Both methods can achieve the same results, but have different advantages.

To turn on break processing with the Subtotal function, the function must appear in the form method or an object method for a variable located in a Break area of the form. Before printing the report, 4th Dimension scans the form and object methods for the Subtotal function.

If 4th Dimension finds the function, break processing is activated. The Subtotal function does not need to be executed for it to turn on break processing. For example, it could be in a method of an object that is below the Footer line and therefore would never be printed or executed.

When Subtotal is used to activate break processing, you must sort on one more level than you break on. For example, to have two levels of breaks in your report, sort on three levels.

Using BREAK LEVEL and ACCUMULATE for Break Processing

You can also use the BREAK LEVEL and ACCUMULATE commands to turn on break processing. To do so, you must execute both of these commands before printing a form report. In this scheme, the Subtotal function is still required in order to display values on a form. You do not need to sort on one extra level; you must, of course, sort on at least as many levels as you need to break on.

The primary advantage of using Subtotal to initiate break processing is that you do not need to execute a method prior to printing the report. This is especially useful in the User environment.

The process to print the report in the User environment is typically like this:

1. Select the records to be printed.

2. Order by (sort) the records, sorting on one extra level.

3. Choose Print from the File menu.

4th Dimension scans the form and object methods, finds the Subtotal function, turns on break processing, and prints the report. There are two disadvantages to using Subtotal to trigger break processing:

• You cannot use Subtotal to activate break processing in compiled databases.

• You must sort on one extra level; if you have many records, this may be time consuming.

Using BREAK LEVEL and ACCUMULATE to activate break processing is the recommended method when using methods to generate form reports. The process to print a report using this method is typically like this:

1. Select the records to be printed.

2. Sort the records using ORDER BY. Sort on at least the same number of levels as breaks.

3. Execute BREAK LEVEL and ACCUMULATE.

4. Print the report using PRINT SELECTION.

You must use BREAK LEVEL and ACCUMULATE to activate break processing in compiled mode. However, the Subtotal function is still necessary in order to display values on a form.

ACCUMULATE, BREAK LEVEL, Level, PRINT SELECTION.