Grouping Records | Crystal Reports 10: The Complete Reference

Sorting records is handy for lists or other simple reports that just need records to appear in a certain order. It s more common, however, to want not only to have your report sorted by certain fields, but also to have subtotals, counts, averages, or other summary information appear when the sort field changes. To accomplish this, you must use Crystal Reports group s. In other reporting tools and mainframe development languages, this was often referred to as report level breaks .

When you create a report group, you both sort the records on the report and create two additional report sections every time the group field changes. You may place subtotals, averages, counts, and many other types of summary information in these sections. In addition, grouping your report enables the group tree, an Explorer-like window on the left side of the Preview tab. The group tree gives you a quick overview of the organization of your report and enables you to navigate directly to a particular group that you want to see.

To create a report group, you may either choose Insert Group from the pull-down menus or click the Insert Group button from the Insert Tools toolbar. The Insert Group dialog box appears. It contains two tabs: Common (shown next ) and Options (discussed later).

There are two drop-down lists in the Common tab that you use to insert a group. Follow these steps to complete the dialog box:

Click the top drop-down list to select the field you want to group on. The list will display fields on your report, as well as other fields in the tables that the report is using. You can group by a field already on the report or by a database field that you didn t put on the report. You can also group by a formula field.
Click the second drop-down list to select the order in which your groups will appear on the report. There are four options: ascending order shows the groups in A to Z order alphabetically ; descending order shows the groups in Z to A order; specified order lets you create your own groups (described later in this chapter); and original order groups records in the order in which they appear in the database. (This last option is an interesting feature, but probably not useful in most reports.)

If you wish to allow Crystal Reports to make typical choices for the way groups appear, you may simply click OK now to create the new group. However, if you want to set additional options for the group, click the Options tab.

Make Options tab choices as follows :

If you simply want to have the database field itself appear in the groups on the report and the group tree, leave the Customize Group Name Field check box empty. However, if you d like to customize the way the groups appear (perhaps you d like a month fully spelled out along with a four-digit year for a date field), click the Customize Group Name Field check box. You can then make additional choices to determine what appears for the group. Customizing Group Name Fields appears later in the chapter.
Click the Keep Group Together option if you want Crystal Reports to try to keep your groups from breaking at the end of a page. If you leave this option unchecked, the beginning of a group and just a few detail records in the group may print at the bottom of a page, while the rest of the group s detail records and its subtotals may appear at the top of the next page.

Click the Repeat Group Header on Each Page option if you think you will have large groups that will span more than a single page. This option will print the group header section (described later) at the top of each page where a group continues. This allows you to look at details sections on subsequent pages and know which group they belong to.

Caution

Clicking Keep Group Together can cause odd behavior if the first group in your report won t fit on a page by itself. In this case, Crystal Reports will detect that it can t fit the group on the first page of the report and will start a new page before it starts printing the group. The result will be a blank first page. If this happens, you may resolve the problem by suppressing the report header section. If you do this but still want material to print at the top of the first page only, create separate Page Header A and B sections, and conditionally format page header A to suppress if the page number is greater than 1. Formatting report sections is described in Chapter 10.

Figure 3-1 shows the two new sections that are added to the Design tab, the group header and group footer. These sections appear at the beginning and end of every group. Note that Crystal Reports places an object in the group header section automatically. This group name object will automatically print the contents of the field on which the group is based in each group header. The group footer section is empty.

Figure 3-1: Group added to a report

Tip

You may turn off the Insert Group Name with Group option in File Options if you don t want Crystal Reports to automatically insert a group name object in the group header when a group is created. If this is turned off, or if you inadvertently delete a group name object, you can insert a group name from the Field Explorer by dragging and dropping a group name object after opening the Group Name Fields category.

Figure 3-2 shows the Preview tab with the now-active group tree. You can see your groups in an outline form and navigate directly to the beginning of one of the groups by clicking the group in the group tree. You can also turn the display of the group tree on and off by clicking the Toggle Group Tree button on the Standard toolbar.

Figure 3-2: Group Tree in Preview tab

Tip	In addition to the steps previously outlined, you may use the Group Expert (discussed later in the chapter) to create a new group.

Manipulating Existing Groups

After you create a group, you may wish to delete it so that only detail records print again without grouping. Or, you may wish to change the field that the group is based on, change the order of the groups from ascending to descending, customize the group name field, or choose one of the formatting options to control the way Crystal Reports deals with page breaks inside groups.

One way to do this is to point your mouse to a group header or footer section in the gray section area of the left side of the Design or Preview tab (the section will be abbreviated GH and GF in the Preview tab). You must point to the group header or group footer for the group you wish to delete, right-click, and choose Delete Group from the pop-up menu. The group header and group footer sections will be removed from the report, along with any objects in them. You may also use the new Group Expert, discussed in the following section, to delete a group.

There are also several ways to change existing groups by redisplaying the dialog box that appeared when you created the group. You may change the field that the group is based on, change the order of groups (ascending, descending, specified, original), customize the group name field, or select the Keep Group Together or Repeat Group Header on Each New Page option.

As when deleting groups, you may point to the group header or group footer section of the group you want to change in the Design or Preview tab. Then right-click and choose Change Group from the pop-up menu. The Change Group Options dialog box will reappear, where you can change group settings on either the Common or Options tab and then click OK when you re finished. You may also use the new Group Expert, discussed in the following section, to change a group.

The Group Expert

Another Crystal Reports approach to group manipulation is the Group Expert , which is designed to let you perform the grouping options described previously in the chapter, such as creating new groups, modifying existing groups, reordering groups, and deleting groups. To display the Group Expert, choose Report Group Expert from the pull-down menus, or click the Group Expert button in the Expert Tools toolbar. The Group Expert will appear, as shown in Figure 3-3.

Figure 3-3: The Group Expert

Here, you can add a new group to the report by selecting the field from the Available Fields list that you want to use, and clicking the right arrow to add the group. If you have an existing group on the report that you wish to remove, select that group in the Group By list and click the left arrow to remove it. And, if you want to choose options for a group on the report (either a new group you just added or an existing group that was added previously), select the group in the Group By list that you want to modify and click the Options button. The Group Options dialog box will appear. Choose the Common or Options tab and choose or change appropriate options.

If your report contains more than one group (discussed in more detail later in the chapter under Multiple Groups ), you may wish to change the order in which the groups appear. For example, you may have initially grouped your report first by Customer, and within Customer by Region. If you want to change the order of the groups to be by Region and then Customer, you would select a group in the Group By list and use the up and down arrows above the Group By list to change the order of the groups.

Adding Summaries

So, what s the difference between just sorting the report and creating a group? Not only is the group tree useful with grouped reports, but you now have a section available for subtotals, averages, counts, and other summary functions at the end of each group. Although the group footer is empty when first created, you can insert summary functions into it with ease.

Inserting summaries in your report requires several steps:

Choose Insert Summary from the pull-down menus, or right-click the field you want to summarize in the Details section and choose Insert Summary from the pop-up menu. To insert a summary, you can also click the Summary button on the Insert Tools toolbar. The Insert Summary dialog box will appear.
If you selected a field on the report before you chose an Insert Summary option, that field will be pre-selected in the Choose Field to Summarize drop-down list. Otherwise, choose the field you wish to total, average, or otherwise summarize, in this list.
Choose the summary function (sum, average, and so on) that you wish to use. Then, either choose to insert a grand summary for the entire report (the default), or choose the existing group in which you want to place the summary. If you wish to create a new group on the report for your summary, click the Insert Group button to create a new group.
If you want to create a percentage summary field, click the Show As a Percentage Of check box and choose from the drop-down list the field that you want to show the percentage of (percentage summary fields are discussed in detail later in the chapter). You must choose a report group before you can check this box ”Percentage Summaries can t be created in the report footer.
If the group you ve chosen is a hierarchical group (discussed later in the chapter under Hierarchical Groups ), you may check the Summarize Across Hierarchy check box to include the summary in all hierarchical groups.
Click OK to place the subtotal or summary in the group footer or report footer directly below the detail field that you re summarizing.
Since Crystal Reports doesn t label group summaries for you, you should add text objects next to the summaries to indicate what they display. For example, a subtotal won t be confused with an average if you place a text object containing Subtotal: next to the subtotal object.

Here are a few pointers to keep in mind when inserting summaries:

Although Crystal Reports places subtotals and summaries in the group footer or report footer by default, you don t have to leave them there. If you move them to the group header or report header, they ll print the same information, but at the beginning of groups or the report.
Once a summary or subtotal has been created, you don t have to delete it and insert a new summary if you want to change its function (for example, to change a subtotal to an average). Simply click the subtotal or summary and choose Edit Summary from the pull-down menus, or right-click the summary and choose Edit Summary from the pop-up menu.
If you want to insert a summary in one or more group footers and the report footer (to summarize for multiple groups and the entire report), you don t have to use the Insert Summary options over and over for each group and the entire report. Just insert the summary into one group and then copy the summary from that group footer to the other groups or the report footer by using Edit menu options, or just CTRL -dragging the summary from a group footer to copy it to another section.

Table 3-1 shows the different summary functions that are available in Crystal Reports and what each does.

Table 3-1: Summary Functions
Function	Results
Sum	Returns the subtotal of the chosen field; available only for number or currency fields. Choosing a summary with the Sum function is exactly the same as inserting a subtotal.
Average	Returns the average of the chosen field; available only for number or currency fields.
Sample Variance Sample Standard Deviation Population Variance Population Standard Deviation	For number of currency fields only, these functions return the statistical result for the group or report values. For detailed descriptions on the specific steps performed to arrive at the results, consult a statistics text or Crystal Reports online help.
Maximum	For number or currency fields, returns the highest number in the group. For string fields, returns the last member of the group alphabetically. For date fields, returns the latest date in the group.
Minimum	For number or currency fields, returns the lowest number in the group. For string fields, returns the first member of the group alphabetically. For date fields, returns the earliest date in the group.
Count	Simply counts the records in the details section and returns the number of records in the group. Although you are required to choose a database field before selecting this option, the Count function will return the same number no matter which field you choose (with the exception of fields that contain null values).
Distinct Count	Similar to the Count function, but returns only the distinct number of occurrences of the field. As opposed to Count, the field you choose in the details section before choosing Distinct Count is very significant. For example, if five records contain the strings Los Angeles, Chicago, Vancouver, Chicago, and Miami, the Count function would return 5, whereas Distinct Count would return 4.
Correlation Covariance	For number of currency fields only, these functions return the statistical result for the group or report values. When you choose these functions, an additional drop-down list appears where you may choose an additional report field to complete the function. For detailed descriptions on the specific steps performed to arrive at the results, consult a statistics text or Crystal Reports online help.
Median	For number and currency fields only. Returns the median, or middle, number in the group. If there is one number in the group, it is returned. If there are two numbers , their average is returned.
Mode	Returns the most frequently occurring value from all the detail records in the group. For numeric fields, Mode returns the most frequently occurring number. For string fields, Mode returns the most frequently occurring string (for example, with detail records containing five occurrences of FedEx, three occurrences of UPS, and eight occurrences of Parcel Post, Mode would return Parcel Post).
Nth Largest	Returns the third, fifth, or tenth (and so forth) largest value in the group, depending on the value you enter for N . For example, N th largest, with N equaling 1, returns the largest value in the group. When you choose this function, an additional box appears in which you can enter the value for N . For numeric fields, this function returns the N th-highest numeric value. With string fields, it returns the N th value alphabetically (for example, if there are three records containing FedEx and two records containing UPS, N th largest when N equals 2 will be UPS, and N th largest when N equals 3 will be FedEx).
Nth Smallest	Returns the third, fifth, tenth (and so forth) smallest value in the group. For example, N th smallest with N equaling 1 returns the smallest value in the group. When you choose this function, an additional box appears in which you can enter the value for N . This function behaves similarly to N th highest with both numeric and string fields.
Nth Most Frequent	Returns the third, fifth, tenth (and so forth) most frequent occurrence in the group. This is similar to Mode, except that you're not limited to just the most frequent occurrence.
Pth Percentile	For number and currency fields only. When you choose this function, an additional box appears in which you can enter a number between 0 and 100 for P . This function returns the number that indicates what the percentile is for P , based on all the numbers in the group.
Weighted average	For number and currency fields only. This will calculate an average of group or report values, but apply the weight of another field to adjust the average. When you choose this function, an additional drop-down list appears where you may choose an additional report field to complete the function.

Caution

If the details field that you summarize contains null values (a special database value where the field actually contains nothing, as opposed to a zero or empty string), the summary function won t count that record. For example, a Count or Average won t figure the null record into its calculation. If you wish to avoid this problem with the current report, you can convert database null values to zero or empty string values by choosing File Report Options and clicking Convert Database NULL Values to Default. If you want this to be the default option for all new reports, you can choose the same option on the Reporting tab from File Options.

Percentage Summary Fields

Although the default summary functions satisfy most needs for analytical reporting, sometimes you may prefer to calculate percentages rather than whole numbers. For example, if your report groups sales by sales rep within each month, you may want to know both the actual dollars and the percentage of revenues each sales rep is responsible for in that month. And, you may also want to calculate the percentage of total sales for the entire year that each month is responsible for. Such an example is shown in Figure 3-4. Crystal Reports features the percentage summary field, which creates this type of calculation without the need for a formula.

Figure 3-4: Percentages created with Percentage Summary field

To create a percentage summary field, follow the steps previously outlined to insert a summary field. Choose the field you want to use to calculate the percentage (the numerator ) and the group in which you want the percentage placed, as though you were creating a regular summary. Then, click the Show As a Percentage Of check box and use the drop-down list to choose the higher-level group or grand total you want used as the denominator. If you are placing the percentage in the highest-level (or only) group on the report, you ll only be able to choose a grand total for the denominator. Click OK.

Crystal Reports will place a summary field in the group footer of the group you specified. When you preview the report, however, you ll see a percentage number rather than a count, subtotal, or other number. The percentage summary field will already be formatted to display a percent sign.

Multiple Groups

Crystal Reports does not limit your report to just one level of grouping. In fact, many powerful reporting features can be provided to your report viewer by creative use of multiple groups. The key to many sophisticated reporting requirements lies in creative use of formulas (covered in Chapter 5) in conjunction with multiple levels of grouping.

Multiple groups form a report hierarchy, with increasing levels of detailed information being presented by inner groups. For example, a report might be grouped by country first. Within the country group would be a geographic region group (Northwest, Southwest, and so forth), and then a group by state, a group by county or township, a group by city, and finally detail records showing individual customers or orders within that city. Each group has its own group header and group footer sections, and subtotals and summaries can exist for each group.

The group tree handles multiple levels of grouping very elegantly, following the general style of Windows Explorer. Plus signs (expand buttons) are displayed next to groups that can be expanded to display inner groups; minus signs (collapse buttons ) are shown beside expanded groups that can be collapsed . Figure 3-5 shows a report with multiple groups. Notice that you can navigate through the group tree by clicking the plus and minus signs to open and close group levels. When you find the group you want to see, click the group name in the group tree to go directly to the beginning of that group in the report, no matter how deep the group is in the hierarchy.

Figure 3-5: Multiple levels of grouping

To create additional levels of grouping, simply repeat the process described previously for inserting a group, either using the Group Expert or the Insert Group options. The groups will appear in the order that you create them. You can use the Insert Summary options to add subtotals and summaries to the group footer and group header sections.

You may inadvertently create groups in the wrong order. For example, if you wish to have your report grouped by state and then by city, make sure to create the groups in that order. If you create the city group first, followed by the state group, you ll have one group for each city, with whatever state that city is in as a lower-level group. You d have, for example, a group for Boulder containing a Colorado group. Below that, you d have a group for Denver containing another Colorado group.

This isn t as much of a problem as it might seem. You don t have to delete groups and reinsert them in the desired order ”moving them around is surprisingly easy. Simply return to the Design tab and point to the gray Group Header or Group Footer area on the left side of the screen. Point to the group that needs to be moved, and hold down the left mouse button ”the mouse cursor turns into a hand symbol.

You can now simply drag and drop the group header or group footer on top of the group that you wish to swap it with. When you release the mouse button, the groups swap locations. Don t be confused by the fact that the groups stay numbered in sequential order ”the groups have been moved. In the preceding example, simply dropping the state group header (Group Header #2) on the city group header (Group Header #1) will swap the groups. The state group header becomes Group Header #1, and the city group header becomes Group Header #2.

Tip	The Group Expert also allows you to easily swap the order of groups.

Specified Order Grouping

Sometimes, you may need data grouped on your report in a special order that the database doesn t offer. For example, the database may contain a state field, but not a field indicating what geographic location the record belongs to (Northwest, Southwest, and so on). One option that may be appropriate for more sophisticated customized grouping is basing a group on a sophisticated formula. However, if your customized grouping is not particularly complicated, specified order grouping may be more straightforward. This allows you to create customized groups without having to know the Crystal Reports formula language.

Specified order grouping lets you use a dialog box similar to the Select Expert (discussed in Chapter 8) to create your own groups, based on an existing database field. In the geographic location example used previously, you could create a Northwest group consisting of Washington, Oregon, Idaho, and Montana. The Southwest group could include Nevada, California, Texas, and Arizona. The Northeast group could consist of New York, Maine, Vermont, and New Hampshire. Southeast could include Florida, Alabama, Louisiana, and Mississippi. All the other states not in these four groups could either be ignored, placed in their own individual groups, or lumped together in one final group given a name of your choice, such as Midwest.

To specify your own groups, choose Specified Order in the Group dialog box instead of Ascending or Descending order when you create a new group or change an existing group. When you choose Specified Order, a Specified Order tab is added to the Change Group Options dialog box.

Click the New button to create a new named group. This will display the Define Named Group dialog box, shown next. Type the name the group should have on the report, such as Northwest. Then, using options in the tabs, indicate which records will be included in the group. For example, you may want the Northwest group to include records in which Customer.Region is one of WA, OR, and ID.

If you need to use several different criteria for the named group, you can click the <New> tab and add additional criteria for the group. When you re finished, click OK on the Define Named Group dialog box. The named group will be created and will appear in the list of named groups in the Group Options dialog box. You can now click the New button again to add additional named groups (for example, Southwest, including AZ, CA, and TX).

Tip	Remember that clicking the <New> tab to create more than one selection tab still only allows you to select by one field ”the field your group is created on. The criteria on the tabs will be joined using a logical Or operation ”this is different from the Select Expert.

As you add new named groups, they appear in the Specified Order tab of the Change Group Options dialog box in the order that you created them, not in alphabetical order. If you wish to change the order in which the named groups appear on the report, select a named group and use the up or down arrow next to the list of named groups to change its position. The Other group, however, will always be last, no matter what you name it or how you position named groups with the arrow buttons.

After you create at least one named group, the Others tab appears in the Group dialog box. You use this tab to deal with any records that haven t been caught by your specific named groups. You can discard the remaining records, place them in one catchall group with the name of your choice, or leave them in their own groups based on the database field. In the geographic example, Northwest, Southwest, Northeast, and Southeast have all been created as named groups. The Others tab is used to lump any regions that weren t otherwise specified into a Midwest group.

Tip

You might have noticed the Named Group drop-down list on the Specified Order tab. This list will browse the database, showing you samples of the actual field you re grouping on. If you choose one of these samples, Crystal Reports will create a named group with the same name as the actual database field. This is handy if you want to create the same groups as you would by using Ascending or Descending order, but place the groups in the specific order you desire .

After you create your named groups and click OK on the Change Group Options dialog box, the report will reflect your new grouping. Figure 3-6 shows a report grouped by the five geographic areas described previously. If you wish to change any of your specified grouping options, or remove them altogether so that records appear in their own groups, just use the steps mentioned previously to change an existing group. The Change Group Options dialog box will open, and you can edit your specified groups or change grouping to ascending or descending order, which will remove specified grouping.

Figure 3-6: Report with Specified Order grouping

Drilling Down on Data

One of the most powerful features of Crystal Reports is its online reporting capability. Although you can print reports on a printer and export them to other file formats, such as Word or Excel, the real power of many reports becomes available only when users can view and interact with them online. This means that the user directly views the .RPT file that Crystal Reports creates. This affords two benefits:

A user can rerun the report whenever they want, seeing an updated view of the database at that moment.
A user can interact with the report by using the group tree and drill-down capabilities.

Various Crystal Reports 10 editions offer online interactive reporting to users in several ways:

Giving users their own copy of Crystal Reports, letting them open, view, and modify reports at will
Reporting with a Web browser and Crystal Enterprise (covered in Part II)
Including a Crystal Report in a custom Windows or Java application (covered in Part III)

All of these interactive methods allow a user to drill down on data in the report. This technique, a feature that has been carried over from early PC-based decision-support system software, allows a report viewer to initially look at higher-level data. For example, a report might start at the country level. If the viewer sees a subtotal or summary number (or an element in a pie chart or other chart) for a particular country that interests them, they can double-click that number. This will drill down to the next level in the report, possibly the region or state level, where they can see summary numbers for each of those states or regions. If the report is designed with several levels of drill-down ability, the user could then double-click a region that piqued their interest to display all the cities in that region. The drilling down could progress further, allowing users to drill down on cities and finally ZIP codes, where individual detail items at the ZIP-code level would appear.

Crystal Reports automatically sets up a drill-down hierarchy when you create groups. Every group you create can be drilled into, exposing the lower-level group and finally the details section. So, for our drill-down example to work, you would create multiple groups on the report in the following order: country, region (state), city, and finally ZIP code.

After you create groups, you can drill down on the group name (automatically placed in the group header) or any summary or subtotal that you place in a group header or group footer. When you point at these objects, you ll notice that the mouse pointer changes from an arrow to a magnifying glass (or a finger cursor in a web page), called a drill-down cursor. This indicates that you can drill down on the group by double-clicking this object (or single-clicking in a web page). When you double-click, a drill-down tab appears next to the main Preview tab, containing the lower-level group or detail data. Every time you drill down, a separate drill-down tab appears.

If you drill down enough times, there won t be enough room to see all the drill-down tabs, along with the main Preview and Design tabs. In this situation, two small arrows appear next to the last drill-down tab. You can click these arrows to move back and forth among the tabs to see tabs that have disappeared off the screen. You can also close any drill-down tab by clicking the red X button next to the page-navigation buttons. This closes the current tab and displays the tab to the left. You can close every tab this way (including the Preview tab), except for the Design tab.

Tip

If you wish to print the report to a printer or export it to another file format, only the material appearing in the current tab will print or export. If you re displaying the Preview tab at the time, the summarized report will print or export. If, however, you have a drill-down tab selected when you print or export, only the material in that drill-down tab will be included.

Figure 3-7 shows a report with several drill-down tabs visible. Notice that there isn t enough room to show all the tabs, so small arrows appear to the right of the last drill-down tab. Also notice that the mouse cursor has changed to a magnifying glass because it is over a group name object.

Figure 3-7: Drill-down report

You can drill down on any report that has at least one group on it, even if all the details sections are showing already. Drill-down ability is really helpful with summary reports that start out showing only high-level data. A viewer will only want to see the lower-level groups and the detail when they drill down. Therefore, you ll want to hide the details section, as well as the lower-level group headers and group footers, to create a truly useful drill-down report. You ll see how to hide or suppress sections in Chapter 10.

Tip

Controlling detailed drill-down behavior, such as deciding if you want to show all higher-level group headers when you drill down, or whether to show a particular group header in some drill-down tabs, but not others, may require use of Crystal Reports formulas or conditional formatting. A DrillDownGroupLevel function is available in Crystal Reports to help with this custom drill-down behavior. Look in the Print State category of the Formula Editor functions tree to find this new function. Formulas are covered in more detail in Chapter 5, and conditional formatting is covered in more detail in Chapters 9 and 10.

Grouping on Date Fields

When you create report groups based on date fields, you probably don t want a new group to appear every time the date changes from one day to another. You may only want a new group for every week, month, or calendar quarter. You could create a complicated formula that breaks down groups in this manner and groups on the formula, but Crystal Reports provides a much easier way.

When you select a date field to group on, Crystal Reports automatically adds an additional drop-down list to the Insert Group dialog box.

You can choose how often a new group will be created by selecting the appropriate item from the list. Then click OK. The groups will now appear in the group tree for every month, quarter, year, or whatever period was chosen. The group name in the group header will indicate the beginning date for each group (the first month of the quarter, the first day of the week, and so forth).

Customizing Group Name Fields

The group name field takes on the Crystal Reports default format for the data type of the group field. For example, the default format for date fields is the same as your Windows default date format. Thus, a group name for a calendar quarter group may show up as 1/2001, 4/2001, and so on. What if you prefer the group names to appear as January, 2001 and April, 2001 ?

You can format a group name field just like any other field or object. Click the group name field and then format it just like any other object (by using the Format menu, by using the Format toolbar button in the Expert Tools toolbar, or by right-clicking and using the pop-up menu). For a group name based on a date field, for example, you can choose how the month and year appear, as well as what character should be used as the separator between them.

However, you ll notice that even when you format the group name field as mentioned here, the group tree will not reflect the change. It continues to use the default formatting regardless of how you format the group name field. Also, you may find situations in which you want to show information for the group that is different from what is actually supplied by the field you group on. For example, you may be grouping by a fiscal month number in a database. You ll want to show the spelled-out month name despite the fact that the group tree and group headers will show the number (1 for January, 8 for August, and so on). Or, you may be grouping by employee number rather than employee name, to avoid the possibility of lumping employee data together for employees that share the same name. But, you still want the report to show the employee name instead of the number.

You may create a formula and group on it instead of the database field to accomplish this type of specialized group display. However, you ll often not get exactly the results you want (in the numeric month example mentioned previously, you will need to add the month number in front of the spelled-out month if you want to still show the months in chronological order). However, Crystal Reports provides the customized group name field, which gives you much more flexibility in controlling what the group tree and group header display.

You may customize the group name field when you initially create a group. Or, if you decide later that you d like to customize the group name field in an existing group, you can change the group by using the Group Expert, or by right-clicking the appropriate group header or group footer gray section name and choosing Change Group from the pop-up menu. Once the Change Group Options dialog box appears, perform the following steps to customize the group name field:

Click the Options tab.
Click the Customize Group Name Field check box.
To choose an alternate database field to display (for example, if you d like to show an employee name field instead of the employee number field the group is based on), click the Choose from Existing Field radio button and then pick the desired database field in the associated drop-down list.
To create a specialized formula to display instead of the field the group is based on, click the Use a Formula As Group Name radio button. Then, click the Conditional Formula button next to the radio button. The group name Formula Editor appears, in which you can create a string formula to display instead of the field the group is based on. For example, to show an employee s last name, a comma, and then the employee s first name, you could create the following formula:
```
 {Employee.Last Name} & ", " & {Employee.First Name} 
```

Look at Chapter 5 for details on creating formulas.

Grouping on Formula Fields

As your reports become more sophisticated, you ll find more and more often that you won t be able to create the groups you need just from database fields. You may be able to use specified order grouping, but even it is limited by its simple Select Expert “like approach. When your creative grouping with database fields runs out of steam , you need to create formula fields and group on them. Creating formula fields is covered in Chapter 5.

Grouping on a formula field is very simple (at least the grouping is, after you create the formula). The formula appears at the end of the list of report fields in the Insert Group or Change Group Options dialog box. Simply choose it as the formula field you wish to group on.

One of the immediate benefits of grouping on a formula is being able to change your report grouping on-the-fly by use of Crystal Reports parameter fields (covered in Chapter 14). By using a parameter field to change the value that a formula returns, and then grouping on the formula, you can dynamically change report grouping depending on user input.

The @Group By formula field can now be used instead of the database field to dynamically group the report on Country or Region, as chosen by the report viewer.

Caution

Although you gain great flexibility when you group on a formula field, you may lose a little performance along the way. When you group on a database field, Crystal Reports can have the database server (SQL Server, Oracle, and so on) sort records in the proper group order before sending them to Crystal Reports. When you group on a formula field, the server won t be able to sort the records in advance, leaving that for Crystal Reports to do once the records begin to arrive from the server. You may or may not notice any performance degradation, depending on the size of the report and the speed of your computer. If you really want to maximize performance and still have a customized group, you may be able to substitute a SQL expression for a formula as the source for your group. SQL expressions are covered in Chapter 16.

Top N Reporting

Figure 3-8 shows a typical order summary report by customer name. This is a great drill-down report example ”the details section is hidden, and only the summary information for each customer is showing (hiding sections is covered in Chapter 10). This is a good report for the sales manager who is asked, How did Barry s Bikes do last year? All the viewer has to do is click Barry s Bikes in the group tree to go directly to its summaries.

Figure 3-8: Order summary by customer

However, what if the sales manager has ten boxes of Godiva chocolates that she wants to send to her 10 best customers? Or, consider the new sales associate who s been assigned the task of visiting the 15 worst-performing customers to try to bolster sales. The report shown in Figure 3-8 is not very useful if you want to find the top 10 or bottom 15 customers. The sales manager and sales associate would be much happier with a Top N report.

A Top N report lets you sort your groups by a subtotal or summary function (subtotal of order amount, for example), instead of by the name of the group. That way, your groups will appear, for example, in order of highest to lowest sales or lowest to highest sales. In addition, Top N reporting enables you to see only the top or bottom N groups, where you specify the N.

Crystal Reports uses the Group Sort Expert to reorder your groups by a subtotal or summary. Choose this option from the pull-down menus by selecting Report Group Sort Expert. You can also click the Group Sort Expert button in the Expert Tools toolbar. Or, finally, if you select a summary or subtotal in a group footer and click the Sort button on the Expert Tools toolbar, the Group Sort Expert will appear instead of the Record Sort Order dialog box.

The Group Sort Expert presents a tab for every group you ve created on your report (provided that group has at least one summary created for it). Click the tab for the group that you want to reorder. When you first open the Group Sort Expert, the default setting for the first drop-down list is No Sort. This simply indicates that this group initially will not be a Top N group and that groups will appear in the order you chose when you created the groups. If none of the groups on your report have subtotal or summary fields in them, the Group Sort Expert won t be available, as it uses summary fields to sort the groups.

There are several choices available in the For This Group Sort drop-down list. Choose the desired option from the following choices:

All	Displays all groups on report, but uses one or more summary fields to determine the order of the groups. You have a choice of whether to show groups in ascending order (lowest to highest) or descending order (highest to lowest).
Top N	Displays only the top N (top 5, top 10) groups based on a summary field in order of highest to lowest.
Bottom N	Displays only the bottom N (bottom 5, bottom 10) based on summary field in order of lowest to highest.
Top Percentage	Displays the top N percent (top 10 percent, top 25 percent) of groups based on a summary field.
Bottom Percentage	Displays the bottom N percent (bottom 10 percent, bottom 25 percent) of groups based on a summary field.

When you click the down arrow of the second drop-down list, you see all the summaries you ve created for that group (only summaries you created with Insert Summary will be there ”you won t see any formulas or other fields). Choose the one that you want the Top N report to be based on. For example, if you want to see the top ten customers according to last year s sales, choose Sum of Customer.Last Year s Sales. If you leave the first drop-down list set to All, all groups will remain on the report, but they will be sorted in ascending or descending order, based on the radio buttons at the bottom of the dialog box. If you want the groups sorted by more than one summary (for example, first by sum of order amount and then by count of order ID), select additional summaries from the drop-down list and choose an ascending or descending sort for each.

Tip

Using the All option to sort groups in a different order can be a very innovative way to solve unique reporting problems. If you need to create a group based on one field but then have the groups appear in a different order, insert a summary field in the group footer based on the field you want to sort the groups by. Then, using the All option, choose the summary field you created.

If you are only interested in the Top N, Bottom N, Top Percentage, or Bottom Percentage groups, change the first drop-down list from All to the desired choice. The second drop-down list enables you to select one summary or subtotal to use to sort the groups. Choose the summary you want to use. Once you ve done that, type the value of N or the percentage in the appropriate text box. You also have a check box and text box that let you choose whether to include other groups not in the Top or Bottom N or percentage in the report. If you do include them, they will be lumped together in one other group with the name you type. If you want to not increment the N or percentage when group summaries are tied (this may change a top 10 to a top 12 if two of the groups are tied), check the Include Ties check box.

Using the Godiva and worst-performing-customer example earlier:

The sales manager s Godiva chocolate report would be Top N of Sum of Orders.Order Amount, where N is 10 and other groups are not included.
The sales associate s follow-up visit report would be a Bottom N of Sum of Orders.Order Amount, where N is 15 and other groups are not included.

The following Top N report shows who will be getting chocolates this year:

If you wish to change the report from Top N to Bottom N, change the value of N, or remove the Top/Bottom N sorting altogether and show all of your groups sorted in the order you originally chose, simply redisplay the Group Sort Expert and change the values. Remember that a group will be sorted in its original ascending or descending order if you set the first drop-down list to No Sort.

Caution

If you create a Top N report and don t include others, any grand totals you place in the report footer will still include all records on the report. If you want to include accurate grand totals in a Top N report, either include others or use a running total instead of grand totals (explained in Chapter 5).

Hierarchical Groups

Crystal Reports also features hierarchical groups, which can be helpful in certain reporting situations where two fields relate to each other hierarchically. A specific example would be an organizational relationship between employees and supervisors, where the database would contain a single record containing both an employee ID and a supervisor ID. Without hierarchical grouping, creating an organizational chart utilizing this data would be difficult. While you could group by supervisor ID to see all the employees that reported to that supervisor, you still wouldn t see any hierarchical levels, where that supervisor would appear underneath their supervisor, and so forth.

With Crystal Reports, you may simply choose hierarchical grouping options to indicate the relationship between the two fields in the same set of data, and to specify how much indentation you wish to show between the hierarchies. As an example, you may create a simple report using the Employee table from the XTREME sample database included with Crystal Reports. If you create a group based on Employee ID, you ll simply see one group for each employee in the table. To show the reporting relationship among the employees, choose Report Hierarchical Grouping Options. This displays the Hierarchical Options dialog box.

Choose the group (if there is more than one) for which you want to show the hierarchy. Then, click the Sort Data Hierarchically check box and choose the field that relates to the group field in the Parent ID Field drop-down list. Then, type the distance by which you wish to indent the lower-level hierarchies. Click OK.

The report will now create additional occurrences of groups to show the hierarchies created by the relationship of the two chosen fields. Here s an example of hierarchical reporting using the XTREME sample database:

Note	The lines in this illustration are not created automatically. You may use Crystal Reports line drawing tools to create this kind of effect. This example also uses a customized group name field, to show the employee name instead of the employee number in the group tree and the report.

Crystal Reports includes the ability to add subtotals or summaries in hierarchical groups. Once you ve created your group hierarchy with the features discussed previously, just use the same Insert Subtotal, Insert Summary, and Insert Grand total features covered earlier in the chapter to summarize data in hierarchical groups. The Summarize Across Hierarchy check box will appear.