OSTEOSINTESIS

So what is a one-to-many report? Well, it’s really quite simple. It means the report is driven by two tables, a parent table and a child table. For each record in the parent table (the one in one-to-many) there may be one or more (many) records in the child table. A report on customer orders is the example I’m going to use to demonstrate how the One-to-Many Report Wizard works.

The Orders table contains one record for every order. Each record contains basic information about the order such as Order Number, Order Date, and Customer ID. The Order

Line Items table contains one record for each line on the order and contains fields such as Order Number, Line Number, Part Number, Quantity, and Unit Price.

Just as with the Single-Table Report Wizard, the One-to-Many Report Wizard uses a six-step process to generate the report. However, not all of the six-steps are the same. The first six-step in the Single-Table Report Wizard is to select the table. In the One-to-Many Report Wizard, this step is broken out into three different steps: Select the parent table, select the child table, and select the relation.

Step 1 is shown in Figure 24. Select which table is the parent table. Then use the arrow buttons to select the fields you want to appear on the report.

Figure 24. Step 1 allows you to select the parent table and which fields in that table you want displayed on the report.

Once you’ve selected the parent table, you’re ready to go on to Step 2 and select the child table (see Figure 25). Select the table you want used as the child and use the arrow buttons to select which fields you want displayed on the report.

Now that you’ve told the One-to-Many Report Wizard which table is the parent table and which table is the child table, you have to tell it how the two tables are related. This is done in Step 3 (see Figure 26). The parent table appears on the left side of the dialog and the child table appears on the right side of the dialog. The wizard makes an attempt to find a matching field in each table and displays that as the default. In this example, the wizard was able to determine that the order_id field exists in both tables so it uses that on both sides of the relation. If this wasn’t the situation, you could use the drop-down combo boxes on either side to choose the fields that link the two tables together.

Figure 25. Step 2 allows you to select the child table and which fields in that table you want displayed on the report.

Figure 26. Step 3 is where you tell the wizard how the parent and child tables are related.

Step 4 of the One-to-Many Report Wizard is where you select how you want the records sorted. For the most part, this is the same as Step 5 in the single-table wizard, so I won’t bother repeating it all here. Refer to “Step 5—Sort Records” earlier in this chapter for more information. The only difference to keep in mind is that with this dialog, in addition to the list of fields to choose from, the list also includes indexes of the parent table. The fields are listed first, followed by a horizontal line, and then followed by the indexes. Each index is identified with an asterisk (*) after the name to distinguish it from a field. To follow along with the Order example I’m creating, select the order_number* index from the list for sorting this report.

There’s a bug in the One-to-Many Report Wizard that sometimes causes the sort order to be set improperly. It seems to occur when the table has a descending index, which you select as the sort order. The wizard incorrectly sorts the data in ascending order.

Step 5 (see Figure 27) is used to choose the report layout. In essence, this is a combination of three different steps in the Single-Table Report Wizard: Step 2—Group Records, Step 3— – Choose Report Style, and Step 4—Define Report Layout. First, you select the style (see Figures 10-14 earlier in this chapter for examples). Next, you select the orientation. Lastly, you may optionally choose some summary options (see “Step 2—

Group Records” previously described in this chapter for more information about the summary options).

Figure 27. Step 5 of the One-to-Many Report Wizard is a combination of several steps of the Single-Table Report Wizard. It combines options for style, layout, and grouping.

The last step, Step 6—Finish, is the same as in the Single-Table Report Wizard. Use this step to preview the report, set a few miscellaneous options, and finally to save the report.

Figure 28 shows the first page of the 314-page Order report generated with the One-to-Many Report Wizard.

Figure 28. This Orders report was created with the One-to-Many Report Wizard.

There’s a bug in the One-to-Many Report Wizard that can create blank child records in the child table. It occurs when a parent record has no matching child records and at least one field from the child table is printed in the Detail band. The workaround for this bug involves changing the Print When logic and is described in more detail in Microsoft’s KB Article Q137413.

Conclusion

The Report Wizard supplied with Visual FoxPro is a great way to get started with creating reports. It introduces you to many aspects of reports while keeping the user interface very simple. And the Preview button on the final step really helps you take a peek at the report before you commit to saving it. This gives you the opportunity to go back and make changes over and over again until you’re pleased with the results.

The other important thing to remember about reports created with the wizard is that once the report is saved, it is a regular VFP report. There’s nothing different that distinguishes the report as one created by a wizard as opposed to one created with the Report Designer. Once you’ve saved it, you may use the native VFP Report Designer to edit and tweak the report as many times as you wish. Frequently, it may be a real time-saver to create the report with the wizard and then use the Report Designer to tweak it as necessary.

The bottom line—the report wizard is a great tool for creating simple reports or for creating a starting point for more complex reports.

Chapter 3 Learning the Basics

This chapter shows you how to get started by revealing several different ways to invoke the Visual FoxPro Report Designer. It also shows you how to set up the page and the Report Designer environment, and how to add and remove various bands.

Finally, this chapter shows you how to preview and run your report from within the VFP Report Designer.

Invoking the VFP Report Designer can be done a number of different ways. If you’re the type of person who prefers using menus, you can create a new report or modify an existing report through the main VFP Menu bar. If you’re the type of person who prefers to do things through the Command Window, you’re in luck too. You can create new reports and modify existing reports through the use of commands in the VFP Command Window.