Show All

Hide All

ActiveReports allows you to set up grouping in unbound reports. When setting up grouping, the group header's DataField property is used to retrieve the grouping data from the database in the same manner as a textbox's DataField property. This walkthrough illustrates how to set up grouping in an unbound report.

This walkthrough is split into the following activities:

• Adding code to connect the report to a data source
• Adding controls to contain the data
• Using the DataInitialize event to add fields to the report's fields collection
• Using the FetchData event to populate the report fields
• Adding code to close the connection to the data source

Tip: For basic steps like adding a report to a Visual Studio project and viewing a report, please see the Basic Data Bound Reports walkthrough.

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 operating system, a copy is located in C:\Program Files (x86)\GrapeCity\ActiveReports 6\Data\NWIND.MDB).

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

To add code to connect the report to a data source

1. Double-click the gray area below the report. This creates an event-handling method for the report's ReportStart event.
2. Add code to the handler to:
   • Set the data source connection string
   • Set the data source SQL query
   • Open the connection and retrieve the data with the data reader

The following examples show what the code for the method looks like in Visual Basic.NET and C#.

To write the code in Visual Basic.NET

Dim connection As System.Data.OleDb.OleDbConnection
Dim reader As System.Data.OleDb.OleDbDataReader 

'Create the data connection and change the data source path as necessary 
Dim connectionString As String 
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\GrapeCity\ActiveReports 6\Data\NWIND.MDB" 
connection = New System.Data.OleDb.OleDbConnection(connectionString) 
connection.Open() 
    
Dim sqlString As String 
sqlString = "SELECT * FROM categories INNER JOIN products ON categories.categoryid= products.categoryid ORDER BY categories.CategoryID" 
Dim command As New System.Data.OleDb.OleDbCommand(sqlString, connection) 
'Retrieve data 
reader = command.ExecuteReader()

To write the code in C#

private System.Data.OleDb.OleDbConnection connection;
private System.Data.OleDb.OleDbDataReader reader;

//Create the data connection and change the data source path as necessary
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\GrapeCity\ActiveReports 6\Data\NWIND.MDB";
connection=new System.Data.OleDb.OleDbConnection(connectionString);
connection.Open();

string sqlString = "SELECT * FROM categories INNER JOIN products ON categories.categoryid = products.categoryid ORDER BY categories.CategoryID";
System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(sqlString, connection);   

//Retrieve data
reader = command.ExecuteReader();

To add controls to the report to contain data

1. Back on the design surface of the report, right-click and select Insert, thenGroup Header/Footer to add group header and footer sections.
2. Select the group header and make the following changes in the Properties Window.
   • Name: ghCategories
   • BackColor: Silver
   • CanShrink: True
   • DataField: CategoryID
   • GroupKeepTogether: All
   • KeepTogether: True
3. Select the group footer, and in the Properties Window, change the Name property to gfCategories.
4. Select the detail section, and in the Properties Window, change the CanShrink property to True.
5. Add the following controls to the GroupHeader section (drag the bottom edge of the section down to display all of the controls):

   GroupHeader controls

   Control	DataField	Name	Text	Miscellaneous	Location
   TextBox	CategoryName	txtCategoryName	Category Name	ForeColor = Blue BackColor = Silver Font size = 12 Size = 2, 0.2 in	0, 0 in
   TextBox	Description	txtDescription	Description	Size = 6, 0.198 in	0, 0.3 in
   Label		lblProductName	Product Name	Bold	0, 0.6 in
   Label		lblUnitsInStock	Units In Stock	Bold Alignment = Right	4.4, 0.6 in

6. Add the following controls to the Detail section:

   Detail controls

   Control	DataField	Name	Text	Miscellaneous	Location
   TextBox	ProductName	txtProductName	Product Name	Size = 4, 0.198 in	0, 0 in
   TextBox	UnitsInStock	txtUnitsInStock	Units In Stock	Alignment = Right	4.4, 0 in

7. Add the following controls to the GroupFooter section:

   GroupFooter controls

   Control	DataField	Name	Text	Miscellaneous	Location
   Label	TotalLabel	lblTotalLabel		Size = 2.4, 0.198 in	2, 0 in
   TextBox	ProductName	txtTotalItems	Total Items	SummaryType = SubTotal SummaryFunc = Count SummaryRunning = Group SummaryGroup = ghCategories Alignment = Right	4.4, 0 in
   Line		Line1		LineWeight = 3	X1 = 1.88 in X2 = 6.44 in Y1 = 0 in Y2 = 0 in

8. Right-click the PageHeader section and select Delete. 

To add fields using the DataInitialize event

Warning: Do not access the Fields collection outside the DataInitialize and FetchData events. Accessing the Fields collection outside of these events is not supported, and has unpredictable results.

To write the code in Visual Basic

Fields.Add("CategoryID") 
Fields.Add("CategoryName") 
Fields.Add("ProductName") 
Fields.Add("UnitsInStock") 
Fields.Add("Description") 
Fields.Add("TotalLabel")

To write the code in C#

Fields.Add("CategoryID");
Fields.Add("CategoryName");
Fields.Add("ProductName");
Fields.Add("UnitsInStock");
Fields.Add("Description");
Fields.Add("TotalLabel");

To populate the fields using the FetchData event

To write the code in Visual Basic

Try
   reader.Read()
   Me.Fields("CategoryID").Value = reader("categories.CategoryID")
   Me.Fields("CategoryName").Value = reader("CategoryName")
   Me.Fields("ProductName").Value = reader("ProductName")
   Me.Fields("UnitsInStock").Value = reader("UnitsInStock")
   Me.Fields("Description").Value = reader("Description")
   Me.Fields("TotalLabel").Value = "Total Number of " + reader("CategoryName") + ":"
   eArgs.EOF = False
Catch
   eArgs.EOF = True
End Try

To write the code in C#

try
   {
      reader.Read();
      Fields["CategoryID"].Value = reader["categories.CategoryID"].ToString();
      Fields["CategoryName"].Value = reader["CategoryName"].ToString();
      Fields["ProductName"].Value = reader["ProductName"].ToString();
      Fields["UnitsInStock"].Value = reader["UnitsInStock"].ToString();
      Fields["Description"].Value = reader["Description"].ToString();
      Fields["TotalLabel"].Value = "Total Number of " + reader["CategoryName"].ToString() + ":";
      eArgs.EOF = false;
   }
catch
   {
      eArgs.EOF = true;
   }

Adding code to close the connection to the data source

To write the code in Visual Basic

reader.Close() 
connection.Close()

To write the code in C#

reader.Close();
connection.Close();