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