Working with Groups

Grouping data in a report facilitates business user analysis and enables meaningful summarizations. Examples of common and useful groupings in reports include:

• Sales Reports that group by Sales Rep, Product Line, Sales District, or Quarter

• HR Reports that group by Department, Management Level, or Tenure with the company

• Financial Reports that group by Company Division, Product Line, or Quarter

• Inventory Reports that group by Part Number, Supplier, or Manufacturing Plant

Crystal Reports provides easy-to-use grouping functionality that enables multiple types of powerful and flexible data grouping.

Inserting Groups

Taking either the sample report from this or the previous chapter, you can realize the flexibility and power of grouping in a few short steps. Assume that senior sales management in a hypothetical company is interested in viewing customer order information by Employee/Sales representative, in addition to the existing grouping by Country. The following steps will guide you through an example of how grouping can help this company accomplish this task:

1.	Select the Group option from the Insert menu or click on the Insert Group button located on the Insert toolbar. This opens the Insert Group dialog shown in Figure 2.8. Figure 2.8. The Insert Group dialog requires selecting the field to be grouped on and enables specification of some custom grouping options.
2.	The Insert Group dialog prompts for the Data field on which the group is based. The field you select can be an existing database field already on the report, a database field included in your data sources (perhaps not yet on the report), a formula field, or a SQL Expression. For this exercise, select the Employee ID field from the Orders Table for the Grouping field.
3.	Select Ascending Order for the Sort Order.
4.	Click OK, and the report changes to reflect a new grouping on Employee ID.

The results of this new grouping are shown in Figure 2.9. Note that the Employee ID grouping is automatically selected to be the lowest-level grouping. This is the standard and expected behavior when inserting new groups, but based on the sales management's hypothetical request, you will need to edit the grouping order so that Employee ID becomes the highest level and you can view an Employee's sales across countries. You will do that in the next section.

Figure 2.9. Here is a sample report that has been grouped by Country and Employee ID.

Note

The specified order selection of the Insert Group dialog is particularly interesting because of the great flexibility it provides. With this option, you can dynamically create both groups and a custom order of appearance on the report. A related geographic example would be the creation of a Continent grouping based on the country field in the database with the groupings and order of appearance specified in the Insert Group dialog. Notice that when you select specified order and name the group, two more tabs appear in the Change Group Options dialog (see Figure 2.10). These tabs enable you to specify or create dynamic groupings and also to select a method of handling the other elements that do not fit into your dynamically created groups.

Figure 2.10. The Change Group Options dialog displaying the Specified Order tab.

Another custom ordering option new to XI is the ability to conditionally sort a group based on a formula. This option is enabled through the Group Dialog box and the Use Formula As Sort Order check box. A formula can then be entered by clicking on the x+2 box.

A last note on the Change Group Options dialog is that options around group naming are available for customization. These options are accessed through the Options tab and facilitate the process of making your reports most presentable. For example, in another situation you might want to group on a country code instead of a country name for report processing efficiency (that is, numeric fields are sorted faster than string fields), but you still want to present the actual Country Name in the report. You could perform this customization through the Options tab in the Change Group Options dialog as shown in Figure 2.11.

Figure 2.11. The Options tab of the Change Group Options dialog enables you to set some custom Grouping options, such as the displayed Group name.

Reordering Groups

As you can certainly imagine, it is quite common to want to group data by different fields within a single report. It is also quite common to receive multiple reporting requests for different views of data by various levels of grouping. Some examples might be

• View sales numbers grouped by product, region, and by sales rep

• View sales numbers grouped by region, product, and by sales rep

• View sales numbers grouped by sales rep, product, and by region

During report design, one of these different grouping orders could be created initially as you did in the last section with the groups Country and Employee ID. If other grouping orders were required, these could be quickly realized through either the Crystal Reports Design window or the Group Expert. Working in the left-most report section area of the Design tab of Crystal Reports (not the Preview tab), the different groups (sections) can be dragged and dropped before or after each other, quickly rearranging the grouping order. To complete the sales management's reporting request from the last section (to group by Employee ID at the highest level and Country below that), follow these steps:

1.	Click on the Design tab of the Crystal Reports Designer if you are not already on that tab.
2.	After double-clicking and holding the last click on either the Employee ID Group Header or Footer, drag that group to the outside of the Country Grouping to dynamically re-sort the order of grouping. A hand replaces the normal cursor image when you have grabbed a group, and blue lines highlight the intended drop location before you release your click and re-sort the grouping order. Tip To facilitate identification of groups while in the Design tab, hover over a group header or footer section and a descriptive rollover tip temporarily appears.
3.	Click on the Preview tab, and you will see the benefits of your workthe same report with the groupings instantly rearranged. Figure 2.12 highlights your intended results. Figure 2.12. A sample Customer Orders report regrouped by Sales Rep (Employee ID) and then Country. Note Note the change in the Group Tree as you move back to the edited Crystal Report. This Group Tree provides an easy-to-use navigation system for end users of this report as they can drill into the group tree and then link to the exact location and group they desire.

An alternative and powerful method for reordering groups is provided with the Group Expert. It is accessed from the Report menu, and the different groups can be reordered through the up and down buttons within the Grouping dialog. This quick reordering can present your data in completely different ways, serving multiple analysis requirements with very little report development effort. The next section explores the power of the Group Expert.

Using the Group Expert

Crystal Reports provides an easy method to add multiple groups simultaneously and a central location for accessing all your current groupsthe Group Expert dialog. Accessed from the Report menu, the Group Expert dialog, shown in Figure 2.13, enables you to add multiple groups at one time and quickly reorder any specified groups.

Figure 2.13. The Group Expert dialog accessed from the Report menu enables macro-level report group reordering and option setting.

This dialog enables the selection of multiple groups in one location and provides access to the same functionality as the Change Group Options dialog through the Options button. The groups can also be easily reordered from within this dialog through use of the up and down arrow buttons, located on the upper right of the dialog area.

Grouping on Date/Time Fields

One type of grouping that is common across most organizations is date-and-time related grouping. Analysts from all industries want to see how numbers (for example, sales revenue, units shipped, units produced, employees hired, and so on) change over various periods of time. To facilitate this type of analysis, Crystal Reports provides some built-in flexibility around date-and-time grouping. When you are creating a group that is based on a Date or Time field, an extra drop-down box appears in the Insert Group dialog (see Figure 2.14). This extra Print by Section box enables the user to group the detail records in the report automatically by any number of time-related criteria. Examples include By Day, By Hour, By Quarter, or even By Second. These automatic grouping options enable quick time-oriented analysis.

Figure 2.14. The Insert Group dialog with the Date/Time grouping drop-down box expanded.

Hierarchical Grouping

Another type of special grouping that is available in Crystal Reports is hierarchical grouping. This special type of grouping enables your report data to be dynamically grouped on a hierarchy kept within a single table of your database. To enable hierarchical grouping, a group of the base-level data should be created through the standard Group Creation dialogs described previously. The Hierarchical Group option dialog can then be selected from the Report menu. To walk through a quick example, follow these steps:

1.	Create a new blank Crystal Report and connect to the Xtreme Sample Database 11.
2.	Select the Employee table for the report and Click on the OK button in the Database Expert.
3.	Open the Field Explorer, select the First Name, Last Name, Extension, and Position fields from the Employee table, and drop them into the detail section of the report.
4.	Insert a Group on Employee ID using the Insert Group dialog (accessed from the Insert menu) and select ascending sort order. Move to the Options tab of the Insert Group dialog before finishing, click on the Customize Group Name Field check box, and select the Employee Last Name field as the field to display. Now click OK in the Insert Group dialog.
5.	Select Hierarchical Grouping Options from the Report menu. You are presented with the dialog displayed in Figure 2.15. Click on the Sort Data Hierarchically option and select either Employee Supervisor ID or Employee Reports To as the parent field with an indent of 0.33 of an inch. Figure 2.15. The Hierarchical Group Options dialog accessed from the Report menu enables specification of hierarchical grouping options such as parent field and indentation.
6.	Click OK and view your new report. Figure 2.16 displays a report that should be similar and highlights the power of hierarchical grouping. Figure 2.16. A sample report that highlights the hierarchical grouping and indentation functionality. Caution When creating a hierarchical group, the only eligible parent fields are those fields in the selected data source that have the same field type (for example, number, string, date) as the Grouped On field. Note As can be seen in Figure 2.16, the value you enter in the Group Indent field affects all other objects in the same report section as your hierarchical group fields. For example, the Position and Extension fields are also indented when you added the Employee field as a hierarchical group with supervisor. With XI, a new function has been added to enable indentation of only the hierarchy records and not the other objects. This is accomplished by leaving this value as 0 (zero) and using the new conditional-X-position feature with the new HeirarchyLevel() function. A sample formula is provided in the Crystal Reports help file and a working sample report is available for download from usingcrystal.com.