Show All

Hide All

ActiveReports allows 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 walkthrough illustrates how to modify the subreport record source from the data in the parent report to retrieve the correct information.

This walkthrough is split up into the following activities:

• Adding a main report and a subreport to a Visual Studio project
• Connecting the main report to a data source
• Adding controls to the main report to display data and contain the subreport
• Adding controls to the subreport to display data
• Adding code to save the current record's CategoryID for use in the subreport's SQL query
• Adding code to create an instance of the subreport
• Adding code to assign a data source for the subreport

To complete the walkthrough, you must have access to the Northwind database.
A copy is located at C:\Program Files\GrapeCity\ActiveReports 6\Data\NWIND.MDB (on a 64-bit Windows operating system, a copy is located in C:\Program Files (x86)\GrapeCity\ActiveReports 6\Data\NWIND.MDB).

When you have finished this walkthrough, you will have a report that looks similar to the following.

To add a main report and a subreport to a Visual Studio project

1. Open a new project in Visual Studio.
2. From the Project menu, select Add New Item.
3. Select ActiveReports 6 (code-based) File and rename the file rptMain.
4. Click Add.
5. Add a second report named rptSub.

To connect the main report to a data source

1. Click the gray report DataSource icon on the Detail section band of rptMain 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 * FROM Categories

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

To add controls to the main report to display data and contain the subreport

1. Click in the gray area below rptMain to select the report.
2. In the Properties Window, set the PrintWidth property to 5.75.
3. Set the CanShrink property of the Detail section of rptMain to True to eliminate white space.
4. Drag a Label control onto the Page Header section of rptMain, setting the properties as indicated:

   Label Properties

   Size	Font Size	Name	Miscellaneous	Text	Location
   5.75, 0.25 in	14	lblProductsbyCategory	Align = Center	Products by Category	0, 0 in

5. Drag the following controls onto the Detail section of rptMain, setting the properties as indicated:

   Detail section controls

   Control	Miscellaneous	Name	Text	Location
   Label	Bold Size = 1.15, 0.2 in	lblCategoryName	Category Name:	0, 0.05 in
   TextBox	DataField = CategoryName	txtCategoryName1		1.15, 0.05 in
   Label	Bold	lblProducts	Products:	2.4, 0.05 in
   SubReport	Size = 2.25, 1 in	SubReport1		3.5, 0.05 in
   TextBox	DataField = CategoryID Visible = False	txtCategoryID1

To add controls to the subreport to display data

1. Set the CanShrink property of the Detail section of rptSub to True to eliminate white space.
2. Set the BackColor property of the Detail section to AliceBlue to distinguish the subreport from the main report.

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

3. Right-click the PageHeader or PageFooter section and select Delete. Subreports do not render these sections, so deleting them saves processing time.
4. Add a TextBox control to the Detail section of rptSub, setting the properties as indicated:

TextBox properties

Adding code to create an instance of the subreport

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.

To write the code in Visual Basic

Private rpt As rptSub
Private childDataSource As New DataDynamics.ActiveReports.DataSources.OleDBDataSource()

rpt = New rptSub()

To write the code in C#

private rptSub rpt;
private DataDynamics.ActiveReports.DataSources.OleDBDataSource childDataSource = new DataDynamics.ActiveReports.DataSources.OleDBDataSource();

rpt = new rptSub();

Adding code to assign a data source for the subreport

1. Back in design view of the report, 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

To write the code in Visual Basic

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

To write the code in C#

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