Subreports with Run-Time Data Sources

ActiveReports allows Section Reports to contain any number of child reports using the Subreport control. Child reports, or subreports, are executed each time the parent section (i.e. the section in which the Subreport control is placed) is processed. This tutorial illustrates how to modify the subreport record source from the data in the parent report to retrieve the correct information.

Create Report

1. In Visual Studio, create a new Windows Forms App (.NET Framework) project and click Next.
2. In the Configure your new project dialog, type a name for your project, set Framework to .NET Framework 4.7 and click Create.
3. From the Project menu, select Add New Item.
4. In the Add New Item dialog that appears, select ActiveReports 19 Code-Based Report and in the Name field, rename the file as rptMain.
5. Click the Add button to open a new Section Report in the designer.
6. From the Project menu, select Add New Item.
7. In the Add New Item dialog that appears, select ActiveReports 19 Code-Based Report and in the Name field, rename the file as rptSub.
8. Click the Add button to open a second new Section Report in the designer.

Connect the Parent Report (rptMain) to a Data Source

1. On the detail section band, click the Data Source Icon.
   

2. In the Report Data Source dialog that appears, from the OLE DB tab, create a data source connection.

3. Once the connection string field is populated, in the Query field, enter the following SQL query.

   SELECT * FROM Categories
   

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

Design Report Layout for the Parent Report (rptMain)

1. In the Report Explorer, select the report and in the Properties window, set the PrintWidth property to 5.75.

2. On the design surface, select the detail section and in the Properties window, set the CanShrink property to True to eliminate white space.

3. From the toolbox, drag a Label control onto the pageHeader section and in the Properties window, set the properties as follows.

   Property Name	Property Value
   Name	lblProductsbyCategory
   Text	Products by Category

4. From the toolbox, drag the following controls onto the detail section and in the Properties window, set the properties as follows.

   Property Name	Property Value
   TextBox1
   Name	txtCategoryID1
   Data Field	CategoryID
   Visible	True
   TextBox2
   Name	txtCategoryName1
   Data Field	CategoryName
   Label1
   Name	lblCategoryName
   Text	CategoryName:
   Label2
   Name	lblProducts
   Text	Products:
   Subreport
   Name	SubReport1

Design Report Layout for the Child Report (rptSub)

1. On the design surface, select the detail section and in the Properties window, set the following properties.

   Property Name	Property Value
   CanShrink	True
   BackColor	AliceBlue

   type=info

   Tip: Even if you do not want colors in your finished reports, using background colors on subreports can help in troubleshooting layout issues.

2. On the design surface, right-click the pageHeader or pageFooter section and select Delete. Subreports do not render these sections, so deleting them saves processing time.

3. From the toolbox, drag a TextBox control to the detail section and in the Properties window, set the following properties.

   Property Name	Property Value
   DataField	ProductName
   Name	txtProductName
   Text	Product Name

Add Code to Create an Instance of the Subreport

Design Report Layout for the Child Report (rptSub)

1. On the design surface, select the detail section section and in the Properties panel, set the BackColor to 'White Smoke'

   type=info

   Tip: Even if you do not want colors in your finished reports, using background colors on subreports can help in troubleshooting layout issues.

2. On the design surface, right-click the pageHeader or pageFooter section and select Delete. Subreports do not render these sections, so deleting them saves processing time.

3. From the toolbox, drag the following controls to the detail section and in the Properties panel, set the properties as follows.
   

   Property Name	Property Value
   TextBox1
   DataField	ProductName
   Name	txtProductName
   TextBox2
   DataField	UnitPrice
   Name	txtUnitPrice
   OutputFormat	$#,##0.00 (or select Currency in the dialog)

Add Code to Create a new instance of the Child Report (rptSub)

type=warning

Warning: Do not create a new instance of the subreport in the Format event. Doing so creates a new subreport each time the section Format code is run, which uses a lot of memory.

VB Code:

1. Right-click the design surface of rptMain and select View Code.
2. At the top left of the code view of the report, click the drop-down arrow and select (rptMain Events).
3. At the top right of the code window, click the drop-down arrow and select ReportStart. This creates an event-handling method for the ReportStart event.
4. Add code to the handler to create an instance of rptSub.

The following example shows what the code for the method looks like.

Private rpt As rptSub
Private childDataSource As New GrapeCity.ActiveReports.Data.OleDBDataSource()

rpt = New rptSub()

C# Code:

1. Click in the gray area below rptMain to select it.
2. Click the events icon in the Properties panel to display available events for the report.
3. Double-click ReportStart. This creates an event-handling method for the report's ReportStart event.
4. Add code to the handler to create a new instance of rptSub.

The following example shows what the code for the method looks like.

private rptSub rpt;
private GrapeCity.ActiveReports.Data.OleDBDataSource childDataSource = new GrapeCity.ActiveReports.Data.OleDBDataSource();

rpt = new rptSub();

Add Code to Assign a Data Source for the Child Report {rptSub)

1. Back in design view of the Parent report (rptMain), double-click the detail section. This creates the Detail_Format event handler.

2. Add code to the handler to:

   • Set the connection string for the OleDBDataSource for the subreport
   • Set the SQL query for the new data source and pass in the current record's CategoryID
   • Set the data source of the subreport to the data source
   • Assign rptSub to the SubReport control

   The following example shows what the code for the method looks like.

   childDataSource.ConnectionString = CType(Me.DataSource, GrapeCity.ActiveReports.Data.OleDBDataSource).ConnectionString
   childDataSource.SQL = "SELECT * FROM Products WHERE CategoryID = " + Me.txtCategoryID1.Value.ToString
   rpt.DataSource = childDataSource
   SubReport1.Report = rpt
   

   childDataSource.ConnectionString = ((GrapeCity.ActiveReports.Data.OleDBDataSource)this.DataSource).ConnectionString;
   childDataSource.SQL = "SELECT * FROM Products WHERE CategoryID = " + this.txtCategoryID1.Value.ToString();
   rpt.DataSource = childDataSource;
   SubReport1.Report = rpt;
   

   Preview the report

   Click the preview tab to view the report at design time.