Managing Groups
 |  |  |  |
 |
| |||||||||
 |
| ||
Revisiting the business question, we can next turn our attention to the requested changes in the grouping component of the report. Groups are created to help you view data in smaller segments and are created based on field values and calculations. Currently the group in our report splits the resort information into smaller bits of data based on the country in which the resort is located.
Business Question: We need the resort region, resort code, and mailing information for all the resorts sorted by resort name, with totals grouped by region and showing which ones are in North America.
We need to create an additional grouping of data showing the countries organized by region code.
Creating New Groups
When you were using the Report Creation Wizard, the way you added a group was through the Group Expert after choosing fields. The toolbars shown in Figure 2.9 give you a quick way to work with a whole series of experts, including the Group Expert. In Figure 2.9, the Group Expert icon is second from the left on the Expert toolbar. You can click this toolbar icon or choose Report > Group Expert to open the Group Expert.
Figure 2.9. Grouping toolbar icons
| Tip | Turn on toolbars by choosing View > Toolbars and select which toolbars to display and whether to show ToolTips text. |
The Group Expert
The Group Expert is useful for creating new groups and visualizing how groups are hierarchically related to one another. In Figure 2.10, the Region field has been added to the grouping using the techniques you learned in Chapter 1.
Figure 2.10. Group Expert
In the hierarchy shown, the Country field appears in the list before the Region field. This is not what we want. Adding a new field using the Group Expert simply copies an item from the list on the left to the list on the right, and you control the hierarchy grouping after the field exists in the list on the right. At this point, you can use the upward- and downward-pointing triangles above the Group By list on the right, visible in Figure 2.10, to reorder the fields so that Country more correctly comes below Region. How did we decide on this order?
The questions to ask yourself in order to create correct group hierarchies have to do with one group being a part of or a subset of another. For instance, in our case, the questions were “Can a country be part of a region?” or “Can a region be part of a country?” The correct answer is that countries are part of regions; therefore, regions are at the top of the hierarchy and countries are listed below.
When Crystal adds a new group, it adds it to the design area based on the hierarchy you specified in the Group Expert. You’ll recall that all groups are automatically left-aligned, and each group has its own header section and footer section. This is where manually indenting will help you out again. In Figure 2.11, Group #2 was indented manually under Group #1. Ideally, you should indent all lower groups, in this case, Country, as well as the data in the Details section for the groups. The group names are given incrementing numbers based on their hierarchical position, so we now have Group Header #1, Group Header #2, Group Footer #1, and Group Footer #2.
Figure 2.11. Group alignment
| Tip | You can rearrange the hierarchy of the groups directly in Design mode using a drag-and-drop technique; drag the gray section area for a group up or down to reposition it and then drop it. The icon changes to a hand during the drag-and-drop process. |
Insert Group Icon
In addition to the Group Expert, the toolbar bar has an icon specifically for adding a new group, as shown previously in Figure 2.9. Using this icon is the equivalent of choosing Insert > Group and results in the dialog shown in Figure 2.12.
In the first drop-down list box, you choose the field on which the new group will be based. You can choose from any of the fields currently in the report and any of the fields in the data source but not on the report.
This dialog window also allows you to choose a group sort option from the second drop-down list. The default sort option is ascending order.
Figure 2.12. Inserting a group
| Tip | This dialog window can be opened from the Group Expert as well by clicking the Options button, visible in Figure 2.10. The only difference on the dialog is that the title bar label is Change Group Options. |
The Options tab visible behind the Common tab in Figure 2.12 allows you to set the following options:
-
Customize a group name field by typing your own or using data from another field in the database.
-
Keep the group together to print on the same page in case it would have started midway through one page and finished on a second page.
-
Repeat (display) the group header on every page in your report.
When you add a group with the Group Expert or the Insert Group technique and base the group on a field not already in the report, the group is added to the report but the field that you’re grouping on is not added to the Details section. This is true whether you group on a report field or choose a field that’s in the database but not already on the report. The benefit of this option is it that it doesn’t clutter up the Details section.
If, on the other hand, you add a group based on a field that’s already on the report, the field is not removed from the Details section. Rather, the data is now represented in both the Details section and the grouping area. If you want to remove the field from the Details section, just select the field and press the Delete key as discussed earlier. For our sample report, the data for the Region field and the Country field is represented in the group sections, so these fields are unnecessary in the Details section.
Group Order
There are four sort orders for groups: ascending (the default), descending, specified order, and original order:
Ascending Order Data is sorted lowest to highest; alphabetic characters sort from A ascending to Z, numbers sort from 0 ascending to 10 and higher, and Boolean values sort False before True (which applies to 0 before 1 as well).
Descending Order Data is sorted highest to lowest; alphabetic characters sort from Z descending to A, numbers sort from 10 or higher descending to 0, and Boolean values sort True before False.
Specified Order Data is sorted based on a customized criterion that does not exist in the raw data. This is also known as creating a custom group.
Original Order Data is sorted in the order in which it was added to the data source, with the oldest data appearing at the top and the most recently added data appearing at the bottom.
The data in a field, and therefore the grouping component, is subject to rules unique to its data type. Date and time values sort in chronological order, while text fields sort non-alphabetic characters before alphabetic characters and uppercase letters before lowercase letters. Sort order does not apply to large text fields like memo, binary large object (BLOB), and character large object (CLOB).
Business Question: We need the resort region, resort code, and mailing information for all the resorts sorted by resort name, with totals grouped by region and showing which ones are in North America.
Hmmmm…. North America is a continent, and that is not a field that is being captured directly in the database. However, we can derive a rule that defines North America based on other stored data; namely, if the resort’s country is the United States or Canada, then the continent is North America. At any time, the custom North America group could be expanded to include countries like Mexico, the countries of Central America, Greenland, the island countries, and the dependencies of the Caribbean if the data in the database referenced these places. The point is that your custom group definition may change over time, and that is perfectly okay. This ability to state a rule that uses existing fields is the essence of specified order custom groupings.
Okay, let’s take this part step at a time since it’s not very intuitive and see how to turn our business rule into Crystal Reports functionality. First, you need to do a specified order grouping on the Country field since it’s the Country field that will determine whether the data belongs in the custom group. When you select this option on the Common tab on the Insert Group dialog, the Specified Order tab becomes visible, as shown in Figure 2.13.
Figure 2.13. Insert Group dialog
On the Specified Order tab, you begin your new business rule by clicking a button labeled New. This opens the Define Named Group dialog shown in Figure 2.14. The first thing you do here is type the name of the group as you want it to be known; it can be anything you like but should add meaning to the process and generally describe the business rule. The phrase “North America” describes the group we’re building, so that’s what we’ve used.
Figure 2.14. Define Named Group dialog
There are two tabs below the group name; one represents the database field you are using to create the custom group and the other lets you add additional fields. Referring to the business question we’re answering, the field of interest here is Country since it is the country that will determine whether the value is in North America. On this tab, you specify that the United States and Canada, for example, are countries in North America.
Using the valid values typing area, you can type valid values or use the drop-down list to pull values from the database. The values you choose
then appear in the list below as the set of all valid values. A comparison operator is chosen to compare the database field to the list of valid values. The available comparison operators are described in Table 2.2.
| Operation | Description |
|---|---|
| is any value | Allows any value (basically has no effect, which is the default) |
| is equal to | Checks to see if field value is exactly equal to a value you provide |
| is not equal to | Checks to see if field value is not equal to a value you provide |
| is one of | Checks to see if field value is one of several values you provide |
| is not one of | Checks to see if field value is not one of several values you provide |
| is less than | Checks to see if field value is less than a value you specify |
| is less than or equal to | Checks to see if field value is less or equal to a value you specify |
| is greater than | Checks to see if field value is greater than a value you specify |
| is greater than or equal to | Checks to see if field value is greater than or equal to a value you specify |
| is between | Checks to see if field value is between two other values; very useful for dates |
| starts with | Checks to see if field value starts with a particular letter |
| does not start with | Checks to see if field value does not starts with a particular letter |
| is like | Checks to see if field value is similar to a value you specify |
| is not like | Checks to see if field value is not similar to a value you specify |
The data stored in the Country field in the database does not contain the full names United States or Canada. Instead, the Country value for these two countries is stored as US and CN, respectively, so it is US and CN that need to appear on the valid value list. How would you know how the data was stored? You can click the Browse Data button to peek inside the field identified on the tab to see the actual stored data.
| Warning | If you were to type United States or Canada in the valid value list, there would be no data in the database that matched your business rule, so you should click the Browse Data button to be sure you’re right. |
Now the moment is here to create a custom group and use values from the database to define whether a record will fit into the group. You need to consider what will happen on your report to the values that don’t fit your criteria. This is exactly what the Others tab, shown in Figure 2.15, is intended to do.
Figure 2.15. Data that doesn’t meet the custom group criteria
On this tab, you can specify that any data outside the value list be discarded and not shown in the report. Another option is to group all the values that don’t fit into the custom group and show them under a general title; Others is the default, but names like Miscellaneous and Extra make sense too. The label is up to you, so you can type something meaningful to you. For our example, if the custom group label is North America, values that don’t fit into the group could be labeled Outside North America. This tab also provides the option to simply represent the data in whatever group it was already in and display it after the custom group.
| Tip | The Others tab is not visible unless you create a Specified Order custom group since it does not apply to any other sort order. |
Deleting Groups
To delete a group, you need to delete the group header and group footer. To do so, right-click the gray section for either the group header or group footer to display the context menu. Both sections contain the Delete Group menu option, as shown in Figure 2.16.
Figure 2.16. Group header context menu
| Note | Notice also in this context menu the Change Group option. Selecting this option opens a window similar to the one shown previously in Figure 2.10 titled Change Group Options. |
It is not sufficient to select and delete the group field from the report in either the group section itself or the totaling field, if one exists, in the group footer. This will delete the data from the report but will not stop the grouping from taking place. To delete the group, you must use the section menu shown in Figure 2.16.
| ||
 | |
| Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement | |
| Conello © 2000-2003 Feedback | |
EAN: 2147483647
Pages: 217