Using subreports

Subreports let you nest a report within your report. The data you display in a subreport is typically related to the data in the main report and you enable this by passing one or more subreport parameters to the subreport. However, the data displayed in a subreport can also be unrelated to the data in the main report.

Reasons to use subreports including the following:

• You wish to avoid complex SQL, such as a RIGHT OUTER JOIN.
• Your report requires data from multiple databases.

The following figure illustrates the use of subreport parameters and the relationship between a report and a subreport:

Note: Although the Report Builder supports multiple levels of nesting, it displays one level of nesting only.

For additional information on subreports, see Report Builder online Help.

Defining a subreport

You can define a subreport and include it in a report, or you can define it as part of inserting the subreport in the main report.

However, regardless of how you define it, a subreport has the following characteristics:

• Data displayed in the detail band only. A subreport uses no header or footer bands.
• If the subreport is related to the main report, it must include an internal query that uses a SELECT statement with a WHERE clause specifying the name of the input parameter used in the main report's Subreport Expression property.

Adding an existing subreport to a report

If you have already defined a subreport, you add it to the main report and define subreport parameters, as necessary.

To add an existing subreport to a report:

1. Define or open your main report.
2. Click the Subreport icon in the toolbox.
3. Drag an area for the subreport in the desired report band.

   The Subreport Source panel appears.

4. Select From an Existing Report, specify the subreport, and click Next.

   The Report to Subreport Parameter Binding panel appears.

5. Select the fields in the main report that correspond to fields in the subreport and click Next.

   The Finish Subreport Setup panel appears.

6. Click Finish.

   The Report Builder adds the subreport to the main report, saving the report to subreport mappings as subreport parameters.

7. To modify subreport parameter settings, select the subreport and click on Subreport Parameters in the Properties sheet.

Adding a new subreport to a report

If you are certain of the data required for a subreport, you can define a new subreport while adding it to the main report.

To add a new subreport to a report:

1. Define or open your main report.
2. Click the Subreport icon in the toolbox.
3. Drag an area for the subreport in the desired report band.

   The Subreport Source panel appears.

4. Select As a New Report and click Next.

   The Define Query Fields panel appears.

5. Click Query Builder.

   The Query Builder appears.

6. Select the tables and columns for the subreport.
7. Specify a WHERE clause for the report by using the Condition and Criteria columns for the key columns.

   Specify WHERE for Condition and either ='#CFVariable#' (string column) or =#CFVariable# (numeric column) for Criteria, and then overtype CFVariable with the name of the input parameter for the subreport (you define the input parameter name later in the procedure.)

8. Click Save.
9. Click Next.

   The Define Report Grouping panel appears.

10. Specify grouping fields, if appropriate for your subreport.
11. Click Next.

    The Report Layout panel appears.

12. Specify Free Form or Grid. Then click Next.

    The Report Style panel appears.

13. Specify Only Detail Band and click Next.

    The Report Theme panel appears.

14. Specify a color scheme and click Next.

    The Report Name panel appears.

15. Specify headings, as appropriate, and click Next.

    The Subreport Binding panel appears.

16. For each parameter required by the subreport, specify the following:
    • Parameter name.
    • Associated value from the main report (click the drop-down list box).
    • Data type.
17. Click Next.

    The Subreport Filename panel appears.

18. Specify a fully qualified filename for the subreport.
19. Click Next.

    The Finish Subreport Setup panel appears.

20. Click Finish.
21. Modify the subreport, as necessary.

    The Report Builder adds the subreport to the main report.

Modifying subreport settings

The Report Builder lets you change subreport name and modify subreport parameters in a main report.

To modify subreport settings:

1. Click the subreport element in the main report.

   Subreport settings display in the Properties sheet.

2. To change the subreport, modify Subreport Expression.
3. To modify subreport parameters:
   1. Click the Subreport Parameters property.
   2. Click the ... button.

   The Subreport Parameters dialog box appears.

   3. Add, modify, or delete subreport parameters.
   4. Click OK.

View comments in LiveDocs