Crystal Reports Linking Main and Sub Report Data

/ Uncategorized / By

Linking a subreport to the data in the primary report

Frequently, the data in a subreport supplements the data in the primary report. You might, for example, have customer data in a primary report and then use subreports to show the orders for each customer. In such cases, you will need to coordinate the data in the primary report with the data in the subreport so that the orders in each subreport match up with the correct customer. To do this, you need to specify a field that is common to both the subreport and the primary report. With the Subreport Links dialog box, you create a link between the two common fields. Crystal Reports uses the link to match up records from the primary report to those in the subreport. The link makes certain that the “orders” data in the subreport sits on the same row as the corresponding “customer” data in the primary report.  Keep reading for step by step details on Crystal Reports Linking Main Report data from Sub Report fields.

To link a subreport to the data in the primary report

  1. If you are creating a new subreport or importing an existing report as a subreport, from the Insert menu, click Subreport. Choose or create a report and click the Link tab.

– or –

If you have already placed a subreport in the primary report, but did not create a link at setup, navigate to the Subreport Links dialog box by choosing Subreport Links from the Edit menu. The Subreport Links dialog box appears.

  1. Choose the subreport you want to link from the For subreport list (if it is not already selected).
  2. Select the field you want used as a link field in the primary (containing) report from the Available Fields list.
  3. Click the > arrow.

The field is added to the “Field(s) to link to” list box, and is now selected as a link field.

  1. Repeat steps 3 and 4 for each additional link, as desired.
  2. Use the Field link section (which will only appear if you have selected a link field) to set up the link for each link field:
  • Select the field you want linked to the primary report from the “Subreport parameter field to use.” • Select the “Select data in subreport based on field” check box on and select a field from the adjacent drop-down list to organize the subreport data based on a specific field (this is the quick equivalent of using the Select Expert). If nothing is specified here, the subreport will adopt the organization of the primary report.
  1. Click OK.

When you run the report, the program will coordinate the data in the primary report with the data in the subreport.

Note:

The field type of the Containing Report field determines which subreport fields are visible. Because the Report Designer reads dates as either strings, dates, or date/time fields, you must make sure your subreport parameter field type matches the field type set up in Report Options in the main report for the field you want linked.

Linking a subreport to the main report without modifying the selection formula

Crystal Reports uses a parameter field mechanism for linking subreports to main reports. When linking a main report field that is not a parameter field to a subreport field, the program:

  • Automatically creates a parameter field to complete the link.
  • Modifies the subreport record selection formula to select those records in which the subreport field is equal to the parameter field value.

The need for a parameter field is implied; it is called an “Implicit Link” situation. At times, you may wish to use a linked parameter field in a subreport without using it as part of the selection formula for the subreport. For instance, you may want the main report to pass in a summary value that can be used in calculations by the subreport, or you may want the main report to pass in the title of the subreport. When you link a field in the main report to a parameter field that you have created in the subreport, the program:

  • Checks the link you have specified.
  • Does not create any additional parameter fields.
  • Does not modify the subreport record selection formula.

Specifying a link is called an “Explicit Link” situation.

To link a subreport to a main report without modifying the selection formula

  1. Create a parameter field in the subreport.
  2. Link a field in the main report to that parameter field.

Combining unrelated reports by using subreports

At times, you may wish to combine unrelated reports into a single report. For example, you may want to create a single report that presents:

  • Sales grouped by sales representative.
  • Sales grouped by item.

While both reports deal with sales data, there is no real linear relationship between the reports.  Subreports can be used to combine unrelated reports into a single report like this. While the reports could be based on the same data set, they do not have to be. They could each be based on entirely different data sets.

Each of these reports is free-standing; the data in any of the reports is not linked in any way to data in another report. This is the easiest of the subreport options to work with.

To combine two unrelated reports

  1. Create the report you want printed first as the primary report.
  2. Import an existing report for use as a subreport or create a new subreport.
  3. Place the subreport into the Report Footer and it will print immediately after the primary report.

To combine three or more unrelated reports

  1. Create the report you want printed first as the primary report.
  2. Import or create each of the other reports you want to use as subreports.
  3. Use the Section Expert to insert enough Report Footer sections to match the number of subreports that you are using.

For example, if you want to use three subreports, insert two new Report Footer sections so that you have a total of three Report Footer sections.

  1. In Report Footer A, place the subreport you want printed immediately after the primary report. In Report Footer B, place the subreport you want printed next, and so forth. The primary report will print first and then the subreports in the order that you placed them in the report.

Note: Subreports can be placed side-by-side in the same Report Footer section. They will print next to each other at the end of the report.

  1. Place the subreports into the Report Footer sections and they will print sequentially after the primary report.

Using subreports with unlinkable data

Tables can be linked in a report as long as the following criteria are met:

  • The link fields are both database fields.
  • The link fields contain similar data.
  • The link fields are the same length.
  • The link field in the link to (lookup) table is indexed (PC databases only).

Linking tables is rarely a problem. However, there are some circumstances in which you cannot coordinate data from different tables because the data does not meet the linking criteria. For example, linking to or from a formula field, or linking two unindexed tables cannot be done in a single report. Subreports must be used.

Linking to/from a formula field

There are situations in which you may need to link to or from a formula (calculated) field. For example, an employee ID could be an 11 character value that consists of a two-character department code followed by the employee’s nine-character Social Security Number (for example, HR555347487).

The formula language makes it easy to extract the Social Security Number from this field:

{employee.EMPLOYEE ID} [-9 to -1]

– or –

{employee.EMPLOYEE ID} [3 to 12]

For the value HR555347487, either formula would return the value 555347487.

While the return value is a valid Social Security Number, the fact that it comes from a formula prevents you from using the field to link to a Social Security Number field in another table. You can report on and coordinate the values in the two tables, however, by using a subreport.

To link to/from a formula field

  1. Create the primary report using a table that includes the Social Security Number field.
  2. Create (or import) a subreport using the formula that extracts the Social Security Number from the Employee ID field (for this example, {@EXTRACT}).
  3. Place the subreport where you want it to appear in the primary report.
  4. Link the subreport to the primary report by linking the Social Security Number field in the primary report ({file.SSN}) to the formula that extracts the number in the subreport ({@EXTRACT}).

Want to know more? Come along to one of our highly rated Crystal Reports training courses, held regularly in Glasgow and Edinburgh