Collapse All Expand All

To pass parameters to a subreport, you set the datasources of both reports with the parent report supplying the parameter value for the subreport. There are also some properties that to set on the child report to optimize it to run as a subreport.

To connect the parent report to a data source

1. Click the gray report DataSource icon on the Detail section band to open the Report Data Source dialog.
   
2. On the "OLE DB" tab, next to Connection String, click the Build button.
3. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
4. Click the ellipsis (...) button to browse to the Northwind database. Click Open once you have selected the appropriate access path.
5. Click OK to close the window and fill in the Connection String field.
6. In the Query field, enter the following SQL query

   SQL Query	Copy Code
   SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.Extension, Customers.CustomerID FROM Customers, Employees ORDER BY Employees.EmployeeID, Customers.CustomerID

7. Click OK to save the data source and return to the report design surface.

   Note: This query joins the Employees table for the parent report to the Customers table for the child report.

To connect the child report to a data source

1. Click the gray report DataSource icon on the Detail section band to open the Report Data Source dialog.
   
2. On the "OLE DB" tab, next to Connection String, click the Build button.
3. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
4. Click the ellipsis (...) button to browse to the Northwind database. Click Open once you have selected the appropriate access path.
5. Click OK to close the window and fill in the Connection String field.
6. In the Query field, enter the following SQL query

   SQL Query	Copy Code
   SELECT Customers.*, Employees.EmployeeID, Orders.OrderID FROM Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID WHERE CustomerID = '<%CustomerID%>'

7. Click OK to save the data source and return to the report design surface.

   Note: This SQL query uses parameters syntax: '<%CustomerID%>'. For more information on parameters, see the Parameters topic.

To set up the child report to be used in the subreport control

1. Click in the gray area below the child report to select the report.
2. In the Properties Window, change the ShowParametersUI property to False. This prevents the subreport from requesting parameter values from the user.
3. Right-click the PageHeader or PageFooter section and select Delete. Subreports do not render these sections, so deleting them saves processing time.

To display the child report in the subreport control on the parent report, see the Embed Subreports in a Report topic.