Adding a Subreport

Adding a subreport to a main report involves two steps: adding the report and setting the linking field. When adding the report you can choose to add an existing report or create a new report from scratch. If you create a new report from scratch, you will go through the Report Wizard to set its data source and specify the fields to print. After adding the subreport, you have the option of setting it to be an on-demand subreport. The on-demand subreport is displayed as a clickable link on the report and doesn’t open until the user clicks on the link.

The second step of adding a subreport is setting the linking field. You have three options for linking reports. You can link them by a data field, a formula, or even leave it unlinked. Each one of these options gives you different benefits. Let’s look at the details of adding the subreport next.

Subreports can only be one level deep. A subreport object can only be added to a main report and it can’t be added to another subreport. If you are editing an existing subreport, then the menu options Insert > Subreport are disabled.

To add a subreport to an existing report, select the menu options Insert > Subreport. You can also use the Insert Subreport button shown in Figure 9-1.

Figure 9-1. Insert Subreport Button

This opens the Insert Subreport dialog box, shown in Figure 9-2.

Figure 9-2. The Insert Subreport dialog box.

The top portion of this dialog box lets you choose the method for adding the subreport. The first way of adding a subreport is to select an existing report. Click the top radio button to enable the Browse button. Clicking the Browse button lets you find the report on your local computer or on the network. After selecting the report and clicking OK, the full filename and path is entered onto the dialog box.

The second way of adding a subreport is creating a report from scratch. When selecting this option, the Report Wizard button stays disabled until you enter a new report name. Once you enter a report name and click the Report Wizard button, the Report Expert dialog box opens and walks you through the steps for creating a new report.

When importing an existing report as a subreport, the subreport becomes a copy of the original report. When you make changes to the subreport, the original report is not modified. The subreport is saved within the same .rpt file as the main report.

Browsing for an existing report is fairly trivial and creating a new report using the Report Wizard has been covered earlier in the book, so there is no point in rehashing those details here. Let’s move forward to the second step of adding a subreport: setting the linking field. Click on the Links tab (shown in Figure 9-3) to set how the reports are linked. Leave it alone if this is an unlinked report.

Figure 9-3. The Link tab of the Insert Subreport dialog box.

This dialog box is where you set the properties that determine how the subreport is linked to the main report. The Link tab has a list on the left side that shows the available report fields, formulas and data fields. These fields are from the parent report that holds the subreport. Select one or more of the fields and click the right arrow button to move it to the Fields To Link To list on the right. You can also double-click a field to add it to this list. The fields in this list are the ones that are passed to the subreport.

Once a field is added to the Fields To Link To list, the bottom section of the dialog box changes to show that a new parameter will be added to the subreport for that field. This parameter is automatically created by Crystal Reports for you. It’s shown on the bottom left corner of the dialog box and starts with “?Pm-“.

The bottom right side of the dialog box is where you tell Crystal Reports how to interact with this new parameter. The checkbox tells Crystal Reports whether or not you want this to be a linked subreport. If it is a linked subreport, the dropdown box below the checkbox tells it which field to link to. Crystal Reports tries to guess which field in the subreport should be linked with the parameter. It basically does this by looking to see if the subreport has a field with the same name as the field in the master table. If not, it will also try to match up data types. Be sure to check the field that it picked because it may not be the one you want.

Question: I clicked on the drop-down box to select the subreport field to link to, and the field I want to use isn’t listed. Why don’t I see all subreport fields in this list?

Answer: Crystal Reports only shows fields that have the same data type as the field in the main report that it is being linked to. For example, if the field in the main report is a string, the list of subreport fields will only show strings as well. Be careful with date fields because the data type Date is not the same as the DateTime data type.

By default, the subreport is linked and the option Select Data In Subreport Based on Field is checked. If you uncheck this option, then the subreport becomes an unlinked report and the dropdown box below it becomes disabled.

When you click the OK button to save your changes, the dialog box disappears and you are taken back to the main report in design mode. However, there is now the outline of a rectangle attached to the mouse cursor. This rectangle represents the new subreport. Move your mouse around on the report to position the rectangle where you want the subreport to be. Once it is positioned correctly, click the mouse button to release it and drop the subreport at that location. If you didn’t place it exactly right, you can always click on it again and drag it to a new position.

If the subreport spans more than a single page, then you can’t place it in the Page Header section. Page headers aren’t allowed to span more than one page and a large subreport will break this rule. If you do so, Crystal Reports will give you the error message, “The page size is not large enough to format the contents of an object.”

Crystal Reports automatically modifies the subreport so that the information from the main report can be passed to the subreport and select the data. It does this by creating a new parameter field (using the name specified in the dialog box) and modifying the record selection formula. If you look at the subreport, you can see how these changes take effect. Figure 9-4 shows a subreport that has the new parameter field in it.

Figure 9-4. The new parameter field created in the subreport.

The subreport parameter field works differently than how you expect parameters to work. For subreports, you are not prompted to enter a value for the parameter. Instead, Crystal Reports automatically fills in the parameter for you with the value of the data field from the main report. This is done for you behind the scenes.

The second step that Crystal Reports does when you create a linked subreport is to modify the subreport record selection formula. The selection of the subreport is modified so that the subreport field is equal to the new parameter field. Figure 9-5 shows the Select Expert dialog box of the subreport.

Figure 9-5. The new record selection formula of the subreport.

You can see from these two figures that there is nothing magical about linked subreports. Crystal Reports creates a parameter to hold the main report’s value and the record selection formula ensures that the subreport’s data gets filtered.

The record selection formula isn’t restricted to filtering data based on this one field. You can add more selection criteria to further filter the data. For example, you could also add date range filters, product categories, etc.

The default comparison operator between the parameter field and the subreport field is the equal sign. But you can easily change it to less than, greater than, not equal to, etc. Clearly, these are unusual circumstances because most of the time you want the data to be equal. But you do have the option for more flexible selection criteria when necessary.