Link Sub-Reports

By default, the main query and sub-report queries are independent of each other. However, you can link them by including data items from the main query in the sub-report filter expression.

For Example

You filter a sub-report query so that it shows only order information for the current customer name in the main query. In the graphic below the Customer Name is the main report and the Order Date is the sub-report.

The filter expression that links a sub-report to a main report looks like this:

<column name>=Main Query.<column name>

<Column name> is the data item you use to link the queries. The example above uses the Customer Name column to link the queries. When the sub-report query runs, it checks for the first row in the Customer Name column and shows only information for that customer.

If you want a sub-report to show information for another row in the column, place the main report in a form frame that shows only one row at a time. When you insert the sub-report into the form frame as well, it changes as you click through the rows in the main report. For example, the main and sub-reports above are both in a form frame that shows only one row of the Customer Name column at a time. Each time you scroll to another customer name, the sub-report shows only information for that customer.

Steps to Create the Main Report

1. Create a new report using the Blank template.

   The Blank template uses a form frame to create a report.

2. In the Data tab (Query dialog box), double-click the data items you want to include in the main query.

   To recreate the example above, open the Customers folder, double-click Customer Name, and click OK.

3. Click the empty report.

4. From the Format menu, click Properties, and click the Layout tab.

5. In the Scope of Data box, click Single Row of Data, and click OK.

6. From the Report menu, click Query.

7. In the Query Data box, click the Data items, and click Mark for Insertion. Click OK.

Steps to Add a Linked Sub-report

1. From the Insert menu, click Sub-Report, and click List Frame.

   The cursor shows a List Frame icon. Use to drag and size a list frame anywhere in the report.

2. In the Data tab (Query dialog box), double-click the data items you want to include in the sub-report query.

   To recreate the example above, open the Orders folder, and double-click Order Date and Closed Date.

3. Click the Filter tab, and use data items from the main query to create a filter expression.

   To recreate the example above

   • double-click Catalog Columns, open the Customers folder, and double-click Customer Name

   • double-click =

   • double-click Report Columns, Main Query, and Customer Name

4. Click OK.

Use the scroll buttons on the top right of the report to scroll through the data items in the main query. Each time you scroll, the report frame is recreated, and the sub-report query runs. The sub-report then shows information for the current row in the main query.

For more information about creating filter expressions, see .