The hypothetical COO scenario just explored highlights an example of an unlinked Subreport. In Crystal Reports terminology, this means that the parent, or main, report did not have any specific data connections (or links) to its related child report (the Subreport). Unlinked Subreports are completely independent from their main reports and do not rely on the main report for any data. Many reporting problems in which multiple views of the same or different data sources are required in a single presentation can be resolved with unlinked Subreports. If a requirement exists to share data between the parent/main report and its Subreport, linked reports provide the answer.
Contrary to unlinked Subreports, linked Subreports are bound (or linked) to the data in their associated main report. The links are defined in the Link tab of the Insert Subreport dialog shown in Figure 12.4.
Figure 12.4. Link tab of the Insert Subreport dialog.
The Link tab enables you to link report, database, or formula fields in the main report to fields in the Subreport and enables you to filter the Subreport based on the data passed in from the main report.
The Available Fields section of the Links dialog enables you to select the field from the main report to be linked on. More than one field can be selected for linking. After at least one field has been selected, a separate Field Links section appears at the bottom of the Links tab. For each linked field, a parameter in the involved Subreport must be selected to receive and hold that information. These parameters can be pre-existing parameters predefined in the Subreport, or they can be a parameter that is automatically created for each field you have selected to link. (These are automatically created in the Subreport with the prefix ?Pm-.)
Finally, for each linked field from the main report, a data filter can be created in the Subreport based on that parameter. This is accomplished by checking the Select Data in Subreport Based on Field check box and selecting the report field, database field, or formula field in the Subreport that you want to have filtered based on the linked parameter from the provided drop-down box. In effect, checking this box creates a selection filter in your Subreport that is based on the selected filter field and the selected parameter field.
Linking Subreports and Reports with Formulas
The capability to link Subreports and main reports with formulas gives you a flexible method of presenting data from different database tables that is not possible otherwise. The Crystal Reports Database Linking Expert only enables joining of fields from different tables and does not permit joining formulas to fields. By using formulas and Subreports, a derived formula can now be linked to another database field in a Subreport.
For example, this would be beneficial if a firm's Order Processing system (SAP, Oracle, Baan, and so on) stored a customer ID as a nine-digit number (999123888), but that same company's Customer Relationship Management (CRM) system (Siebel, Salesforce, PeopleSoft, and so on) stored the same customer ID as a nine-digit number prefixed with a regional code (ONT999123888). These fields could not be joined in the Crystal Reports Database Linking Expert, but they could be linked using a formula that extracts the nine-digit number from the CRM/Siebel Customer ID and links to the SAP Customer ID in a Subreport.
One last point of interest is that the new Business Views does now enable the linking of formula fields in the creation of Data Foundations. Chapter 18, "Crystal Reports Semantic LayerBusiness Views," covers this new functionality.
To explore a reporting solution with linked Subreports, solve the hypothetical reporting problem faced by the same COO of Maple Leaf Bikes Corporation. The COO now wants a single report that highlights the company's top-selling product types in one bar chart (similar to the previous example), enables drill-down into the actual products, and produces a list of suppliers for each product type to be available for reviewessentially, a Supplier's listing Subreport linked to the main report based on the Product Type Name. To accomplish this, follow these steps:
Figure 12.5. Sample report with Linked Suppliers Subreport.
The COO can now make an informed analysis on whether his firm has too much reliance on a small number of suppliers, and you have learned some of the benefits of a linked Subreport.
Unlike the initial sample report presented in this chapter where we placed the Top Sales Rep Subreport in the Report Header and it ran once for the entire main report, the Product Suppliers Subreport is run multiple timesin fact, once for every product. This is the case because the Subreport was placed in the Group Header of the main report, and it therefore is executed for each different group in the main report. This is important to note with respect to performance, specifically when your databases and reports become large.