The example shown below will explain how to create a main report showing detail records for the table to which it is bound, and how to dynamically filter the contents of a sub report shown for each detail record.

 

The example below creates the main report EmployeesSalesSummary3, using the report DetailSalesByEmployeeWithNoParameter. This sub report uses no parameters; it will use the Filter By property field to dynamically filter the sub report contents for each detail record by employee ID and the year provided. The Filter By property is a SQL expression string that details how to filter the data in the sub report.

 

The Design View of the sub report is displayed below:

 

sub_report_dynamic_filtering_design_view

 

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 EmployeeSalesSummary3 is bound to the table SampleEmployees and the fields FirstName, LastName, and Position are placed on the report through the wizard.

 

sub_report_dynamic_filtering_detail

 

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.

 

sub_report_dynamic_filtering_detail_2

 

3.Add a Sub Report (toolbar_icon_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 DetailSalesByEmployeeWithNoParameter report.

 

sub_report_dynamic_filtering_detail_3

 

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 DetailSalesByEmployeeWithNoParameter.

 

sub_report_dynamic_filtering_detail_4

 

sub_report_dynamic_filtering_properties

 

5.To dynamically filter the sub report, the property field Filter By in the Properties Pane must be set to pass the required information from the main report to the sub report. This value is a SQL expression string that is passed to the sub report and used to filter the data.

 

sub_report_dynamic_filtering_properties_2

 

6.The Filter By property is a criteria expression string used by the sub report as a WHERE clause to filter the sub report’s data. The criteria expression string is built by concatenating text strings with the required fields or expressions from the main report to create the WHERE clause used in the sub report.

 

The sample report will filter the sub report for each detail record by that detail record’s EmployeeID, and for all the sales in the year 2003. The following string is entered:

 

“[EmployeeID] = “ + [EmployeeID] + “AND YEAR([OrderDate])=2003”

 

The quoted text will be passed to the sub report as is, while any text that is not quoted is seen as data to be filled in by the main report.  For the above example, assuming a detail record with EmployeeID of 1, the WHERE clause that will be used by the sub report would become:

 

WHERE [EmployeeID] = 1 AND YEAR([OrderDate])=2003

 

sub_report_dynamic_filtering_properties_3

 

7.The report is now ready to preview or print. When each detail record is processed, the WHERE clause is constructed for the sub report for that detail record. The EmployeeID information for that detail record is passed from the main report, substituted into the criteria string, and then received by the sub report as Filter By criteria. The information displayed by the sub report will be determined by criteria set in the Filter By field.

 

report_sub_report_dynamic_filter_print_preview