Tutorial 3-4. Creating Summary Fields

This tutorial walks you through the process of creating summary fields that are displayed in the Group Footer. You will build a sales report that lists the number of purchases they had, their total sales and their average sales.

1. Go to the Start Page and click on Standard Report Wizard.
2. When asked for the data source, choose the Xtreme.mdb database like you did in the previous tutorials.
3. Select the Customer table and Orders table and click the Next button. When the Link dialog box appears, it shows that the two tables are related by the Customer ID. This is what you want so click the Next button.
4. From the Customer table, choose the fields Customer ID and Customer Name. From the Orders table, choose the field Order Amount. Click the Next button.
5. For the grouping field, choose the Customer ID field and click the Next button.
6. The Summaries dialog box appears and you see that it has already pre-selected two summary fields: Customer ID and Order Amount. It makes sense to summarize the Order Amount, but not the Customer ID. Unfortunately, Crystal Reports obviously has no idea what the meaning of each field is so it automatically creates a summary for every numeric field being printed. You have to remove the fields you don’t want. Click on the Customer Id summary and click on the left arrow button to remove it from the list.
7. Click the Finish button and the report is generated automatically. Your report should look similar to the one below.

This is a decent first attempt at a report, but it needs some obvious corrections before we go any further. The first problem is that the sub-total for the Order Amount doesn’t line up with the Order Amount column. It is too far to the left. The second problem is that the Company Number and Company Name are repeated on each detail line when it really only needs to be shown in the group header. Let’s make those corrections now.

8. Click on the Design tab so that you can modify the report.
9. Look in the Group Footer and find the summary field for Order Amount. Move it to the right so that it is aligned with the Order Amount column (this would be a good time to practice using the Align feature). Also make the field width smaller because it doesn’t need to be that big.
10. Delete the Customer Number field from the Details section so it doesn’t get repeated.
11. Move the Customer Name field by clicking on it and dragging it from the Details section, up to the Group Header section.
12. Since the Customer Name field is now in the Group Header, it needs to match the formatting of the Customer Number field already in the group header. Right-click on the Customer Number field and select the Format Painter menu item. Move the cursor onto the Customer Name field and the cursor will become a paint brush. Click on the field and it gets the same formatting as the Customer Number field. Now they both match.
13. Preview the report and it should look much nicer.

Next we’ll add the summary fields to show how many purchases each customer made as well as their average sales amount.

14. Click on the Design tab so you can make changes to the report.
15. On the menu, select Insert > Summary (or click the Insert Summary button). The Insert Summary dialog box appears so you can choose the field to summarize.
16. Select the field Order Amount. For the type of summary, choose Count. For the summary location, choose Group #1. This creates a summary field that counts how many times the Order Amount field appears in the group. Consequently showing you how many purchases were made.

17. Click the OK button and the summary field is automatically added to the Group Footer section. Move it next to the existing Order Amount summary field.
18. Add another summary field by selecting the menu items Insert | Summary.
19. Select the field Order Amount. For the type of summary, choose Average. For the summary location, choose Group #1. This creates a summary field that calculates the average order amount for all the orders placed by each customer.
20. Click the OK button and the summary field is automatically added to the Group Footer section. Move it next to the previous summary field.
21. The report is now finished and it includes three summary fields: the total sales (created by the report wizard), the number of sales and the average sales (both created by you). It is shown in the next figure.

Of course, if you were preparing this report for your company, you would want to spruce it up by creating column headers for the new summary fields and changing the formatting to make it fit the corporate image.

If you want to customize the formatting of common fields based upon which group is currently being printed, you can use the GroupNumber function with conditional formatting to do so. The GroupNumber function returns the current group number being printed (1, 2, 3, etc.). Conditional formatting is covered later in the book.