Understanding Linked Versus Unlinked Subreports

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:

1. Open the previous sample report from this chapter and delete the previous Subreport containing the Top 5 Sales Rep pie chart. Now add the Product_Type table to this report through the Database Expert under the Database menu option. It is automatically and correctly linked to the Product table. Also, add another Group for Product Type Name on top of the existing Product Name (Hint: You can use the Group Expert under the Report menu.) Then hide the details section of this report.
    
2. Open the Insert Subreport dialog and create another new Subreport called Supplier Info using the provided Subreport Report Wizard. Connect this new Subreport to the Xtreme sample database, select the Supplier, Product, and Product Type tables (they will correctly smart-link), and add the Supplier Name, City, and Phone Number fields to the report. Finally, click on the Report Wizard Finish button, but do not exit the Insert Subreport dialog.
    
3. Click on the Link tab in the Insert Subreport dialog. Now, select the Product Type Name field from the Available Fields list as the Field to link on (it can be selected from the Product Type Table). This initiates the Product Type Name ID Link section at the bottom of the dialog. Use the default (and automatically generated) parameter '?Pm-Product_Type.Product Type Name' for the link on the Subreport.
    
4. Select the Select Data in Subreport Field Based On check box and choose the Product Type Name field from the Product_Type table in the drop-down box. Essentially, you have just specified that this supplier's Subreport be filtered on the Product Type Name that is passed in from the main report every time this Subreport is called. Click OK to add the Subreport and place it in the Product Type Name Group Header on the right side of the report.
    
5. To ensure that the desired results are provided and provided in a clean way, edit the Subreport to remove the default provided date and resize its Report Header Subreport section and hide the report footer b in the Subreport. You also need to specify that this Subreport should only return a distinct list of suppliers because the COO is not interested in a repetitive listthis can be done through the Report Options selection under the File menu. Click the Select Distinct Records check box.
    
6. Lastly, back in the main report, resize the bar chart graphic on the main report and you will have a new sample report for the COO resembling the report depicted in Figure 12.5.
    

   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.

NOTE

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.