Creating custom groups

Most of the time you sort and group your data based on the values in a field in your report. For example, if you have a customer list and you want to sort and group by state, the program first sorts the list by state and then breaks the list into state groups whenever the value in the State field changes.

Sometimes, however, you may not want to group based on the values found in one of the fields on your report. For example:

●Your report may not contain the field you want to group on. For example, your report contains a City field and a State field but no Country field, but you want to group by country.

●Your report may contain the field you want to group on, but you are not happy with the grouping based on the values in that field. For example, you have a Color field on your report that includes specific color names (Logan Green, Sky Blue, Emerald Green, Navy Blue, etc.) but you want all shades of each color to appear as a single group (Greens, Blues, Reds, etc.). In this case you can build custom groups and manually assign the records you want to be in each group.

●Your report may contain the field you want to group on, but you want to select specific values or ranges of values for each group. For example, you might want one group to contain records where gross sales are less than a certain value, a second group where gross sales are greater than a certain value, and a final group where gross sales fall between two values. In this case, you can build your groups using the same range of selection facilities that are available to you for building record selection queries.

Specified order grouping provides a solution to these custom sorting and grouping challenges. Specified order grouping enables you to create the customized groups you want to appear on your report and the records that each group contains. Your only real limitation is that a record can be assigned to only one group.

Sorting, Grouping, and Totalling

277

Summarizing

group values

To create a custom grouping using specified order grouping, you select in specified order as your sort option (whenever the program provides you with that option). The program gives you the in specified order option whenever:

●you create groups using the GROUP, SUBTOTAL, or SUMMARY command on the Insert menu,

●whenever you create groups while building a report using one of the Report Creation Experts, or

●whenever you choose the CHANGE GROUP EXPERT command from the Report menu.

One of the primary reasons you might break your data into groups is so you can run some calculations on each group of records instead of on all the records in the report. When you do this, the program evaluates all of the values in each group and then summarizes them. For example:

●For a customer list, you might want to determine the number of customers in each state. For this report, your summary would consist of counting the distinct customers in each state group.

●For an order report, you might want to determine the average order placed each month. For this report, your summary would calculate the size of the average order for each month group.

●For a sales report, you might want to determine the total sales per sales representative. For this report, your summary would sum or subtotal the order amounts for each sales representative group.

As you can see, you can summarize your grouped data in a variety of ways to make useful reports.

When the program summarizes data it sorts the data, breaks it into groups, and then summarizes the values in each group. It does this all automatically; all you have to do is specify:

●the field you want summarized,

●the type of summary operation to be performed on the field,

278	Seagate Crystal Reports User’s Guide

Sorting

summarized group values

●the field that is going to trigger a new group whenever its value changes, and

●the sort order.

The program handles all the details.

The program includes a number of summarizing options. Depending on the data type of the field you are planning to summarize, you can:

●sum the values in each group,

●count all the values or only those values that are distinct from one another,

●determine the maximum, minimum, or average value, and

●calculate two kinds of standard deviations and variances.

You can set up all of these summaries by clicking the INSERT SUMMARY button on the standard toolbar or by choosing the SUBTOTAL command from the Insert menu. Search for Subtotal command and Summary functions in Seagate Crystal Reports online Help.

You can sort summarized group values in either ascending or descending order. In an orders report, for example, if you subtotal orders by state, you could have:

●the group with the lowest subtotal first, then the next lowest, and so forth (ascending), or

●the group with the highest subtotal first, then the next highest, and so forth (descending).

You can sort your report based on group values using the TOPN/ SORT GROUP EXPERT command on the Report menu. For more information on TopN/BottomN sorting and grouping, see How to select the top or bottom N groups, Page 267.

NOTE: To sort groups that are not summarized, choose the CHANGE GROUP EXPERT command from the Report menu.

Sorting, Grouping, and Totalling

279