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, two more tabs appear in the Insert Group 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 Insert Group dialog displaying the Specified Ordering tab.

A last note on the Insert Group 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 Insert Group dialog as shown in Figure 2.11.

Figure 2.11. The Options tab of the Insert Group 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 Insert Group 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 10.
    
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 pres