The first step in creating a subsummary report is creating the summary fields. These fields allow you to define the specific type of aggregated value you want to generate. Each summary field involves two elements:
- The type of summary (i.e. aggregated value) you want to generate
- The field to be summarized
For example, let's say that your solution includes a number field called “Price Paid”. You might create a "Total Price Paid" summary field that generates the total of the field “Price Paid”.
Summary field types
There are eight different summary field types. Unless otherwise noted, they can only be associated with number, date, or time fields:
- Total - adds up the values of a field.
- Average - calculates the average across the values of a field.
- Count - counts non-empty values of a field. (Can be applied to any field type.)
- Minimum - calculates the lowest (or earliest) value of a field.
- Maximum - calculates the highest (or latest) value of a field.
- Standard Deviation - calculates how widely the values in a field differ from each other.
- Fraction of Total - calculates the ratio of a single field’s value against the total for the field.
- List - generates a return-separated list of all field values. (Can be applied to text fields as well.)
One of the simplest operations you can perform is counting records in your found set. Since the "Count" summary type only counts non-empty values of a field, in this case you should associate it with a field that always has a value. A good choice is your primary key, since as an auto-enter serial number it will always be populated.
Summary field placement
Once you've created your summary field, just place it on your layout and view it in Browse mode. Depending on where you put it, it will summarize differently:
- When you can place it in a header, footer, body, or grand summary part, it aggregates the values from the field to be summarized across all records in the found set.
- When you place it in a subsummary part, it aggregates these values separately for each subgroup of records (though only when the records are sorted by the subsummary part's break field).
This means you could place your “Total Price Paid” field on a subsummary part in order to calculate subtotals for groups of records, and also place it in a grand summary part (or the header) to calculate the grand total for the found set.
Create a summary field that counts every record by using your Activity ID field as the field to summarize.
- Make sure you have the Manage Database… dialog open.
- Select the Fields tab at the top of the dialog.
- Choose your secondary table from the list in the upper left.
In the example solution, this is “Activities”.
- For the Field Name, enter: Count of [Secondary Table ID]
The example solution uses “Count of Activity ID”.
- Change the Type drop down to “Summary”.
- Click the Create button.
An Options for Summary Field… dialog will appear.
- Select the Count of option.
- Select the primary key field of your secondary table.
The example solution uses “Activity ID”.
- Click the OK button to save your changes.