Tutorial 3-2. Grouping by a Specified Order

Crystal Reports has an excellent sample report that demonstrates how to create a grouping report using the specified order option. The report Group By Intervals.rpt is a sales report that shows total sales from the prior year for each customer. Each customer is broken down into three categories based on their sales volume: Less than $10,000, $10,000 to $25,000, and Greater than $25,000. Within each category the records are sorted by the Customer Name field. We’re going to see how this report works by creating the same report from scratch.

1. On the Start Page click Standard Report Wizard to create a new report.
2. In the Data dialog box, select the Xtreme.mdb database as the data source. Do this by clicking on Create New Connection > Access/Excel > Make New Connection.
3. For the Database Name property, navigate to the Xtreme.mdb database that is installed in the Crystal Reports sample folders. Click the Finish button once you’ve selected it.
4. Open the Tables category and double-click the Customer table to add it to the Selected Tables list on the right. Click the Next button.
5. In the Available Fields list, select the fields Customer Name, Region, Country, Postal Code, and Last Year’s Sales. Click the Next button to go to the Grouping dialog box.
6. In the Grouping dialog box, select Last Year’s Sales as the grouping field. The Grouping dialog box doesn’t let you set any of the advanced options, so we will have to wait till later before setting the specified order options.
7. Click the Next button to go to the Summaries dialog box. Crystal Reports saw that the field Last Year’s Sales was numeric and automatically created a summary field for it. This isn’t necessary for this example so you can delete it.
8. Click the Finish button to skip the rest of the Wizard’s dialog boxes and go to the report designer. Your report preview should look similar to the following.

The report is grouped by last year’s sales and consequently there is a group for almost every record in the database. This isn’t very practical, so let’s clean it up by consolidating the values into three categories.

9. Open the Group Expert dialog box by clicking on the Group Expert button or selecting the menu items Report > Group Expert. The field Last Year’s Sales is listed in the grouping fields on the right.
10. Click the Options button to open the Change Group Options dialog box. On the sorting property (the second dropdown box), select the In Specified Order option. This displays the Specified Order tab.
11. Click the New button to create the first group. This opens the Define Named Group dialog box.
12. Enter a name of Under $10,000. In the criteria dropdown box select Is Less Than. For the Value property enter 10,000. Click the OK button to save it.
13. Under $10,000 is now listed on the Specified Order tab. Click the New button to enter the second group.
14. Enter a name of $10,000 to $25,000. In the criteria dropdown box select Is Between. For the two Value properties enter 10,000 and 25,000. Click the OK button to save it.
15. For the final group, click the New button again and enter the name Over $25,000. For the criteria, select Is Greater Than and enter a value of 25,000. Click the OK button to save it.
16. At this point, you’ve created the three groups that will categorize the records. In some reports you would now go to the Others tab to tell Crystal Reports what to do with the records that didn’t fall into one of the groups. But in this example, all the records will be included in one of the three groups. Click the OK button to save your changes on the Specified Order tab.
17. As another option, you might want to list the customers with the most sales first. If so, you can re-order the groups on the Specified Order tab by selecting the group and clicking on the arrow keys to move it up or down in the list. For this example, we’ll just leave it as is.

As we learned earlier in the chapter, specifying the order of the groups doesn’t effect how the records within the group are sorted. We want the detail records to be sorted by Customer Name.

18. Open the Records Sort Expert dialog box by selecting the menu items Report > Record Sort Expert.
19. Double click the Customer Name field to add it to the Sort Fields list. Click the OK button to save it.
20. You’ve made all the changes necessary. Save it as Specified Order Tutorial.rpt and then preview it. If you page through it, you’ll see that each group is in the order that you added them to the Specified Order tab and that the detail records are sorted by customer name. Your report should look similar to the following.