Working with Groups

To this point, your exposure to grouping report data has been limited to using the Report Wizard(s) introduced in Hour 4, "Using the Default Report Wizards," and a brief introduction to manual grouping in Hour 5. 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 hour, the flexibility and power of grouping can be realized in a few short steps. Let's assume that senior sales management in a hypothetical company is interested in viewing customer order information by country, in addition to the existing grouping by employee/sales rep.

1. Select the Group option from the Insert menu or click on the Insert Group button located on the Insert toolbar. This will bring up the Insert Group dialog as shown in Figure 6.8.

   Figure 6.8. The Insert Group dialog.

   

2. The Insert Group dialog prompts for the Data field that the group will be based on. 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 Country field from the Customer Table for the Grouping field.

3. Select Ascending Order for the Sort Order.

4. Click OK, and the report will be changed to reflect a new grouping on country.

The results of this new grouping are shown in Figure 6.9. Note that the country 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 request, we will need to edit the grouping order so that country becomes the highest level. We will do that in the next section.

Figure 6.9. A sample report with Country and Customer Name groupings.

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. You will notice that when you select specified order, two more tabs will appear in the Insert Group dialog (see Figure 6.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 6.10. The Insert Group dialog with the Specified Ordering tabs displayed.

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 highlighted in Figure 6.11.

Figure 6.11. The Options tab of the Insert Group dialog.

Re-ordering 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 we did in the last section with the groups Customer Name and Country. If the other grouping orders were required, these could be quickly accomplished 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 Country at the highest level and Customer below that), follow the following quick 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 Country Group Header or Footer, drag that group to the outside of the Customer Name Grouping to dynamically re-sort the order of grouping. A hand will replace the normal cursor image when you have grabbed a group, and blue lines will highlight the intended drop location before you release your click and re-sort the grouping order.

   

   To facilitate identification of groups while in the Design tab, hover over a group header or footer section and a descriptive rollover tip will temporarily appear.

   
   

3. Click on the Preview tab, and you will see the benefits of your work the same report with the groupings instantly rearranged. Figure 6.12 highlights your intended results.

   Figure 6.12. A sample Customer Orders report re-grouped by country and then customer name.

   

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 re-ordered through the up and down buttons within the Grouping dialog. This quick re-ordering 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 groups the Group Expert dialog. Accessed from the Report menu, the Group Expert dialog, displayed in Figure 6.13, enables the addition of multiple groups at one time and the quick re-ordering of any specified groups.

Figure 6.13. The Group Expert dialog.

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 re-ordered 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 will appear in the Insert Group dialog (see Figure 6.14). This extra Print by Section box enables the user to have the detail records in the report automatically grouped 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 6.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, try the following steps:

1. Create a new blank Crystal Report and connect to the Xtreme Sample Database(hint: use the ODBC listing).

2. Select the Employee table for the report and Click on the OK button in the Database Expert.

3. Call up 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 Last Name using the Insert Group dialog (accessed from the Insert menu). Select ascending sort order and click OK in the Insert Group dialog.

5. Select Hierarchical Grouping Options from the Report menu. You will be presented with the dialog displayed in Figure 6.15. Click on the Sort Data Hierarchically option and select either Employee Supervisor or Employee Reports To as the parent field with an indent of 0.33 of an inch.

   Figure 6.15. The Hierarchical Options dialog.

   

6. Click OK and view your new report. Figure 6.16 displays a report that should be similar and highlights the power of hierarchical grouping.

   Figure 6.16. A sample hierarchical report.

   

When creating a hierarchical group, the potential Parent fields are selected from the fields in the selected data source that have the same field type (for example, number, string, date) as the Grouped On field.