Creating and Using Subreports

Chapter 6 - Summarizing Information
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

A subreport is a report that can be embedded within another report. This design element is used to separate and organize information, promote reusability in data reporting, and in some cases, enhance the performance of a report. The full complement of insert options are available for subreports including summary objects, groups, lines, boxes, charts, etc. Subreports can contain any element that a main report can contain and support the same drill-down capabilities and suppressing options as their main report counterparts.

Once a subreport is embedded, the original report is referred to as the main report, the container report, or the primary report. Once saved, the subreport becomes a part of the main report and is stored within the same RPT file. A subreport can be directly created within a main report or it can be created from an existing separate RPT file, which is then embedded in a main report. Multiple subreports can be inserted into a single main report, and depending on which sections they are placed into, the subreports may display once per report, once per page, once per group, or once per detail record.

Choosing Insert > Subreport opens the dialog in Figure 6.28, and you can then choose to retrieve an existing report by clicking the Browse button or create an internal subreport and give it an alias name.

If you create an internal subreport with an alias name, clicking the Report Wizard button (shown grayed out in Figure 6.28) opens the Standard Report Creation Wizard to help you choose a data source and fields and perform grouping and record selection. The fact that the Standard Report Creation Wizard is used here should further convince you that a subreport is nothing more than a report within a report!

Figure 6.28. Inserting a subreport

Note 

Refer to Chapter 1 for more information on the Standard Report Creation Wizard.

Types of Subreports

The information contained in a subreport may or may not be related or relevant to the information in its main report. When there is a relationship between the two, Crystal calls this a linked subreport. Conversely, when there is no relationship between the data in the subreport and the data in the main report, the unlinked subreport is used.

Unlinked Subreports Using the subreport approach, a main report can display a variety of data that may or may not be related. There is no requirement that the data in a subreport have any relationship to the data in the main report. For instance, if you typically generate a report called The Daily Report, it can contain information about the sales part of the business, the newest employees hired, and the company picnic. The main report is simply the vehicle used to contain all this information as a unit. When the subreport does not contain any information that is connected to the data in its main report, it is known as an unlinked subreport.

Linked Subreports The data in a subreport may be very relevant to the data in the main report and will share a common field value. This type of subreport is known as a linked subreport. A typical example of a linked subreport is to create customer invoices where one-time-only information is positioned on the main report to show the customer’s name, address, and order number while the subreport contains the line item details of items within each order. When you connect a subreport to a main report, you choose a database field or a formula to use as the connecting value. The field or formula’s value is used as a dynamic runtime parameter in the subreport to build a secondary record selection formula to retrieve data into the subreport. Data is retrieved into the subreport only when the link field’s value equals the parameter’s value. In the invoice example, the link field and dynamic parameter in the subreport is the order number that connects the main report to subreport’s invoice line items.

Figure 6.29 demonstrates the creation of a link from a field in the container report to a field in the subreport; this Link tab is available from the Insert > Subreport option. The tab is visible but not open in Figure 6.28. In this subreport, the link is being made on the ResortCode field, which exists in the main report and in the subreport.

Figure 6.29. Creating a link in a subreport

Figure 6.30 shows the result of a linked subreport; the Amenities report displays within the main report. The design tab for the AmenitiesByResort.rpt file is visible behind the previewed report.

Figure 6.30. An embedded subreport

In addition to the concept of linked and unlinked subreports, a subreport can be evaluated either as an in-place subreport or as an on-demand subreport.

In-Place Subreports

An in-place subreport is one that loads and displays at the same time as the main report. To a person looking at a report that contains an in-place subreport, the subreport may appear to be inside a black-outlined rectangle. Depending on which section the subreport is placed inside, the subreport will be visible a variable number of times.

On-Demand Subreports

At the bottom of the Insert Subreport dialog box, shown previously in Figure 6.28, the On-Demand Subreport check box option is visible. This option is disabled by default. When enabled, the subreport is not viewed by the user or evaluated by Crystal’s runtime process until the user clicks a link to display the subreport. This can help a report load and display on a computer screen very quickly since some of the processing is being deferred until later if and when the user wants to see the subreport.

Once the on-demand option is chosen, no further configuration is required to embed the link; however, you can add formatting that will make the link a little more pleasing to the eye. By default, the link appears as the name of the subreport, whatever you may have called it, with a simple hyperlink underline. You can modify the appearance of the text link using the Format Editor submenu, which is described in the next section. You can also suppress the text altogether and use a graphic as a link instead. To do this, suppress the subreport text link with the Format Editor, bring in a picture by choosing Insert > Picture from the main menu, and then position the graphic behind the subreport text link by right-clicking to show the context menu and selecting Move To Back.

Placement of a Subreport

Elements in a report are evaluated positionally from left to right and from top to bottom, and since a subreport is an element, it will be evaluated and displayed after any fields that come before it and before any elements that follow it. The positional placement is especially important when working with linked subreports. When working with data linked by group, you can position the linked subreport in the Group Header or Group Footer so that each time the group field value changes, the subreport changes to display data specific to the current group of data records. You’ll also want to make sure to place linking fields in the main report in sections that precede the subreport. This will allow the link fields to be evaluated and have a value that can then be used to connect to the subreport. If the subreport is placed before the linking field, no connection can take place because the link field will have no value in the main report until after the evaluation of the subreport.

Formatting a Subreport

Once a subreport is embedded in a report, you can right-click the subreport object to open a context menu, as shown in Figure 6.31. The items on the menu assist you in customizing the look and behavior of the link from formatting its appearance to editing the placement of the fields in the subreport.

Figure 6.31. Subreport menu

As a standard convention in Crystal, the top item in the menu tells you the object type you’re working with and its name. The menu here shows us working with the object type of subreport whose name is AmenitiesByResort.rpt. This particular subreport was created by embedding a previously created RPT file in the main report. Table 6.3 describes the behavior of the menu options.

Table 6.3: Subreport Menu Descriptions

Menu Options	Purpose
Format Subreport	Displays the Format Editor with options specific to changing the visual affects on a subreport or its behavior; see the discussion below for more information.
Edit Subreport	Opens the subreport in Design mode in its own tab with all sections and fields visible and available for normal report formatting.
Save Subreport As	Allows the subreport to be saved as an external RPT file; this option can be used whether the subreport was an internal one using an alias name or imported as an RPT file from the beginning.
Change Subreport Links	Modifies or creates a field link between a subreport and its main report; the dialog that displays is the same as the Links tab that is used when a subreport is first created.
Re-import Subreport	Interactively refreshes the data in the subreport and displays the most current data and its formatting.
Move	Allows an on-demand subreport link to be moved behind or in front of another object in the report, creating a layered effect.
Size And Position	Sets the height and width allotted to the subreport in Design mode as well as the x and y offset value positions in inches from the top-left corner of the section containing the subreport.
Delete	Deletes the subreport from the main report.
Cancel Menu	Closes the subreport menu.

Editing a Subreport

When you edit the subreport, the layout of the design elements is shown in its own tab, as shown in Figure 6.32. The particular subreport shown here includes a group and a formula. Unlinked subreports can be previewed directly from Design mode by clicking the Refresh toolbar icon or choosing View > Other Views and choosing the subreport to display. If you click the Refresh button while viewing the layout of a linked subreport, you’ll be prompted to provide any values required by the subreport that are normally provided by its main report; you can avoid this parameter-prompting approach by viewing the subreport through the main report’s Design tab instead.

The subreport displays as part of the main report on the Preview tab, and when you click the subreport itself, you can drill down to display the subreport in its own tab. Figure 6.32 also shows a second AmenitiesByResort.rpt tab, which is the drill-down display of the subreport; notice its jagged right edge and the left and right arrows beside the tab. Whenever Crystal can’t display the tab or additional tabs in their entirety, the jagged edge signals that there are unseen areas on the screen. Use the left and right arrows to navigate between tabs, and use the close button (X) visible at the top-right corner of a displayed tab to close the tab when desired.

Figure 6.32. Subreport in Design mode

At its creation, a subreport contains several sections of its own: a Report Header and Footer and a Details section. It will not and cannot contain Page Headers and Footers; the concept of a page does not apply to subreports. The remaining sections, however, are identical in behavior and function to their main report counterparts. If you add a group to a subreport, a Group Header and Group Footer are inserted into the sections. When working with linked subreports, there may be times when there is no data in the subreport to match the main report’s linking value; in this case, summaries and group totals may appear onscreen as zeros or be otherwise blank. You can modify this behavior by conditionally suppressing the Details section of a subreport using a formula in the Section Editor. The formula would test to see whether the field has a value, and if it does not, it would hide the section or the detail row. You can also conditionally suppress the Report Header and Report Footer as well as add additional sections.

Note 

Refer to Chapter 8, “Customizing Sections,” for more information on how to add additional sections and conditionally suppress a section based on a field or formula value.

The Subreport Format Editor

Selecting the Format Subreport menu option described in Table 6.3 opens the Format Editor shown in Figure 6.33. From here you can do things like changing the default look of an on-demand subreport to spice it up a bit beyond the blue underline hyperlink. While the majority of the settings in the Format Editor, such as the Common, Border, and Font tabs, should be familiar from Chapter 3, “Formatting Fields and Objects,” there are new settings that directly affect subreports. Subreport settings are found on the Subreport tab.

Subreport Name The subreport name will be either the alias you gave it for an internal subreport or the name of the RPT file retrieved into the report from a stored location. This name can be used for programmatic purposes for code-based interactions with the report. The subreport name should not be changed after you initially set it because only the first name assigned will ever be known or displayed inside Crystal on the Preview tabs.

On-Demand Subreport If an on-demand subreport was created when you inserted the subreport, this option is automatically enabled. It can be enabled or disabled to change from on-demand to in-place reports and vice versa.

On-Demand Preview Tab Caption This option is associated with a programming button that lets you write a formula to determine what the text for the hypertext link will be. The formula may be as simple as a manually typed text string or may be a complex calculation that results in a text string. If a subreport caption is not specified, the subreport name is used; this is the default. This option is not available if the subreport being formatted is not using the on-demand opening style.

Subreport Preview Tab Caption When a subreport is opened in Crystal, it displays on its own tab or in its own web browser window. The value set for the tab caption displays on the Preview tab itself or on the title bar in a web client. You can code a formula to dynamically set the Preview tab caption or hard-code a value like Amenities instead of settling for the default name of the subreport.

Re-Import When Opening This option forces Crystal to rerun the data query and refetch the data into the subreport when it is opened for use.

Suppress Blank Subreport Even when a subreport contains no data, it still takes up placement space in your report. To minimize the amount of space devoted to Crystal for this purpose when the subreport has no records, enable the Suppress Blank Subreport option.

Figure 6.33. Formatting a subreport

Subreport Data Sources

A subreport can use the data source of the main report or it can have an entirely independent data source. A data source can be different tables or different databases or even databases of different types. This makes a subreport a handy device for incorporating data from different places and presenting it within one report. The subreport can be either a linked or unlinked subreport as well as an in-place or on-demand subreport.

If your report makes use of more than one type of database, for instance, IBM DB2 and Microsoft SQL Server, subreports are recommended as a way to pull the data into a single report. Using this approach, you gain the following benefits:

• Each subreport can use a database driver specific to the data source for enhanced performance.

• Each subreport can use its own SQL statement for data retrieval.

• Each subreport connects to its data source separately without knowledge of the other data source.

• Nonindexed tables can be linked in a subreport to a main report.

Tables that do not have an index can benefit from being embedded in a linked subreport because, in general, nonindexed tables cannot be joined with any other table. A subreport that contains a field in common with the main report can superimpose a link into the report that did not exist in the data source or data sources. While it will not be as fast and efficient as a table join performed inside a database using an index, subreport linking can provide an alternative to an otherwise unlinkable data situation. In this case, performance is sacrificed for functionality. Whenever possible, it is best to link a subreport to a main report using an indexed field in the subreport to optimize the speed at which records are retrieved into the subreport.

Passing Values between a Subreport and a Main Report

Crystal allows data to be passed from a subreport to its main report using variables that are defined with a scope of Shared. These are known as shared variables and were introduced conceptually in Chapter 4, “Adding Business Logic with the Formula Workshop.” Shared variables are used to pass values between a subreport and a main report and, to be effective, require the use of two formulas:

• One formula to initially store a value in the shared variable

• A second formula to retrieve and use the value in the shared variable

As you might have guessed, you need to store the value before you can use it, so the placement of the formulas matters in relation to where the subreport is placed. The formula that stores the value is placed in the subreport; the formula that uses the value is placed in the main report.

Let’s demonstrate the sharing of values with two generically named formulas called @SetValue and @GetValue. If you were creating these types of formulas in your report, you might want to use more descriptive names like SetValueMyCount and GetValueMyCount, building the name of the variable being worked on into the name of the set and get formulas; this would make it easier to maintain the report over time. In the following formulas, we want to count the number of five-star resorts and display it with the grand total at the end of the report. The placement of the @SetValue formula field is visible in Figure 6.32. In this example, the field has been formatted to be suppressed so that no interim values will be displayed for this formula but the value will be available to pass to the main report.

   //@SetValue…place formula in subreport WhilePrintingRecords; Shared numberVar myCount; if {Resorts.FiveStarRating} = True then    myCount := myCount + 1;

With this field in the subreport (which has been positioned in the Details section), the field to retrieve the value can be placed anywhere in the report below the subreport. In Figure 6.34, you can see that the @GetValue formula has been placed in the Report Footer to display the total number.

Figure 6.34. Placing a value retrieval formula

//@GetValue … place formula in main report    WhilePrintingRecords;    Shared numberVar myCount;    myCount;

In this formula, the second line of code retrieves the value of the myCount variable, while the final line of code returns that value for printing in the report. For the formulas to work together between a subreport and a main report, the following rules must be followed:

• The keyword SHARED must be used to define the variable in both formulas.

• The variable name must be identical in the two formulas.

• The formula that sets the value must appear in the report before the formula that retrieves the value.

• Shared variables can be used only while records are being printed to the screen; therefore, the WhilePrintingRecords formula statement is required.

  Note 

  For more information on the WhilePrintingRecords formula, refer to Chapter 14.

In the code example for @SetValue and @GetValue, if the @GetValue formula had been positioned in the report in a section above the subreport, the value displayed would be zero since myCount would not exist until after the subreport was evaluated. A common technique to guarantee that the subreport

sets the value before the main report uses the value is to create a new section and place the subreport in that section prior to the section that contains the retrieval formula. To create a new section, right-click the section name in Design mode and choose Insert > Section Below, and then place the subreport in the first section and the retrieval formula in the second section.

Once retrieved, the value from the subreport can then be used in the main report in any formulas that appear after the subreport.

Warning 

Because the evaluation time of an on-demand subreport is determined by the user of the report, variables cannot be shared between on-demand subreports and main reports. If the sharing of values is a requirement of your report, use in-place subreports.

Subreport Caveats

Subreports are very much like ordinary reports in that you can add all the usual design elements to a subreport in the same way you would do it in a main report. However, because of their embedded nature, there are a few limitations put on subreports:

• Subreports do not get assigned their own .rpt file extension but instead are saved as a part of the main report’s RPT file unless intentionally and explicitly stored as an RPT using the Save Subreport As menu option (see Table 6.3).

• A subreport created internally inside an existing RPT file cannot be viewed or accessed independently of a main report (unless saved separately).

• Subreports cannot contain other subreports.

• A subreport can’t link to another subreport.

• Options set for the main report using File > Report Options apply only to the main report; to set options for the subreport, select the object in Design mode and choose File > Report Options for the selected object.

• Subreports do not have their own Page Header and Page Footer sections.

• If a subreport is placed in the Page Header or Page Footer of a main report, the subreport must be small enough to fit on a single page.

Page Headers and Footers cannot span multiple pages since, by their very definition, they are placed at the top and bottom of a page. If you embed a subreport that is too large for a Page Header or Footer, you’ll see a warning message similar to that shown in Figure 6.35.

Figure 6.35. Page size warning

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback