Understanding Subreports

The first 11 chapters of this book introduced you to the design of individual reports using single aggregated datasets. Crystal Reports provides further flexibility and reporting capabilities through the use of additional reports embedded directly within an original main report. These embedded reports, referred to as Subreports, provide enhanced value extending your reporting solutions into an expanded domain that will be explored in this chapter.

This chapter covers the following topics:

• A description of Subreports
• The usefulness and value of Subreports
• Linked versus Unlinked Subreports
• In-Place versus On-Demand Subreports
• Passing data between the main report and Subreports

The next two sections provide you with

• An introduction to Subreports
• An idea of when you might use them
• A lesson on how to use Subreports

Crystal Reports provides the capability to embed multiple Crystal Reports within a single existing main report to allow for increased flexibility in report creation. Think of these Subreports as entire reports within reports, which can contain their own data sources, formatting, and record selections. The embedded Subreports can be created from existing Crystal Reports files or can be dynamically created at report design time using the insert Subreport functionality. When presenting a report that contains one or more Subreports to business users, the Subreports can be displayed either in-place, providing a seamless integration, or on-demand, minimizing the amount of required up-front report processing.

Common Subreport Usage

A few particular reporting problems are difficult to solve without the use of Subreports. Some of the most common problems and a specific example of each are listed here:

• The presentation of data from two (or more) completely unrelated data sources on a single report. Specific Example: On a Manufacturing Plant Efficiency report sourced from your internal SAP system, you want to display industry average information sourced from a completely different and unrelated industry or trade database.
• A report that needs to combine data from different tables with only derived (and not direct) database field links. Specific Example: On a Customer Profile report, you want to combine Order Information from your ERP (for example, SAP, Oracle, Baan) system with call-center information from your call-center application (for example, Remedy) and your CRM system (for example, Siebel, PeopleSoft), but the employee ID field is stored slightly different in each system. The Subreports enable the linking of the different employee IDs by allowing linking on formulas or derived fields.
• The presentation of the same data in two (or more) different ways in a single report. Specific Example: On a Sales Summary Report, senior management wants to present a high-level summary of sales by region but also wants to present a separate and personalized summary of sales by product for each salesperson who will be viewing the report.
• The inclusion of a summary field in the report that is unrelated to the established grouping in the main report. Specific Example: On an employee HR report, HR managers want to see employee salary information grouped by Business Unit, Division, and Department. Additionally, they want to view a count of the different departments that this employee worked for in the previous year. The main report groups employees by department (and by division and business unit), whereas the Subreport groups departments by employee to determine a department count.
• The inclusion of a reusable component like a standard reporting header or footer in numerous reports across an organization that can be dynamically updated for all reports in a single location. Specific Example: A firm wants to deploy all reports in its organization with a standard header including standard logos and titles. In addition to using the new Report Templates and Repository, Subreports can be used within all the reports as a header and provide a single location for updating the header across all the reports.

Data presented in Subreports is often related to the data presented in the associated main report, but it does not have to be. Subreport data can be a twist on the main report's information or sourced from a completely different database.

Adding Subreports to Your Reports

Adding a Subreport to your main report is as easy as adding any other Crystal Reports object. After selecting the Subreport option from the Insert menu, you are presented with the Insert Subreport dialog (see Figure 12.1).

Figure 12.1. The Insert Subreport dialog enables you to add a Subreport to your main report.

To explore one of the many challenges solved by using Subreports, let's solve the hypothetical reporting problem faced by the Chief Operating Officer (COO) of Maple Leaf Bikes Corporation. This COO wants a single report that highlights the recently acquired company's (Xtreme) top-selling products in one bar chart and additionally highlights the company's top selling sales reps in a corresponding pie chart. The two charts are sourced from the same sales information but have no direct relation or links to each other. To resolve this request, complete the following steps:

1. Create a New Report and point this report at the Xtreme Sample Database 10.

    

2. Select the Orders, Order Detail, and Product Tables and then select the Product Name and Order Amount Fields to Display on the report.

    

3. Group the report by Product Name and Add a Summary to the report that sums Order Amount for each Product Name group. Also limit the report to display only the top five groups based on the Summarized Field. (Reminder: You can use the Group Sort Expert under the Report menu option to accomplish this last task and remember to explicitly not include an Others group by selecting that check box.)

    

4. Add a bar chart in the Page Header to represent the top five selling products, and you should have a report similar to that depicted in Figure 12.2.

    

   Figure 12.2. Preliminary sample report to solve COO problem.

    
5. Make room for the COO's requested second visual by resizing the bar chart to only take up half of the page header's width.

    

6. Select the Insert Subreport option by either accessing that option from the Insert menu or clicking on the Insert Subreport icon. The Insert Subreport dialog in Figure 12.1 appears.

    

7. Select the Create a Subreport with the Report Wizard option by clicking on the associated radio button.

    

8. Enter a Name similar to Top Sales Reps and click on the Report Wizard button.

    

9. As you step through the familiar Report Wizard to create this Subreport, select the Xtreme Sample Database 10 and the Employee and Orders tables. From the list of available fields, select the First Name and Order Amount.

    

10. Group the Subreport on Employee First Name and create a Summary on the Sum of Order Amounts for each Employee Group. Limit the report to display the top five employees based on this sum, add a pie chart to this report, and click the Finish button.

     

11. Ensure that the On-Demand Subreport check box is unchecked, and then click OK on the Insert Subreport dialog. Drop the Subreport on the right side of the main report so that it does not overlap the existing bar chart. The details of On-Demand reports are described later in this chapter.

     

12. To clean up the final presentation of your main report and included Subreport, edit the Subreport by right-clicking on it and then hiding all the sections of the report except the report header a. As a reminder, hiding sections is accomplished by right-clicking on the name of the involved sections in the Design or Preview tab and selecting the Hide option. Lastly, delete the report header b section in the Subreport. Figure 12.3 shows the final result of this quick report. If your final result appears slightly different, review Chapter 8, "Visualizing Your Data with Charts and Maps," and revise the charts accordingly.

     

    Figure 12.3. Sample report with Subreport to solve COO problem.

     

NOTE

As mentioned in the previous sections, Subreports are Crystal Reports in their own right, and as such they have their own Design tab in the Crystal Reports Designer. To format the details of a Subreport, it is necessary to open the Design tab for that Subreport from within the Designer of the main report. This can be accomplished by right-clicking on a Subreport and selecting the Edit Subreport option. Figure 12.3 displays the tabs for both the sample's main report and the Subreport.

With that introduction to Subreports, you should begin to see some of the flexibility and power that they offer in solving difficult reporting (and even dashboard-related) problems. The next few sections explore this in more detail.