Sorting records by subsummary values

To properly display a report with grouped data and subtotals (also known as a subsummary report), data must be sorted to display the appropriate break fields. If you have a subsummary report, data in the report is grouped and displayed by the category that produced the totals. However, you can specify a subsummary value to sort on to reorder data more logically in a report.

Note  If you create leading or trailing groups in Table View, the data is automatically sorted by the field that you used to group the data.

Suppose you create a report that subtotals sales by region. You can sort data in the report by region (Example 1) or you can specify that records in the report are sorted by subsummary values, which lists data by total sales (Example 2).

In both examples, Total Sales is a summary field that shows regional totals when sorted by Region.

Example 1: Report with grouped data and subtotals, not sorted by subsummary values

Total sales sorted by region in ascending order

Example 2: Report with grouped data and subtotals, sorted by subsummary values

Regions sorted by total sales in descending order

To sort records by subsummary values:

  1. Create a report with grouped data and subtotals. Include the field you use to categorize the records (in this example, Region) and the summary field (here, Total Sales) in the report.

    For more information on creating a report with grouped data and subtotals, see Creating a layout.

    Note  You can also create a report with grouped data and subtotals in Table View in Browse mode. See Creating dynamic reports in Table View.

  2. In Browse mode, click Sort in the status toolbar, or choose Records menu > Sort Records.
  3. Move the grouping field (Region) to the Sort Order list, or select it if it is already there.
  4. Select Reorder based on summary field or click Specify.
  5. In the Specify Field dialog box, select a summary field (Total Sales) and click OK.
  6. Select a sort order (in this example, Descending order).
  7. Click Sort.
  8. Click Table View Table View button or List View List View button in the layout bar, go to Preview mode, or print to see the sorted results.

Tip  If data is already sorted by the grouping field, you don't have to re-sort when you add or remove a summary field in the sort order. Specify the summary field in the Specify Field dialog box as described above, then click OK.

Related topics