When displaying the report, all the Sales Order Detail records should be displayed for the current Sales Order Number. To accomplish this you can create a group based on the SalesOrderNumber field and then add a page break after the group footer. When you add a group to a Crystal Report you automatically get a group header and footer section. You can dynamically format these sections to page break, suppress, reset the page number, and a variety of other features. For our report we want to break the page after the group footer so the next invoice starts on the next page and we want to reset the page number so you can print out invoices in bulk but mail them individually.

1.    Click back the Main Report view. Right click any white space on the report and select InsertàGroup… from the pop-up menu.

2.    The Insert Group dialog box should appear. Select the SalesOrderHeader.SalesOrderNumber field from the drop down list. Click OK.

3.    You will notice that two new sections were added to the report: Group Header #1 and Group Footer #1. By default, Crystal adds the special field called Group #1 Name to the Group Header #1 section. This will display the value for whatever field you are grouping on. For this report you do not need the Group Name since it is the Sales Order Number and it is printed in the Page Header. You can remove this field by clicking on the field and pressing the delete key.

4.    We do not need to show Group Header #1 so you can suppress this section by right clicking on the section header in the report and selecting Suppress (No Drill-Down) from the pop-up menu.

5.    The next step is to tell Crystal to break the page after the page footer and reset the page number. Right click the Group Footer #1 section heading and select Section Expert from the pop-up menu.

6.    Check the boxes next to "New Page After," "Reset Page Number After" and then click OK.

Figure 15

7.    The next step is to add the line items for the invoice. The details section will repeat for each record in the SalesOrderDetail table. Expand the Product table in the Field Explorer. Drag the ProductNumber field to the Details section. Notice that when you add a field to the Details section, Crystal Reports automatically adds a column header field in the Page Header Section.  The column header is simply a text object that you can edit to a friendlier name. Change the column header to "Product Number."

8.    Drag the Product.Name field to the Details section. Change the width of the field to 4800.

9.    Drag the SalesOrderDetail.OrderQty field to the Details section. Change the column header to "Qty."

10. Drag the SalesOrderDetail.UnitPrice field to the Details section. Change the column header to "Unit Price."

11. Drag the SalesOderDetail.UnitPriceDiscount field to the Details section. Change the width of the field to 600. Change the column header to "Disc."

12. Drag the SalesOrderDetail.LineTotal field to the Details section. Change the column header to "Line Total."

13. The next step is to add a box so the column headers and details are surrounded and stand out in the report. Right click on the report and select InsertàBox. Drag the box around the column headers and down to the Group Footer section. 

Figure 16

14. Preview the report and you should see the line items appear.

The last step is to create the totals in the Group Footer section. The sum of the LineTotal field equates to the sub total for the invoice. There is a field on the SalesOrderHeader record called subtotal, but for some reason it does not equal the sum of the details. To get around this we will create our own subtotal in the report. We will then add in the Freight and Tax to calculate the invoice total.

15. Right click on the LineTotal field in the details section. Select InsertàSummary… from the pop-up menu.

16. The field to summarize should have defaulted to SalesOrderDetail.LineTotal and the calculation should be set to Sum. Change the Summary Location drop down to Group #1: SalesOrderHeader.SalesOrderNumber - A. Click OK.

Figure 17

17. This will automatically add the summary field to the footer section below the LineTotal field.  You may need to increase the height of the group footer section so it appears correctly.

18. Right click on the field and select Format Object. Click the Font tab and change the style to Regular. Click the Number tab and check the box next to Display Currency Symbol. Click OK.

19. Click on the summary field and the line total field while holding the Ctrl key. Make the fields the same size and align the right sides.

20. Right click to the left of the summary field and select InsertàText Object from the pop-up menu. Change the text to "Subtotal:" and right align the field. This is the label for the summary field.

21. Now add the Tax field. Expand the Database Fields in the Field Explorer. Expand the SalesOrderHeader table. Drag the Tax field to the group footer just below the LineTotal summary field.

22. Right click to the left of the Tax field and select InsertàText Object from the pop-up menu.  Change the text to "Tax:" and right align the field.

23. Now drag the SalesOrderHeader.Freight field below the Tax field.

24. Right click to the left of the Freight field and select InsertàText Object from the pop-up menu.  Change the text to "Freight:" and right align the field.

25. Click on the summary field, tax field, and freight field while holding Ctrl. Make them all the same size and align their right side.

26. Now you have to create a formula for the invoice total. Right click the formula fields in the Field Explorer. Select New… from the pop-up menu and enter InvoiceTotal for the name. Click Use Editor.

27. Enter the following formula. This adds the subtotal, tax, and freight.

Listing 4

Sum ({SalesOrderDetail.LineTotal}, {SalesOrderHeader.SalesOrderNumber}) + 
  {SalesOrderHeader.TaxAmt} + {SalesOrderHeader.Freight}

28.  Click the Save and close button.

29. Drag the InvoiceTotal formula field to the group footer below the Freight field. Make this field the same size as the sub total field and align their right sides.

30. Since this is a total field we will add a single line above the field and a double line below the field. To do this, right click on the InvoiceTotal field in the report and select Format Object from the pop-up menu.

31. Click the Border tab and set the Top line style to Single and set the Bottom line style to Double. Click OK.

32. Now insert a label to the left of the Invoice Total. Right click to the left of the Invoice Total field and select InsertàText Object. Change the text to "Invoice Total:" and right align the field.

The Report Footer should look like the following:

Figure 18

The Report Header and Report Footer sections are not used in this report so you can suppress them. To suppress these sections, right click on the section header and select Suppress (No Drill-Down) from the pop-up menu.