Chapter 13: Using Subreports

Overview

As you become more sophisticated in your report designing abilities , you will find at times that it s difficult, if not impossible , to create certain kinds of reports . For example, you might want to create one of the following:

• A single-page Company Condition report that contains an accounts receivable summary in the upper left, an accounts payable summary in the upper right, a payroll expense summary in the lower left, and a sales summary in the lower right. At the bottom of the report, you d like some grand totals for each of the summary reports.

• A listing of orders by customer for the month that also has a summary of the top five products sold during the month, regardless of customer.

• A sales report grouped by state, with a list of all credit granted in the same state in the group footer.

• A report that combines tables in such a way that duplicate records from certain tables repeat when matching to other tables (often referred to as a many-to-many relationship). You wish to properly subtotal records from the affected tables.

• A report that contains a report title, logo, and company information from a separate Company Information table in the database that doesn t contain any field that can be linked to other fields in other tables.

In each of these cases, you can t create the report using traditional Crystal Reports methods . The first three instances are prohibitive because a report, by nature, can use only a single result set, or a single group of fields returned all at once, from the database. The fourth instance exhibits a common problem that occurs when multiple tables contain more than one matching record. And the fifth instance (a fairly common situation), exhibits the problem encountered when there are no common fields that can be linked between the two tables.

Crystal Reports provides an innovative way to deal with these types of reporting challenges. Subreports allow you to solve these problems by, in essence, placing one report inside another report. A subreport is simply another report that appears inside the original main report as an object. Even though both reports have separate layouts and separate Design tabs, they appear together in the same place. The main report is created initially, after which one or more subreports are added to the main report.

Each subreport is designed separately, based on its own database tables and fields. You can preview each subreport in its own Preview tab, format individual objects in each subreport, and create unique selection criteria for the subreport. However, when you return to the main report s Preview tab or print the main report, the subreports will be processed and printed at the same time, appearing inside the main report.

There are two main types of subreports:

• Unlinked subreports Have no tie-in to the main report at all ”they exist completely on their own and don t typically communicate with the main report. The Company Condition report mentioned previously falls into this category. Each of the unlinked subreports stands on its own and won t change based on any controlling field in the main report.

• Linked subreports Controlled by the main report. The subreport will follow the main report, returning only a certain set of records based on the main report s controlling field or fields. The previously mentioned subreport containing sales by state followed by matching credit, is an example of a linked subreport. When the state group changes in the main report, the subreport will return only records for that particular state.

You can also choose when subreports are processed by the main report. In-place subreports process at the same time as the main report and return their results at the same time. On-demand subreports appear in the Preview tab only with a placeholder and don t process until a viewer double-clicks them. This improves the performance of the main report, because all the subreports don t have to be processed at the same time as the main report.