Tell us what you think

We want to create the best possible experience for our learners. Your feedback helps us to improve the experience for everyone.

Grouping Records

Objective

After completing this lesson, you will be able to group records in a report

Group Creation Overview

Grouping your records will sort and break your data into meaningful groups. For example, you may want to display customers per region.

You can group information based on database fields, formula fields, or data that is not contained in your data source.

Once the data is grouped, you can also summarize the information in each group.

When you add a group, you add two new areas to your report - a Group Header and Group Footer section.

These new areas are clearly visible in the Design tab and they can be found in the left margin of the Preview tab. The Group Header and Group Footer surround the Details section.

A Group #n Name field is automatically created in the Group Header. This field can be removed without affecting the Group Header and Group Footer sections. You can also re-insert the field using the Field Explorer.

The Preview Panel

The Preview Panel appears in the left margin of the Preview window showing the Group Tree. The Group Tree normally displays the names of the groups and subgroups that you created in your report.

If your report has only one level of grouping, no plus or minus icons will appear beside the group names.

When you click one of the tree nodes for the group that interests you, the program jumps immediately to the part of the report that contains the information for that group. For longer reports or reports in which you wish to jump back and forth between different groups, the smart navigation features of the Group Tree make your work extremely efficient.

The Preview Panel can be toggled on or off by using the Toggle Preview Panel icon in the Standard toolbar.

Drill-Down on Groups

In the Preview window, you can drill down on a group header to isolate information for a specific group and display data that was previously hidden.

When you double-click a group in the Preview tab, an extra tab opens beside the Design and Preview tabs, displaying the information for that group.

Click the Close View button (X) beside the page controls to close the drill-down tab.

Group Options

You apply sort order options when grouping in the Insert Group dialog box. The options for sorting your groups are:

Term	Definition
in ascending order	A to Z, 1 to 9, lowest to highest.
in descending order	Z to A, 9 to 1, highest to lowest.
in specified order	You can create your own group based on data other than the fields in the database. For example, you could create groups based on geographical areas or sales territories and name them accordingly such as Western Region, Eastern Region, Central Region, and so on.
in original order	This option leaves the records in the same order as they were stored in the database.
Use a Formula as Group Sort Order	This option enables you to use a formula to specify the sort order of groups.

The Options tab in the Insert Group dialog box lists the other types of grouping options available. These options include:

Term	Definition
Customize Group Name Field	The name of the group can be customized to suit your needs. You can choose from an existing field or build a formula that displays a different group name.
Keep Group Together	This option prevents a group from being split across pages and forces groups to stay together. If there is enough room to print all records from a group on the current page, then all the records will print on that page. If not, they will be printed on the next page.
Repeat Group Header On Each New Page	The name of the group in the Group Header using the Group Name Field is printed on the report. If, for example, you had grouped your report by region, the region name would print just above the first record of that region. If the group contains so many records that the group needs to span over more than one page, this option forces the Group Name Field to repeat on each new page for a particular group. Each group generated by the report prints a group header before printing the Details area information. Group names are commonly found in the group header. After the last record of the group has printed, the group footer prints. Summary information about the group is commonly found in the group footer.

Term

Definition

Customize Group Name Field

The name of the group can be customized to suit your needs. You can choose from an existing field or build a formula that displays a different group name.

Keep Group Together

This option prevents a group from being split across pages and forces groups to stay together. If there is enough room to print all records from a group on the current page, then all the records will print on that page. If not, they will be printed on the next page.

Repeat Group Header On Each New Page

The name of the group in the Group Header using the Group Name Field is printed on the report. If, for example, you had grouped your report by region, the region name would print just above the first record of that region.

If the group contains so many records that the group needs to span over more than one page, this option forces the Group Name Field to repeat on each new page for a particular group.

Each group generated by the report prints a group header before printing the Details area information. Group names are commonly found in the group header. After the last record of the group has printed, the group footer prints. Summary information about the group is commonly found in the group footer.

Note

If you do not want to include the group name with the group, you can clear the Insert Group Name with Group option on the Layout tab in the Options dialog box under the File menu.

Hint

Grouping overrides record sort, so check your grouping and sorting options after refreshing a report to ensure the results returned are still appropriate.

Group Modification

Once you have created groups on your report, you may find it necessary to modify them. For example, perhaps you grouped by region and would now like to group by country. You could delete the region group and add a new group, or you could redefine the existing group. Or, perhaps you want to add a second level of grouping to have your records grouped by country then by region.

Changing the Definition of a Group

You may create a group that later you want to remove because of changing circumstances or changing information needs. If your needs change, you can modify the group. For example, you created a report that was grouped by region, but now prefer to have the information grouped by country. You do not need to remove the existing group and replace it with a new one; you can redefine the existing group.

Group Deletion

You may create a group that you want to remove later because of changing circumstances or changing information needs.

To delete a group, right-click in the left hand section of the Group Header or Group Footer of the group you want to delete, and then select Delete Group from the shortcut menu. The group section is deleted from the report.

Next lesson