Linking with a Data Field

Linking subreports with a data field lets you filter the data in the subreport based upon the data that is in the main report. This is useful when you are printing data derived from tables that have a parent-child relationship.

First let’s look at when a subreport isn’t the best option. A simple report is useful when linking multiple tables together that have a one-to-one relationship or when two tables have a parent-child relationship. When tables have a one-to-one relationship, it is easy to match up the detail records and print them together. When two tables have a parent-child relationship, it is easy to group the records based on the parent data and print the associated child records within the detail section of each group.

Subreports become practical when there is a single parent table with more than one child table. If you tried to use a single report to print this data and you link the tables using the default inner join, then it is possible that not every record will print when there isn’t a matching primary key in both child tables. If you use an outer join to link the tables, then you could get some records printed multiple times depending upon how many times the primary key appears in each child table.

Using a subreport with multiple child tables corrects these problems. Within the main report print the records from only one of the child tables in the detail table. For the other child tables, create a subreport for each one and use the main report’s primary key to link them together. The subreports are placed in their own Details section so that their records are independent of the other sections and they print sequentially after the main report’s detail records.

The drawback to linking with a data field is that if you are using a PC database (e.g. MS Access), then the fields must be indexed. You must create an index for each linking field before running the report.

An example of linking with a data field is shown in Figure 12-1. This report shows the sales detail for each customer. Below the detail records is a list of all the credits that has been issued to this customer. Since there are two listings of detail records, the second list must be printed as a subreport. In this example, the subreport is added to the main report’s Group Footer section. It is linked via the Customer Id.

Figure 12-1. Linked subreport in the Details section.