The example shown 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 more than one parameter to display summary data for each detail record.

 

The example below creates the main report EmployeesSalesSummary2, using the report DetailSalesByEmployeeWith2Parameter.

 

This sub report uses two parameters, ChooseEmployee and ChooseItem:

 

 

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 EmployeeSalesSummary2 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, 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, a single sub report component is added for the DetailSalesByEmployeeWith2Parameters 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 DetailSalesByEmployeeWith2Parameters.

 

 

 

5.Since the sub report requires two parameters, 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.

 

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. To specify more than one parameter in this property, separate each parameter by a semi-colon.

 

The sample sub report requires both an employee ID and an item description as parameters. The field EmployeeID is included in the main report’s Record Source, but is not actually displayed on the report itself. The value for the item description is not included in the main report’s record source and will be typed directly into the parameter list. The Passing values are set as [EmployeeID];”Letter Paper” in the Properties Pane.

 

 

7.The Receiving property specifies the name of the parameters required by the sub report.  When more than one parameter is required, each parameter name in the list is separated by a semicolon. Note that when specifying a parameter name in this instance, the “at” symbol (@) is not required. The parameter names must be entered in the same order as the values are entered in the Passing property value.

 

In the sample, the sub report DetailSalesByEmployeeWith2Parameters uses the parameters ChooseEmployee and ChooseItem. These parameters are set as the receiving parameters by typing ChooseEmployee;ChooseItem 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 and the text string Letter Paper are passed to the sub report to be used as the argument to the sub report’s parameters ChooseEmployee and ChooseItem. The sub report then displays the total amount of sales and a breakdown of all the sales of the item Letter Paper for the employee with that employee number.