A report can be sorted on up to 10 fields. Each of those 10 fields can be a “break” field. Break fields specify the levels at which subtotals will be generated for all fields that are totaled in the report.
Subtotal access fields give you access to a field’s total at any of the break levels. For example, suppose you have a report that breaks on the state and then on the city and calculates a total for the number of traffic accidents occurring within each city on a given day. The report might look something like this:
CA EUREKA 10/01/91 10
CA EUREKA 10/02/91 3
CA EUREKA 10/03/91 2
--
EUREKA total (count 3): 15
CA SACRAMENTO 10/01/91 18
CA SACRAMENTO 10/02/91 8
--
SACRAMENTO total (count 2): 26
CA total (count 5): 41
FL SARASOTA 10/01/91 3
FL SARASOTA 10/02/91 9
FL SARASOTA 10/03/91 1
FL SARASOTA 10/04/91 2
--
SARASOTA total (count 4): 15
FL total (count 4): 15
NV RENO 10/01/91 2
NV RENO 10/02/91 5
--
RENO total (count 2): 7
NV LAKE TAHOE 10/01/91 3
NV LAKE TAHOE 10/02/91 6
NV LAKE TAHOE 10/03/91 1
--
LAKE TAHOE total (count 3): 10
NV total (count 5): 17
Report total (count 14): 73
With subtotal access fields, you can access a variety of information. The above report has two break levels: state and city. The subtotal for the number of accidents is reset at the lowest break level, city. You can define a subtotal access field that contains the current accident count at any break level or at the report level. For example, you could create a subtotal access field that contains the current running total of accidents for all cities, in all states, for the entire report. If you then printed this total on the report, the report would look something like this:
State City Date Acc ct Accum ct
CA EUREKA 10/01/91 10 10
CA EUREKA 10/02/91 3 13
CA EUREKA 10/03/91 2 15
--
EUREKA total (count 3): 15
CA SACRAMENTO 10/01/91 18 33
CA SACRAMENTO 10/02/91 8 41
--
SACRAMENTO total (count 2): 26
CA total (count 5): 41
FL SARASOTA 10/01/91 3 44
FL SARASOTA 10/02/91 9 53
FL SARASOTA 10/03/91 1 54
FL SARASOTA 10/04/91 2 56
--
SARASOTA total (count 4): 15
FL total (count 4): 15
NV RENO 10/01/91 2 58
NV RENO 10/02/91 5 63
--
RENO total (count 2): 7
NV LAKE TAHOE 10/01/91 3 66
NV LAKE TAHOE 10/02/91 6 72
NV LAKE TAHOE 10/03/91 1 73
--
LAKE TAHOE total (count 3): 10
NV total (count 5): 17
Report total (count 14): 73
Or you could use a subtotal access field to store the total of a given field, and then use that value in a calculation. Suppose you want to know what percentage of the total number of accidents for a given city the given day’s accident count was, and what percentage it was for the state. Your report might look like this:
State City Date Acc ct % of city % of state
subtotal subtotal
CA EUREKA 10/01/91 10 .67 .24
CA EUREKA 10/02/91 3 .20 .07
CA EUREKA 10/03/91 2 .13 .05
--
EUREKA total (count 3): 15
CA SACRAMENTO 10/01/91 18 .69 .44
CA SACRAMENTO 10/02/91 8 .31 .20
--
SACRAMENTO total (count 2): 26
CA total (count 5): 41
FL SARASOTA 10/01/91 3 .20 .20
FL SARASOTA 10/02/91 9 .60 .60
FL SARASOTA 10/03/91 1 .07 .07
FL SARASOTA 10/04/91 2 .13 .13
--
SARASOTA total (count 4): 15
FL total (count 4): 15
NV RENO 10/01/91 2 .29 .12
NV RENO 10/02/91 5 .71 .29
--
RENO total (count 2): 7
NV LAKE TAHOE 10/01/91 3 .30 .18
NV LAKE TAHOE 10/02/91 6 .60 .35
NV LAKE TAHOE 10/03/91 1 .10 .06
--
LAKE TAHOE total (count 3): 10
NV total (count 5): 17
Report total (count 14): 73
Another use for subtotal access fields is to subtotal one field at the lowest break level and to subtotal another field only at a higher break level. Normally, when you designate a field to be totaled in the Print Fields list, ReportWriter prints a subtotal at each break. To control the break level at which the subtotal should be printed, you would create a subtotal access field, not designate it as a field to be totaled, and then select it to be printed on the desired post-break print line. (See Creating a pre- or post-break line for more information about creating post-break print lines.)
| 1. | Select Design > Fields to create. |
You can select the Fields to create function from any of the Design function windows, enabling you to create temporary fields while specifying other report information.
When you select Fields to create, the Temporary Fields list is displayed. If you are creating a new report or adding temporary fields for the first time, the Available Field Types list is also displayed.
| 2. | From the Available Field Types list, select Subtotal Access Fields. |
|
|
| 3. | In the Subtotal Access Field Definition window, enter data in each field as instructed below. |
As an example, we’ve defined a subtotal access field that contains the running sales commission total. We can then print it in our report to see how it accumulates for each record. See figure 1.
Field name
Enter a name to identify the field. This name must be unique among all temporary fields. It must begin with a letter and the remaining characters can be letters, digits, underscores (_), or dollar signs ($).
We called our subtotal access field RUNCOMM.
Description
Enter an optional description for the subtotal access field. The default is the field name. This description is used as the default field header when this field is printed. The description is also used when this field is listed in the Print Fields list, Sort Fields list, or the list of available fields.
We used the description Running commission total.
Field to total
Enter the name of the field for which you want a total. To display the list of available fields, select Field functions > List selections. You can select any numeric field from any of the available lists. If you need assistance selecting a field, see Selecting fields.
If you select an arrayed field, ReportWriter always uses the first element of the array. To subscript the array, specifying an element other than the first, use a calculation field to assign the array element to a temporary field.
To create a subtotal access field that contains the record count for a given break level, enter <COUNT> or select Field functions > Record count.
Because our subtotal access field will contain the current total for the sales commission field, we selected ORDER.SLS_COMM from the list of fields.
Break level
Enter the name of the break field that corresponds to the break level for which you want a total. To display a list of available break fields, select Field functions > List selections. If you need assistance selecting a field, see Selecting fields.
If you want the total for the entire report, enter <REPORT> or select Field functions > Report total.
Because we want to know the running total for the entire report, we accepted the default, <REPORT>.
Type of total
Select Complete if you want the complete total for the specified break level. (ReportWriter preprocesses the records to find out the total for a given level.) Select Running to access the current cumulative, or running, total for the given field at the specified level.
We selected Running to print the current cumulative total for each record.
You can cancel creation of your subtotal access field by pressing the Abandon shortcut.
| 4. | To save your new subtotal access field definition, exit the window. |
