Walkthrough Using Data Sets Containing Relationships with Subreports

In ActiveReports for .NET, data sets with relationships can be used to populate fields in subreports. Note Subreports will not render PageHeader/Footer sections.

This walkthrough illustrates how to use data sets 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 data set relations

• Adding the code needed to set the subreport control's data source equal to returned row collection of the data set

• Viewing the report

To complete the walkthrough, you must have access to the NorthWind database (NWind.mdb).

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

1. Open a new project in Visual Studio.

2. Click on Project > Add New Item.

3. Select ActiveReports file and rename the file rptCategories.

4. Click Open.

5. Click on Project > Add New Item.

6. Select ActiveReports file and rename the file rptProductsSub.

7. Click Open.

8. Click on Project > Add New Item.

9. Select ActiveReports file and rename the file rptOrderDetailsSub. 10. Click Open.

Adding controls to display the data To add controls to the reports

1. Add a GroupHeader/Footer section to rptCategories.

2. Make the following changes to the group header:

o Change the name to ghCategories o Change the UnderlayNext property to True o Change the GroupKeepTogether property to FirstDetail o Change the DataField property to CategoryName

3. Add the following controls to rptCategories, naming them as indicated:

Control

DataField

Name

Text/Caption

Section

Location

Control

DataField

Name

Text/Caption

Section

Label

(Empty string)

lblCategoryName Category Name

PageHeader

0, 0

Label

(Empty string)

lblProduct Product

PageHeader

1.25, 0

Label

(Empty string)

lblOrderDetails Order Details

PageHeader

2.75, 0

TextBox

CategoryName

txtCategoryName Category Name

GroupHeader

0, 0

Subreport

(Empty string)

SubReporti (Empty string)

Detail

1.25, 0

4. Add a GroupHeader/Footer section to rptProductsSub.

5. Make the following changes to the group header:

o Change the Name to ghProducts o Change the UnderlayNext property to True o Change the GroupKeepTogether property to FirstDetail o Change the DataField property to ProductName

6. Add the following controls to rptProductsSub, naming them as indicated:

Control DataField Name Text/Caption Section

ProductName txtProductName Product Name GroupHeader (Empty

TextBox Label

Label Label

Subreport string)

(Empty string) (Empty string)

(Empty string)

lblUnitPrice lblQuantity lblPrice

SubReporti

Unit Price

Quantity Price

Detail

Detail Detail

(Empty string) Detail

Location

1.4375, 0.1875

7. Add the following controls to the Detail section of rptOrderDetailsSub, naming them as indicated:

Control

DataField

TextBox UnitPrice TextBox Quantity TextBox Discount

Name txtUnitPrice txtQuantity txtDiscount

Text/Caption

Unit Price

Quantity

Discount

OutputFormat Location

Currency (Empty string) Currency

Setting the data connections, data adapters and data set relations To set the data connections

1. Add a Windows Form to your project.

2. Add an ActiveReports viewer control to Forml and set the Dock property to Fill.

To write the code for the viewer in Visual Basic

• Right-click on Forml, and click on View Code to display the code view for the form. At the top left of the code view for Forml, click the drop-down arrow and select (Base Class Events). At the top right of the code window, click the drop-down arrow and select Load. This creates an event-handling method for the Form1_Load event. Add code to the handler to:

• Set the data connections

• Create and fill the data adapters

• Create data set relationships

• Format the viewer to show the report when it is run

To write the code for the viewer in C#

• Click on the blue section at the top of Form1 to select the form. Click on the events icon in the Properties window to display available events for Form1. Double-click Load. This creates an event-handling method for the Form1_Load event. Add code to the handler to:

• Set the data connections

• Create and fill the data adapters

• Create data set 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]

Dim myJoinedDS As New DataSet()

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handle; Dim rpt As New rptCategories() Dim cnnString As String

Dim cnn As New OleDb.OleDbConnection()

cnnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\NWIND.MDB"

cnn = New OleDb.OleDbConnection(cnnString)

cnn.Open()

Dim catAd As New OleDb.OleDbDataAdapter("Select * from categories order by categoj Dim prodAd As New OleDb.OleDbDataAdapter("Select * from products order by producti Dim ODAd As New OleDb.OleDbDataAdapter("Select * from [order details]", cnn) catAd.Fill(myJoinedDS, "Categories") prodAd.Fill(myJoinedDS, "Products") ODAd.Fill(myJoinedDS, "OrderDetails")

Me.myJoinedDS.Relations.Add("CategoriesProducts", myJoinedDS.Tables("Categories"). Me.myJoinedDS.Relations.Add("ProductsOrderDetails", myJoinedDS.Tables("Products"). rpt.DataSource = (myJoinedDS) rpt.DataMember = "Categories" rpt.Run()

Me.Viewerl.Document = rpt.Document

public DataSet myJoinedDS = new DataSet();

private void Form1_Load(object sender, System.EventArgs e) {

rptCategories rpt = new rptCategories();

string cnnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\NWIND.MDB";

OleDbConnection cnn=new OleDbConnection(cnnString);

OleDbDataAdapter catAd = new OleDbDataAdapter("Select * from categories order b OleDbDataAdapter prodAd = new OleDbDataAdapter("Select * from products order by OleDbDataAdapter ODAd = new OleDbDataAdapter("Select * from [order details]",cn catAd.Fill(myJoinedDS, "Categories"); prodAd.Fill(myJoinedDS, "Products"); ODAd.Fill(myJoinedDS, "OrderDetails");

this.myJoinedDS.Relations.Add("CategoriesProducts", myJoinedDS.Tables["Categorie this.myJoinedDS.Relations.Add("ProductsOrderDetails", myJoinedDS.Tables["Product rpt.DataSource = (myJoinedDS); rpt.DataMember = "Categories"; rpt.Run();

this.viewerl.Document = rpt.Document;

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

To write the code in Visual Basic

• Right-click in any section of the design window of rptCategories, and click on View Code to display the code view for the report. At the top left of the code view for rptCategories, click the drop-down arrow and select Detail. At the top right of the code window, click the drop-down arrow and select Format. This creates an event-handling method for rptCategories' Detail_Format event.

To write the code in C#

• Click in the Detail section of rptCategories to select the section. Click on the events icon in the Properties window to display available events for the section. Double-click Format. 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 cnt As Integer

Private Sub Detail_Format(ByVal sender As Object, ByVal e As System.EventArgs) Handles Dim drCategories As DataRow

If CType(Me.DataSource, DataSet).Tables("Categories").Rows.Count > cnt Then drCategories = CType(Me.DataSource, DataSet).Tables("Categories").Rows(cnt cnt = cnt + 1 End If

Dim rpt As New rptProductsSub()

rpt.DataSource = drCategories.GetChildRows(CType(Me.DataSource, DataSet).Relat Me.SubReport1.Report = rpt

End Sub

int cnt;

private void Detail_Format(object sender, System.EventArgs eArgs) {

DataRow drCategories = null;

if(((DataSet)this.DataSource).Tables["Categories"].Rows.Count>cnt) {

drCategories = ((DataSet)this.DataSource).Tables["Categories"].Rows cnt++;

rptProductsSub rpt = new rptProductsSub();

rpt.DataSource = drCategories.GetChildRows(((DataSet)this.DataSource).Relatic this.SubReport1.Report = rpt;

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

To write the code in Visual Basic

• Right-click in any section of the design window of rptProductsSub, and click on View Code to display the code view for the report. At the top left of the code view for rptProductsSub, click the drop-down arrow and select Detail. At the top right of the code window, click the drop-down arrow and select Format. This creates an event-handling method for rptProductsSub's Detail_Format event.

To write the code in C#

• Click in the Detail section of rptProductsSub to select the section. Click on the events icon in the Properties window to display available events for the section. Double-click Format. 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 cnt As Integer

Private Sub Detail_Format(ByVal sender As Object, ByVal e As System.EventArgs) Handles Dim rpt As New rptOrderDetailsSub() Dim drc() As DataRow drc = CType(Me.DataSource, DataRow())

rpt.DataSource = drc(cnt).GetChildRows("ProductsOrderDetails") Me.SubReport1.Report = rpt cnt = cnt + 1

End Sub

int cnt;

private void Detail_Format(object sender, System.EventArgs eArgs) {

rptOrderDetailsSub rpt = new rptOrderDetailsSub(); DataRow[] drc = ((DataRow[])this.DataSource);

rpt.DataSource = drc[cnt].GetChildRows("ProductsOrderDetails");

this.SubReportl.Report = rpt;

Viewing the report To view the report

1. Add the ActiveReports viewer control to a Windows Form.

2. Add the code needed to set the viewer document equal to the report document. See Using the ActiveReports WinForm Viewer for help.

0 0

Post a comment