In ActiveReports for .NET 3.0, datasets with relationships can be used to populate fields in subreports.

Subreports do not render PageHeader/Footer sections. To save on processing time, always delete PageHeader/Footer sections from subreports.

This walkthrough illustrates how to use datasets with relationships in nested subreports.

This walkthrough is split up into the following activities:

Adding three ActiveReports to a Visual Studio project
Adding controls to each report to display the data
Setting the data connection for the reports, creating and filling data adapters and adding dataset relations
Adding code to set the subreport control's data source equal to the returned row collection of the dataset
Viewing the report

To complete the walkthrough, you must have access to the Northwind database.
A copy is located at C:\Program Files\Data Dynamics\ActiveReports for .NET 3.0\Data\NWIND.MDB.

We will be establishing a relationship between the Categories, Products, and Order Details tables at run time.

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

Adding three ActiveReports to a Visual Studio project

To add three ActiveReports to a Visual Studio project

Open a new project in Visual Studio.
From the Project menu, select Add New Item.
Select ActiveReports 3.0 File and rename the file rptCategories.
Click Open.
From the Project menu, select Add New Item.
Select ActiveReports 3.0 File and rename the file rptProductsSub.
Click Open.
From the Project menu, select Add New Item.
Select ActiveReports 3.0 File and rename the file rptOrderDetailsSub.
Click Open.

Adding controls to display the data

To add controls to the reports

Add a GroupHeader/Footer section to rptCategories by right-clicking on the design surface of the report and selecting Insert > Report Header/Footer.
Make the following changes to the group header:
- Change the Name property to ghCategories
- Change the UnderlayNext property to True
- Change the GroupKeepTogether property to All
- Change the DataField property to CategoryName
- Change the BackColor property to Gainsboro
Change the BackColor property of the page header to Silver.

Add the following controls to rptCategories, setting the properties as indicated:

Control	DataField	Name	Text	Section	Location	Font Size
Label		lblCategoryName	Category Name	PageHeader	0, 0	12
Label		lblProduct	Product	PageHeader	1.5, 0	12
Label		lblOrderDetails	Order Details	PageHeader	2.75, 0	12
TextBox	CategoryName	txtCategoryName	Category Name	GroupHeader	0, 0
Subreport		SubReport1		Detail	1.5, 0

Add a GroupHeader/Footer section to rptProductsSub by right-clicking the design surface of the report and selecting Insert > Group Header/Footer.
Make the following changes to the group header:
- Change the Name property to ghProducts
- Change the UnderlayNext property to True
- Change the GroupKeepTogether property to All
- Set the DataField property to ProductName
- Change the BackColor property to LightSteelBlue

Add the following controls to rptProductsSub, setting the properties as indicated:

Control	Miscellaneous	Name	Text	Section	Location
TextBox	DataField = ProductName	txtProductName	Product Name	GroupHeader	0, 0
Label	Underlined	lblUnitPrice	Unit Price	Detail	1, 0
Label	Underlined	lblQuantity	Quantity	Detail	1.75, 0
Label	Underlined	lblDiscount	Discount	Detail	2.44, 0
Subreport		SubReport1		Detail	1, 0.25

Add the following controls to rptOrderDetailsSub, setting the properties as indicated:

Control	DataField	Name	Text	OutputFormat	Alignment	Location
TextBox	UnitPrice	txtUnitPrice	Unit Price	Currency	Right	0, 0
TextBox	Quantity	txtQuantity	Quantity		Right	0.75, 0
TextBox	Discount	txtDiscount	Discount	0%	Right	1.44, 0

Set the CanShrink property of the detail section to True.

Setting the data connections, data adapters and dataset relations

To set the data connections

Add a Windows Form to your project.
Add an ActiveReports viewer control to the Windows Form and set the Dock property to Fill.

To write the code for the viewer in Visual Basic or C#

Double-click on the title bar of the Windows Form. This creates an event-handling method for the Form Load event. Add code to the handler to:

Set the data connections
Create and fill the data adapters
Create dataset relationships
Format the viewer to show the report when it is run

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

'Visual Basic
'Create the dataset
Dim myJoinedDataSet As New DataSet()
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   'Create a new instance of rptCategories and set the viewer document equal to the report's document 
   Dim rpt As New rptCategories()
   Me.Viewer1.Document = rpt.Document
	
   'Create the data connection
   Dim connString As String
   Dim conn As New OleDb.OleDbConnection()
   'Change the path to your data source as necessary
   connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Data Dynamics\ActiveReports for .NET 3.0\Data\NWIND.MDB"
   conn = New OleDb.OleDbConnection(connString)
   conn.Open()
	
   'Create the data adapters with the SQL SelectCommand to retrieve data from the Northwind database
   Dim adapterCategories As New OleDb.OleDbDataAdapter("Select * from categories order by categoryname", conn)
   Dim adapterProducts As New OleDb.OleDbDataAdapter("Select * from products order by productname", conn)
   Dim adapterOrderDetails As New OleDb.OleDbDataAdapter("Select * from [order details]", conn)
	
   'Fill the dataset with the retrieved data
   adapterCategories.Fill(myJoinedDataSet, "Categories")
   adapterProducts.Fill(myJoinedDataSet, "Products")
   adapterOrderDetails.Fill(myJoinedDataSet, "OrderDetails")
	
   'Create the relationships between the dataset's tables
   Me.myJoinedDataSet.Relations.Add("CategoriesProducts", myJoinedDataSet.Tables("Categories").Columns("CategoryID"), myJoinedDataSet.Tables("Products").Columns("CategoryID"))
   Me.myJoinedDataSet.Relations.Add("ProductsOrderDetails", myJoinedDataSet.Tables("Products").Columns("ProductID"), myJoinedDataSet.Tables("OrderDetails").Columns("ProductID"))
	
   'Set the data source for rptCategories
   rpt.DataSource = (myJoinedDataSet)
   rpt.DataMember = "Categories"
	
   'Run the report
   rpt.Run()
   
   'Close the connection
   conn.Close()
End Sub

//C#
//Create the dataset
public DataSet myJoinedDataSet = new DataSet();
private void Form1_Load(object sender, System.EventArgs e)
{
   //Create a new instance of rptCategories and set the viewer document equal to the report's document
   rptCategories rpt = new rptCategories();
   this.viewer1.Document = rpt.Document;
	
   //Create the data connection
   //Change the path to your data source as necessary
   string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Data Dynamics\ActiveReports for .NET 3.0\Data\NWIND.MDB";
   OleDbConnection conn = new OleDbConnection(connString);
   conn.Open();
	
   //Create the data adapters with the SQL SelectCommand to retrieve data from the Northwind database
   OleDbDataAdapter adapterCategories  = new OleDbDataAdapter("Select * from categories order 
	by categoryname",conn);
   OleDbDataAdapter adapterProducts  = new OleDbDataAdapter("Select * from products order by 
	productname",conn);
   OleDbDataAdapter adapterOrderDetails  = new OleDbDataAdapter("Select * from [order details]",
	conn);
		
   //Fill the dataset with the retrieved data
   adapterCategories.Fill(myJoinedDataSet, "Categories");
   adapterProducts.Fill(myJoinedDataSet, "Products");
   adapterOrderDetails.Fill(myJoinedDataSet, "OrderDetails");
	
   //Create the relationships between the dataset's tables
   this.myJoinedDS.Relations.Add("CategoriesProducts", myJoinedDataSet.Tables
	["Categories"].Columns["CategoryID"], myJoinedDataSet.Tables["Products"].Columns
	["CategoryID"]);
   this.myJoinedDS.Relations.Add("ProductsOrderDetails", myJoinedDataSet.Tables
	["Products"].Columns["ProductID"], myJoinedDataSet.Tables["OrderDetails"].Columns
	["ProductID"]);
		
   //Set the data source for rptCategories
   rpt.DataSource = (myJoinedDataSet);
   rpt.DataMember = "Categories";
	
   //Run the report
   rpt.Run();
   
   //Close the connection
   conn.Close();
}

Adding code to set the subreport's data source control equal to the dataset's returned row collection for rptCategories

To write the code in Visual Basic or C#

Double-click on the detail section of rptCategories. This creates an event-handling method for rptCategories' Detail_Format event.

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

'Visual Basic
Dim counter As Integer
Private Sub Detail1_Format(ByVal sender As Object, ByVal e As System.EventArgs)Handles Detail1.Format
        Dim drc As DataRow
        If CType(Me.DataSource, DataSet).Tables("Categories").Rows.Count > counter Then
        	drc = CType(Me.DataSource, DataSet).Tables("Categories").Rows(counter)
        	counter = counter + 1
        End If
        Dim rpt As New rptProductsSub()
        rpt.DataSource = drc.GetChildRows(CType(Me.DataSource, DataSet).Relations("CategoriesProducts"))
        Me.SubReport1.Report = rpt
End Sub

//C#
int counter;
private void detail_Format(object sender, System.EventArgs eArgs)
{
	DataRow drc = null;
	if(((DataSet)this.DataSource).Tables["Categories"].Rows.Count>counter)
	{
		drc = ((DataSet)this.DataSource).Tables["Categories"]
			.Rows[counter];
		counter++;
	}
	rptProductsSub rpt = new rptProductsSub();
	rpt.DataSource = drc.GetChildRows(((DataSet)this.DataSource)
		.Relations["CategoriesProducts"]);
	this.SubReport1.Report = rpt;
}

Adding code to set the subreport's data source control equal to the dataset's returned row collection for rptProductsSub

To write the code in Visual Basic or C#

Double-click on the detail section of rptProductsSub. This creates an event-handling method for rptProductsSub's Detail_Format event.

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

'Visual Basic
Dim counter As Integer
Private Sub Detail1_Format(ByVal sender As Object, ByVal e As System.EventArgs)Handles Detail1.Format
	Dim rpt As New rptOrderDetailsSub()
	Dim drc() As DataRow
	drc = CType(Me.DataSource, DataRow())
	rpt.DataSource = drc(counter).GetChildRows("ProductsOrderDetails")
	Me.SubReport1.Report = rpt
	counter = counter + 1
End Sub

//C#
int counter;
private void detail_Format(object sender, System.EventArgs eArgs)
{
	rptOrderDetailsSub rpt = new rptOrderDetailsSub();
	DataRow[] drc = ((DataRow[])this.DataSource);
	rpt.DataSource = drc[counter].GetChildRows("ProductsOrderDetails");
	this.SubReport1.Report = rpt;
	counter++;
}

Viewing the report

To view the report

Add the ActiveReports viewer control to a Windows Form.
Add the code needed to set the viewer document equal to the report document of rptCategories. See Using the ActiveReports Windows Form Viewer for help.

You can quickly view your report at design time by clicking the Preview tab at the bottom of the designer.

ActiveReports for .NET 3 Online Help	Request technical support
Walkthrough: Using DataSets Containing Relationships with Subreports
See Also

Adding three ActiveReports to a Visual Studio project

Adding controls to display the data

Setting the data connections, data adapters and dataset relations

Adding code to set the subreport's data source control equal to the dataset's returned row collection for rptCategories

Adding code to set the subreport's data source control equal to the dataset's returned row collection for rptProductsSub

Viewing the report

See Also