The example below explains how to create a main report showing detail records for the table to which it is bound, and how to use a sub report that requires a parameter to display summary data for each detail record.

 

The example below creates the main report EmployeesSalesSummary1, using the report DetailSalesByEmployeeWithParameter. This sub report uses a single parameter, ChooseEmployee:

 

 

1.In the Project Pane, double-click the Reports item to launch the New Report wizard. Create a new report, and link this report to the desired table or tables using the New Report wizard or the Record Source tab.

 

In this example, the report is bound to the table SampleEmployees, and the fields FirstName, LastName, and Position are placed on the report through the wizard.

 

 

2.Add any desired components into the Page Header and Page Footer sections, and add and modify the Report Header or Report Footer sections as required.

 

The sample shown includes a report header with a title, the date, and a background rectangle, the employee name, and position in the Detail section and a page footer displaying page information.

 

 

3.Add a Sub Report () component to the Detail section of the report, and size the component as required for the sub report. Sub report components will only grow vertically to fit the data in the sub report; the width of the component will not change. Any data outside the width of the sub report component will be cut off.

 

A sub report has a thin black border around it by default. This can be removed by setting the Border Thickness to 0 in the Properties Pane.

 

In the sample above, a single sub report component is added for the DetailSalesByEmployeeWithParameter report.

 

4.Attach each sub report component to the report it will represent. This is done using the Source field on the Properties Pane. Once linked to a report, the sub report component will display the name of the report.

 

In the sample below, the sub report has been linked to the report DetailSalesByEmployeeWithParameter.

 

 

 

5.Since the sub report requires a parameter, the Passing and Receiving properties in the Properties Pane need to be set to pass the required information from the main report to the sub report.

 

 

6.The Passing property specifies the value that will be sent to the sub report for its parameter. This value can be the name of a field in the main report’s Record Source, an expression build from the data fields in the main report’s Record Source, or a typed-in static text or numeric value.

 

A typed-in static text or numeric value is the value that will be sent to the sub report for its parameter. This value can be the name of a field in the main report’s Record Source, an expression build from the data fields, or a static value. However the actual parameter is typed in (as a data field, an expression, or a value), its data type must match what the sub report’s parameter is expecting.

 

The sample sub report requires the employee’s ID as a parameter. The employee ID field, EmployeeID, is included in the main report’s Record Source, but is not actually displayed on the report itself. It is set as the passing parameter by typing [EmployeeID] as the Passing property in the Properties Pane. Alternatively, if the sub report required a different parameter, such as the year for which to display sales records, a numeric value, such as 2002, could be typed in for the year in the Passing field.

 

 

7.The Receiving property is the name of the parameter required by the sub report. Type the parameter’s name in the Receiving field. Note that when specifying a parameter name in this instance, the “at” symbol (@) is not required.

 

In the sample, the sub report DetailSalesByEmployeeWithParameter uses the parameter ChooseEmployee. It is set as the receiving parameter by typing ChooseEmployee as the Receiving property in the Properties Pane.

 

 

8.The report is now ready to preview or print.  When each detail record is processed, the parameter information is passed from the main report and received by the sub report as the Parameter name specified. The information displayed by the sub report will be determined by the passed parameter.

 

In the sample, the employee’s ID number is passed to the sub report to be used as the argument to the sub report’s parameter. The sub report then displays the total amount of sales and a breakdown of all the sales for the employee with that employee number.