Sorting and grouping in a report

Ability allows you to group records that have common values in a particular field. This is accomplished by sorting on the field. In Reports the sorting command automatically sorts the records in ascending order (according to the values in the chosen field) and then sections off the records with common values.

Within the groups created by this primary grouping, you can create a secondary grouping for records with common values in another of the fields, and so on. The secondary grouping is created by the second sort. In this way Ability provides an effective method of structuring the data in a report. In addition, you can manipulate a report so that the values of group fields act as titles for each group.

You can choose which fields to sort and group on in both Table Grid View and Report View. This is accomplished through the Sort Order page of the "Format Query" dialog, although grouping itself will only be applied when you are in a Report. To create groups in an auto report or in an already existing report do the following:

  1. Click on the Sort Order image\sortordr.gif button on the Query toolbar. The "Format Query" dialog is opened at the Sort Order page.

  2. The Available Fields box shows all the fields in the table (or query or relation) upon which the report is based. To group records by a particular field, click on the field name to select it and then click on the Add button. The field name is copied to the Selected Fields box. Ability will look at every value under this field in the table and arrange the records into groups, each group of records having a common value in the specified field.

  3. You can choose ascending (the default) or descending order for the records. Click on the field in Selected Fields to activate the Descending checkbox. For a descending order, make sure the Descending checkbox is switched on, and for an ascending order, leave it blank.

  4. It may be that you want to group the records within the primary groups. To do this, click on the field in the Available Fields box that you want to group by, and again click on Add to copy it to the Selected Fields box. It is placed after the primary group field. This tells Ability to sort and group by the first field, and then, within the groups so created, sort and group by the second field.

  5. Again you can set an order for the secondary group field. Select the field in the Selected Fields box and switch the Descending checkbox on or off. Note that the secondary grouping can have a different sort order from the primary grouping. You can have as many groups as there are fields in the original table, although of course you would only want to group where it is appropriate.

  6. To sort and group you need to switch on the Group on report checkbox for each of the selected fields that you want to group. If you leave the checkbox blank, the sort order (or orders) will be applied but the sorted records will not be grouped.

  7. Once you have defined your groups and their sort order, click on OK to close the dialog and return to the report.

The report is redrawn to show a new set of headers and footers for each group. Anything you insert into a group header or footer will be displayed at the head or foot of each group in the report (the full effect of this is only apparent when you go in to Print Preview - see Viewing a report in print preview).

Ability automatically gives your report a default layout – see Layout of a grouped report.