Linked Subreports

A linked subreport is handy when you need to display related data elements from more than one database table on a report, but can t tie the tables together properly for one reason or another (such reasons are discussed in detail at the beginning of the chapter). A report that encounters a many-to-many relationship when it attempts to show multiple sales records in a region, followed by multiple credits in the same region, fits this category. The subreports follow along with the main report.

The initial steps for creating a linked subreport are the same as for creating an unlinked subreport. Use the Insert Subreport toolbar button or menu options to create a subreport. Then, import an existing report or create a new subreport with the Subreport Expert. However, before you click OK in the Insert Subreport dialog box, click the Link tab. This will display the Subreport Links dialog box, shown in Figure 13-2, where you can choose how to link the subreport to the main report.

Figure 13-2: Subreport Links dialog box

Tip  

If you inadvertently click OK in the Insert Subreport dialog box before linking, you can still link the subreport after you place it on the main report. You can also choose to change links for an existing linked subreport or link a previously unlinked subreport. Choose Edit Subreport Links from the pull-down menus , or right-click the subreport object and choose Change Subreport Links from the pop-up menu.

If you are linking directly from the Link tab on the Insert Subreport dialog box, the For Subreport drop-down list will be dimmed ”you will be setting links for the subreport you are currently creating. If you are linking a subreport already on the main report, you can choose the subreport you want to set links for (don t forget ”there can be more than one subreport on a main report).

The Available Fields list shows fields and formulas available in the main report. Select the field from the list you want to link from, and add it to the Field(s) To Link To list by clicking the right arrow button. If you later decide you don t want to link on that field, select it in the Field(s) To Link To list and remove it with the left arrow button.

Once you ve added a main report field to link on, three additional options appear at the bottom of the Subreport Links dialog box. The Subreport Parameter Field To Use drop-down list contains any parameter fields you have created in the subreport (see Chapter 14 for information on parameter fields). In addition to any that you have created, Crystal Reports will create a parameter field consisting of the main report field prefixed with Pm-. If you want to link the subreport so that it shows only matching records for the main report field, just leave this automatically created parameter field selected.

The general approach of linked subreports is to limit the subreport to records that match the linking field from the main report. If this is the behavior you want, make sure Select Data in Subreport Based on Field is checked. Then, use the drop-down list below the check box to choose the field in the subreport that you want to use to limit records (Crystal Reports will automatically show any subreport field that has the same field name as the main report linking field). If you want to use more than one field to link the main report to the subreport, just add additional fields to the Field(s) To Link To list and match them up to the corresponding subreport fields.

Clicking OK closes the Subreport Links or Insert Subreport dialog box and creates the links between the main report and the subreport. If you re just creating the subreport, its outline will be attached to the mouse cursor. Drop it in the appropriate main report section, typically the section that matches the field you linked the subreport on. A subreport link is based on two concepts: passing data from the main report into a subreport parameter field, and creating a record-selection formula in the subreport based on the parameter field. This way, every time the main report runs the subreport, it places the value of the main report linking field in the parameter field, which is used to select records for the subreport.

Because of this method of subreport linking, whenever you try to preview a linked subreport on its own, you ll be prompted to supply a parameter field:

This indicates that the value for the parameter field is not being passed from the main report and you need to provide it. Type a valid value for the linked field (such as a state abbreviation, customer number, department code ”whatever value is appropriate for the linked field) and click OK. The subreport Preview tab will appear showing just the records that you specified.

When you preview the main report, it will pass data to the subreport via the parameter field every time the subreport is processed ; the subreport will use the parameter field in its record-selection formula and will return the limited set of resulting records to the main report. Figure 13-3 shows the customer/credit report mentioned previously, with the credit subreport appearing in the state group footer.

Figure 13-3: Linked subreport placed in group footer

Linking Based on Formula Fields

If you use the Database Expert (discussed in Chapter 16) to link tables together in the main report, you can link only on actual database fields. This may be a problem if a field in one table doesn t exactly match the data type or organization in another table. For example, you may want to link two tables together based on a First/Last Name field because there is no other common number field or other linkable field. The problem, however, might be that the fields are separated into individual First and Last Name fields in one table, and contained in a single Name field in the other table. The link will never work in the Database Expert because of the differences in the data layout.

One of the benefits of using subreports is their ability to link based on a formula field, instead of just using database fields. By creating a subreport, you can link the two tables together. The key is to use a formula to concatenate the individual First and Last Name fields together into one combined formula field. Once you ve created the formula in the report that contains the separate Name fields, the Name formula field will appear in the Subreport Links dialog box and you can choose it as a From or To linking field.

Note  

Chapter 5 discusses concatenating string fields and other formula-creation techniques.