Grouping Records
When a report has a lot of pages, it is hard to quickly find information as well as get a general idea of what the report is telling you. Sorting the data helps you find a specific record, but it doesn’t give you high-level summary of what the data means. Grouping records lets you summarize data in a way that the reader can quickly grasp what the report is trying to say.
Grouping records is an advanced form of sorting. It lets you create categories to visually organize the records. You can summarize the data on critical fields and perform summing operations on the data within each group. If you need to see more information, you can explore the detail records that make up the group.
Sorting records in a report results in the records being ordered differently than their natural order, but it doesn’t have any effect on how the report is structured. Grouping is different because it creates new sections in the report’s design. For every group added to a report there is a corresponding group header and group footer added. This lets you add formatting to designate when a new group starts and when the group ends. Within each section you can show report fields, formula fields or summary fields. It’s common to make fields in the group header be a different font (possibly bold) and in the footer it’s common to show sub-totals. The footer gives you a summary of the data within a group without having to read every line. If you don’t need to display the group header or footer, each can be hidden.
Adding and Customizing Groups
Adding and customizing groups is very similar to working with the other sections of a report. Add a new group by right-clicking on the report and selecting Insert > Group. This displays the dialog box shown in Figure 4-3.
Figure 4-3. The Insert Group dialog box.
The Insert Group dialog box has two tabs. The Common tab sets which field the group is based on and the sorting order of the group values. The Options tab, shown in Figure 4-4, lets you customize the group name field and set whether the group data stays together and whether the group header should repeat on each page.
Figure 4-4. The Options tab of the Insert Group dialog box.
Selecting the Grouping Field
The first dropdown box at the top of the Common tab selects the field that the group is based on. You can choose from a current field on the report, any fields in the current data source, or a formula field. When selecting a group field that is a data type of Date, Time or DateTime, you have more options on how to group. A new dropdown box appears that lets you group on the specific part of the date or time (e.g. month, quarter, hour, etc.)
Setting the Sorting Order
The second dropdown box at the top selects the sorting order of how the groups are listed. With one exception, the sorting options are what you would expect and don’t need any explanation. You can choose a sort order that is Ascending, Descending, Original Order (no sorting) or Specified Order.
Specified Order is the option that is not completely intuitive. Specified Order means that you specify the exact order to display every possible data value in that field. Once you select this option, two new tabs appear in the dialog box: Specified Order and Others. This is shown in Figure 4-5.
Figure 4-5. The Specified Order tab of the Insert Group dialog box.
The Specified Order tab is where you build the list of how the values are to be sorted. There are two controls in this dialog box. The top control is a dropdown box that lists all the possible values for the group field. The lower control is a listbox which shows the order that each value is to be listed in. Take values from the dropdown box and add them to the listbox in the appropriate order.
The order of the items in the listbox can be changed. Select the item to move and click on either the up or down arrows located to the right of the listbox.
There are two ways of adding items to the list box: adding individual items or adding named groups (a sub-group). Adding individual items is the easiest method because you simply click on one of the values from the dropdown box and it gets added to the list. Unfortunately, if you have a lot of possible values, this could be very time consuming. To make this a little easier, there is a second way of adding items to the list.
The second way of adding items is by creating named groups that specify a range of values. Specifying a group is done in the typical fashion of specifying a lower and upper bounds for the range or building a formula using Boolean logic. It may be easier to think of this as a sub-group. Any value that falls within this range gets put into the named group. This is obviously a faster way of adding the items because you don’t have to specify individual values. Named groups also give you a lot of flexibility because the formulas can be quite complex.
To create a named group, click on the New button located below the list. This brings up the dialog box called Define Named Group. You can see in Figure 4-6 that this is a fairly simple dialog box.
Figure 4-6. The Define Named Group dialog box.
Assign a name to the group in the top textbox. In the left dropdown, you specify how to filter the range of values. The dropdown box shows numerous ways to select a range of values. A few of these are: Is Equal To, Is Not One Of, and Is Between. When you select one of these operators, the proper input controls automatically appear to the right of the dropdown box. The input controls change depending upon the information needed to complete the filter. There are so many different variations of filtering options and their associated input controls, that they won’t be explained here. They are all very intuitive and you shouldn’t have any problem entering the proper data.
Within a named group, you have the option of using more than one filter. By clicking on the
The last tab in the Change Group Options dialog box is the Others tab. Since it is very possible that some reports won’t need to specify how every value will be grouped, this tab is used to accumulate all the remaining values that didn’t get included in one of the named groups.
This is also useful for reports that group on fields where the data is dynamic and new values are being added. If the new values don’t fall within the current named groups, they get associated with the group called Other.
Values in the Other group can either be excluded from the report or included in the report. If they are included in the report, they are always listed as the last group.
Customizing the Group Name
In most circumstances, the group name that is displayed on the report is the current value. For example, if you are grouping by country, then the group name is the country name. If you don’t want to display the current value as the group name, you have the option of displaying another value instead. For example, a report group that lists how many products were sold for each day of the month is typically based on the inventory number. If the report is intended for users who recognize a product’s name, but not its inventory number, then the group header won’t mean anything to the reader. You want the group header to show the product’s common name.
Under the Options tab, you can set another value to be displayed for a group’s name. Click the option button Customize Group Field Name to use another field for the group name. Select that field from the dropdown box below it. When the report runs, the value from the other field is displayed.
There are times when the group name you want to display isn’t a field in the table. It could be a custom formatting field that is derived from a formula. To make the group name display a formula instead, click the option button Use a Formula as Group Name and click the formula expert button.
Organizing the Group
The remaining two checkboxes on the Common tab affect how the group data is displayed on the report. The first checkbox controls whether the report should try to keep the entire group on the same page. The second checkbox controls whether the group header should be repeated on each page.
Setting the Keep Group Together checkbox is important if you want to prevent having only a few records of a group appearing at the bottom of a page. When this option is on, the entire group is analyzed before it is printed. If it can’t fit on the rest of the page, then the remainder of the page is left blank and the group is started at the top of the next page.
The second checkbox, Repeat Group Header on Each Page, forces the group header to print at the top of every page. This is important for groups that can span multiple pages. By default, if a group extends to a second page, its header is not printed at the top of the second page. It might not be obvious how the detail records are related to each other. If you feel that showing the group header at the top of the page makes your report easier to read, you should click this checkbox.
Sorting the Group Data
After creating the groups and running a test report, you may notice that although your groups are fine, the individual rows within the group are out of order. If this happens, it is because you still need to add sort fields to your report. Telling the report how to group data doesn’t imply that it knows which fields to use for sorting the detail records within the group. When using groups, the data within the group gets sorted according to how you set up the sort fields. If no sort fields are used, the records appear in their natural order (which still may not be what you expect because the grouping process reorders the records as well). Look at the earlier section Setting the Sorting Order for instructions on adding sort fields.
Changing the Field to Group On
Once the report is finished, you might decide that the records need to be grouped differently or you might create a new report that looks the same but has different groups. For example, the original design of the sales report groups by region and it works fine. But you also need a similar report that groups by salesperson. Rather than redo the report from scratch, you can make a copy of the report and modify the group fields that need to change. Changing the group field is done via the Group Expert dialog box.
Figure 4-7. The Group Expert dialog box.
This dialog box can be accessed in two ways. The first way is to right-click on the group header bar and select Group Expert. The other way is to click the menu options Crystal Reports > Report > Group Expert.
The groups are listed in their current order. You can use the arrow buttons to the right to rearrange their order. To modify a group field, click on the group and then click on the Options button. This brings up the Change Group Options dialog box mentioned earlier. Since this is the same dialog box you used to create the group, you can also change any of the other group properties as well.
The Group Expert dialog box lets you modify the existing groups, but you can’t add new groups with it. To do that, you have to exit the report and select the menu options Crystal Reports > Insert > Group (as mentioned at the beginning of this section).
