●The formulas from the Record Selection Formula Editor are evaluated as the program reads records.

●The formulas from the Group Selection Formula Editor are evaluated as the program is printing records. At this point, the only records that are saved with the report are those that passed record selection criteria.

By using this functionality, you can set up record selection based exclusively on:

●indexed fields in a record selection formula, or

●non-indexed fields in a group selection formula.

Since the program runs record selection when it reads records and runs group selection when it prints records, the following events occur:

●The record selection formula on the indexed fields quickly returns a subset of data from your database. For this example, let’s say that it returns 5,000 records out of 100,000 and saves them in a buffer.

●The group selection formula performs record selection but only on the subset of data records (5,000) that are saved with the report.

You accomplish the same record selection but do it in a more efficient manner. With really big databases, this technique can save you significant processing time.

Related Topics

Introduction to Formulas, Page 291

Advanced Formulas, Page 315

Search for Functions and Operators and Variables in Seagate Crystal

Reports online Help.

HANDS-ON (Record and Group Selection)

How to set up record selection using the Select Expert

When you work with the Select Expert, you select the field to which you want to apply selection conditions and then you specify those conditions.

1Right-click the field on which you want to base record selection and choose SELECT EXPERT from the shortcut menu, or select the field and click the SELECT EXPERT button on the standard toolbar. The program opens the Select Expert, ready for you to set the conditions on the highlighted field.

238	Seagate Crystal Reports User’s Guide

NOTE: If you click the SELECT EXPERT button without first selecting a field in your report, the Choose Field dialog box appears. Select the field on which you want to base record selection (preferably an indexed field, see Indexed tables, Page 511) and click OK. The Select Expert appears. See

Selection performance tips, Page 236, and search for Choose Field dialog box in Seagate Crystal Reports online Help.

2 To base record selection on more than one field, click the New Tab and choose the next field from the Choose Field dialog box when it appears.

3 Use these drop-down lists to enter your selection criteria for the indicated field.

4 Click OK when finished.

A selection formula is generated based on your specifications, limiting the report to the records you indicated.

NOTE: To view or edit the selection formula, click the Show Formula button. The Select Expert expands to show the formula. To use the tools in the Record Selection Formula Editor to modify the formula, click the Formula Editor button. The formula will appear in the Record Selection

Formula Editor.

How to set up group selection using the Select Expert

When you work with the Select Expert, you select the summary field to which you want to apply selection conditions and then you specify those conditions.

1Right-click the summary field on which you want to base group selection and choose SELECT EXPERT from the shortcut menu, or select the field and click the SELECT EXPERT button on the standard toolbar. The program opens the Select Expert, ready for you to set the conditions on the highlighted group field.

Record and Group Selection

239

NOTE: If you click the SELECT EXPERT button without first selecting a summary field in your report, the Choose Field dialog box appears. Select the summary field on which you want to base group selection (preferably an indexed field, see Indexed tables, Page 511) and click OK. The Select Expert appears. See Selection performance tips, Page 236, and search for

Choose Field dialog box in Seagate Crystal Reports online Help.

NOTE: Summary fields identify the location of the summary value, the field that triggers a grouping when its value changes, the kind of summary, and the field being summarized. They look similar to this in the Choose Field dialog box:

Group Footer #1: Customer.Region

Sum of Last Year’s Sales

2 To base the group selection on more than one field, click the New Tab and choose the next field from the Choose Field dialog box when it appears.

3 Use these drop-down lists to enter your selection criteria for the indicated field.

If you have not already previewed the report or refreshed the data, there will not be any data saved with the report. Without the data, the program can not calculate group values; thus, no values appear when you click the arrow in the right drop-down list. In this situation, you will have to type in the values you want. If you want real values to work with, you will need to preview your report first. This will calculate the actual summary values available for you to work with.

4When you are finished, click OK in the Select Expert to return to the report.

How to create a record or group selection formula

NOTE: In order to create a selection formula, you need to have some understanding of the formula language and the use of the Formula

240	Seagate Crystal Reports User’s Guide

Editors. For a thorough discussion of these tools, see Introduction to

Formulas, Page 291, and Advanced Formulas, Page 315.

1Choose the EDIT SELECTION FORMULA command from the Report Menu. A submenu appears.

2 Choose Record to create a record selection formula (the Record Selection Formula Editor appears)...

...or choose Group to create a group selection formula (the Group Selection Formula Editor appears).

3Enter your selection formula in the Formula text box as you would any other formula. Since this is a selection formula, however, the formula must be Boolean (return either a True or False value).

4Click the SAVE AND CLOSE button when finished.

5If the Formula Editor reports errors, debug the formula as necessary and then click the SAVE AND CLOSE buttons again. See How to debug a formula, Page 336.

Now, when the program runs the report, it will include only those records or groups of records that you specified. See How to set record selection using parameter fields, Page 359.

How to use record/group selection templates

1Select the template you want to use. You can do this in one of two ways:

●Review the list in Record selection formula templates, Page 233, and write down the formula of interest.

●Find the Record/Group Selection Templates topic in Seagate Crystal Reports online Help and copy the formula of interest to the Clipboard.

2Choose the EDIT SELECTION FORMULA command from the Report Menu. A submenu appears.

Record and Group Selection

241

3 Choose Record to create a record selection formula (the Record Selection Formula Editor appears)...

...or choose Group to create a group selection formula (the Group Selection Formula Editor appears).

4In the Formula text box, type in the formula you wrote down in Step 1 or paste it from the Clipboard.

5Replace the values (fields, text, etc.) in the formula with the values you want. For example, if the example formula is:

{file.FIELD} > 99999

and you want to limit your report to records for which the value in the {orders detail.QUANTITY} field is greater than 25, simply replace the existing values with the values you want, so that your selection formula reads:

{orders detail.QUANTITY} > 25

6Click the SAVE AND CLOSE button when finished to exit the Selection Formula Editor, or click the REFRESH button on the standard toolbar to preview the results.

How to select the top or bottom N groups

NOTE: A report must contain a summary value in order to be able to perform a top N or bottom N selection.

When selecting top N or bottom N groups:

●choose whether to show the top or the bottom groups,

●specify how many groups to show, and

●tell the program how to deal with the records that are not in one of the selected groups.

1Create a report and summarize the data as desired. When you summarize the data, the program breaks the data into groups and summarizes each group.

●With top N grouping, you are instructing the program to display those groups that have the highest summary values (Top N).

242	Seagate Crystal Reports User’s Guide

●With bottom N grouping, you are instructing the program to display those groups that have the lowest summary values (Bottom N).

2Click the TOP N EXPERT button on the supplementary toolbar. The Top N/Sort Group Expert appears with a tab for your group.

If you have multiple group sections, the program will display a tab for each of the groups.

3 Choose Top N, Bottom N, or Sort All from this drop-down list.

4 Choose the summary on which you want to base your selection from this drop-down list.

The summary drop-down list on the right is for those cases in which you have multiple summaries within a single group section. For example, in an orders report, you might sum and average the orders for each customer and then display both the sum and the average in the same group section. In such a case, you would select the sum or the average from this drop-down list.

5In the where N is text box, enter the number of groups you want to display.

6All that is left is deciding what you want to do with all those records that do not fit into the selected groups.

●Toggle the include Others, with the name check box off if you want the other records excluded from the report.

●Toggle the check box on and assign a name to the group to lump all the other records into a single group.

7Click OK when finished.

8Now, when the program runs the report, it will include only those groups that you specified.

Record and Group Selection

243

244	Seagate Crystal Reports User’s Guide

12	Sorting, Grouping, and Totaling

What you will find in this chapter...

Sorting, Grouping, and Totaling Overview, Page 246

Creating custom groups, Page 250

HANDS-ON (Sorting, Grouping, and Totaling), Page 252

Sorting, Grouping, and Totaling

245

Sorting, Grouping, and Totaling Overview

Sorting

Sorting by record

Sorting, grouping, and totaling are the steps that turn disorganized data into useful information on a report. This overview is an introduction to the concepts behind sorting, grouping, and totaling. Refer to the many Hands-On tutorials for detailed instructions.

Sorting means putting values in some kind of order to help you find and/ or evaluate them. For example, information in a phone book is sorted because unsorted, it would have little usefulness. Trying to find someone’s phone number in an unsorted book would entail a random search through tens of thousands of names, a true needle-in-a-haystack experience. With sorting, however, you can find the number you need in a hurry. For example, if you are looking for the phone number of John J. Smith, the following levels of sorting might help you to find it.

●The data is sorted alphabetically by last name so you know the name Smith is in the S section of the book and you turn there immediately.

●When you find the Smiths, (and there are a lot of them) you see that they are sorted alphabetically by first name so that John Smith comes after Bob Smith. You turn to the John Smiths.

●Finally, when you find the John Smiths, you see that they are sorted by middle initial so the John J. Smiths come after the

John D. Smiths. You turn to John J. Smith, find his phone number, and place the call.

Thanks to sorting, you can find anybody’s listed phone number in seconds.

When you sort, the program asks you to select two things:

1.the field you want the sort to be based on (sort field), and

2.the sort direction.

SORT FIELD

A sort field is a field that determines the order in which data appears on your report. Seagate Crystal Reports sorts field data using rules specific to the Country you select in the International section of the Windows Control Panel (Windows 95 and Windows 98, Windows NT 3.51 or 4.0) or the Regional section (Windows 3.x).

Any field can be used as a sort field, including formula fields. A field's data type determines the method in which the data from that field is sorted:

246	Seagate Crystal Reports User’s Guide

●String fields are sorted in the following manner:

¾Single-character values are sorted so that blanks have the lowest value, then punctuation, then numbers, then uppercase letters, and finally lowercase letters.

¾Next two-character values are sorted, then three, etc., using the same rules. As a result:

¾“BOB” comes before “bob”,

¾“123” comes before “124”,

¾“ “ (blank) comes before “a”, and

¾“aa” comes before “aaa”.

●Currency fields are sorted in numeric order.

●Number values (120 or 5555) are sorted in numeric order.

●Date fields are sorted in chronological order.

●DateTime fields are sorted in chronological order by date and same-date values are then sorted by time.

●Time fields are sorted in chronological order.

●Boolean comparison fields are sorted so that False values (0) come first, then True values (1).

When a sort field is selected, the program sorts the values from that field.

SORT DIRECTION

Direction refers to the order in which the values are displayed, once sorted.

●Ascending

Ascending order means smallest to largest (1 to 9, A to Z, False to True). The program sorts the records in ascending order based on the values in the sort-and-group-by field you select, and then it begins a new group whenever the value changes (from Adams to Brown, for example).

●Descending

Descending order means largest to smallest (9 to 1, Z to A, True to False). The program sorts the records in descending order based on the values in the sort-and-group-by field you select, and then it begins a new group whenever the value changes.

When data is grouped, two more sort direction options are available:

●Original

Original order is the order the data was originally saved in the database. The program leaves the records in the order in which

Sorting, Grouping, and Totaling

247

Single field sorts

Multiple field sorts

Sorting and grouping

Results using different sorting and grouping operations

they appear in their originating database table, and begins a new group whenever the value changes in the sort-and-group-by field you select.

●Specified order

Specified order is a user-defined order. The program puts each record into the custom group you specify, and it leaves the records in each group in original order or it sorts them in ascending or descending order, depending on your instructions. See Creating custom groups, Page 250.

In single field sorts all the records used in the report are sorted based on the values in a single field. Sorting an inventory report by stock number or sorting a customer list by customer number are examples of single field sorts. See Results using different sorting and grouping operations, Page 248, and How to do a single field sort, Page 253.

In multiple field sorts, Seagate Crystal Reports first sorts the records based on the values in the first field selected, putting them in ascending or descending order as specified. When two or more records have the same field value in the first sort field, the program then sorts those records based on the value in the second sort field. For example, in a sort on last name and then first name (in ascending order), “Smith, Bob” would be returned before “Smith, John” no matter which way the fields are listed in the database. The program follows a similar process for three field sorts, four field sorts, and so on. See Results using different sorting and grouping operations, Page 248, and How to do a multiple field sort, Page 256.

Seagate Crystal Reports has the most powerful sorting and grouping capabilities of any Windows based report writer. When you select a grouping option, the program automatically sorts the data as part of the grouping operation. See Results using different sorting and grouping operations, Page 248.

The following chart shows how data would appear after being manipulated using different sorting and grouping operations.

1	2	3	4	5	6	7	8	9
CO	AZ	WA	AZ	WA	CO	WA	CA	WA
WA	CA	WA		WA		CA	CA	WA
CA	CA	WA	CA	WA	WA	CA	CA	WA
CA	CA	CO	CA			CA	CA	CA
CA	CA	CO	CA	CO	CA	WA	CA	CA
AZ	CA	CA	CA	CO	CA	WA	WA	CA

248	Seagate Crystal Reports User’s Guide

1	2	3	4	5	6	7	8	9
WA	CO	CA	CA		CA	CA	WA	CA
WA	CO	CA		CA		CA	WA	CA
CA	WA	CA	CO	CA	AZ
CA	WA	CA	CO	CA		CO	AZ	CO
CO	WA	AZ		CA	WA	AZ	CO	CO
			WA	CA	WA	CO	CO	AZ
			WA
			WA	AZ	CA
					CA
					CO

● Column 1

The data as it appears in the database table.

●Column 2

The data from Column 1 sorted in ascending order (A to Z, 1 to 9). There is no grouping.

●Column 3

The data from Column 1 sorted in descending order (Z to A, 9 to 1). There is no grouping.

●Column 4

The data grouped in ascending order. The program automatically sorts the data in ascending order and then inserts a group break whenever the value changes.

●Column 5

The data grouped in descending order. The program automatically sorts the data in descending order and then inserts a group break whenever the value changes.

●Column 6

The data grouped in original order. The data is not sorted before it is grouped. The program inserts a group break whenever the group value changes. Note that similar values may appear in more than one group (for example, CA and CO have more than one group).

●Column 7

The data grouped in specified order. This is one of thousands of possible custom groupings. In this example, the first group consists of Pacific states and the second group consists of Mountain states. The records in each group are sorted in original order.

Sorting, Grouping, and Totaling

249

●Column 8

The same specified order grouping as Column 7, but the records in each group are sorted in ascending order.

●Column 9

The same specified order grouping as Column 7, but the records in each group are sorted in descending order.

The way you sort and group data can have a major impact on the way it appears on a report.

Creating custom groups

Most of the time, data is sorted based on the values from a field in the 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. Then it 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, the report:

●May not contain the field you want to group on. For instance, your report may contain a City field and a State field but no Country field, although you need to group by country.

●May contain the field you want to group on, but you may not be happy with the grouping based on the values in that field. For example, you may 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.

●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 both the customized groups that will appear on your report and the records that each group will contain. Your only real limitation is that a record can be assigned to only one group.

250	Seagate Crystal Reports User’s Guide

Summarizing group values

To create a custom group using specified order grouping, you select in specified order as your sort 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,

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

●choose the CHANGE GROUP EXPERT command from the Report Menu.

One of the primary purposes for breaking data into groups is so that 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 the values in each group and then summarizes them. For example:

●For a customer list report, you might want to determine the number of customers in each state. The 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. The 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. The summary would sum or subtotal the order amounts for each sales representative group.

As you can see, grouped data can be summarized 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,

●which field will 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 plan to summarize, you can:

● sum the values in each group,

Sorting, Grouping, and Totaling

251