1. Create a new Visual Studio project.
2. From the Project menu, select Add New Item.
3. In the Add New Item dialog that appears, select ActiveReports 9 Section Report (code-based) and in the Name field, rename the file as rptSimpleScript.
4. Click the Add button to open a new section report in the designer.

See Adding an ActiveReport to a Project for information on adding different report layouts.

Note: The following steps are just for convenience so that the fields list in the Report Explorer can be populated at design time.

1. In the Report Data Source dialog, on the OLE DB tab, next to Connection String, click the Build button.
2. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button to move to the Connection tab.
3. Click the ellipsis (...) button to browse to your database, for example the NWind.mdb sample database. Click Open once you have selected the appropriate database path.
4. Click the Test Connection button to see if you have successfully connected to the database.
5. Click OK to close the Data Link Properties window and return to the Report Data Source dialog. Notice that the Connection String field gets filled automatically.
6. In the Query field on the OLE DB tab, enter the following SQL query.

   SQL Query	Copy Code
   SELECT * FROM categories INNER JOIN products ON categories.categoryid = products.categoryid ORDER BY products.categoryid, products.productid

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

1. Right-click the design surface of the report and select Insert then Group Header/Footer to add group header and footer sections to your report.
2. Increase the group header section's height so that you have room to work.
3. With the GroupHeader section selected, go to the Properties Window to set the following properties.

   Property Name	Property Value
   BackColor	LightBlue
   CanShrink	True
   DataField	CategoryName
   GroupKeepTogether	All
   KeepTogether	True

4. From the toolbox, drag the following controls to the GroupHeader section and set the properties of each control as indicated.

   TextBox1

   Property Name	Property Value
   DataField	CategoryName
   Location	0, 0 in
   Size	6.5, 0.2 in
   BackColor	CadetBlue
   Font Bold	True
   Font Size	12

   TextBox2

   Property Name	Property Value
   DataField	Description
   Location	0, 0.2 in
   Size	6.5, 0.2 in
   BackColor	CadetBlue

   Label1

   Property Name	Property Value
   Text	Product Name
   Location	0, 0.4 in
   Size	1, 0.2 in
   Font Bold	True

   Label2

   Property Name	Property Value
   Text	Units in Stock
   Location	5.5, 0.4 in
   Size	1, 0.2 in
   Font Bold	True
   Alignment	Right

5. From the toolbox, drag the following controls onto the detail section and set the properties of each as indicated.

   TextBox1

   Property Name	Property Value
   DataField	ProductName
   Location	0, 0 in
   Size	5.5, 0.2 in

   TextBox2

   Property Name	Property Value
   DataField	UnitsInStock
   Location	5.5, 0 in
   Size	1, 0.2 in
   Alignment	Right

6. Click just below the fields to select the Detail section, and in the Properties Window, set the CanShrink property to True to eliminate white space in the rendered report. 
7. In the Detail section, select both TextBox1 and TextBox2, right-click and select Format Border.
   • Select DarkCyan in the color combo box.
   • Select the solid line in the Line Styles pane.
   • Click the bottom edge in the Preview pane.
   • Click the OK button to add a solid cyan line to the bottom edge of the text boxes.
8. Increase the group footer section's height so that you have room to work.
9. With the GroupFooter section selected, go to the properties window and set the following properties.

   Property Name	Property Value
   BackColor	PaleGreen
   CanShrink	True

10. From the toolbox, drag the following controls to the GroupFooter Section and set the properties of each control as indicated.

    TextBox1

    Property Name	Property Value
    DataField	TotalLabel
    Location	2.5, 0 in
    Size	3, 0.2 in
    Font Bold	True

    TextBox2

    Property Name	Property Value
    DataField	ProductName
    Location	5.5, 0 in
    SummaryType	Subtotal
    SummaryFunc	Count
    SummaryRunning	Group
    SummaryGroup	GroupHeader1
    Alignment	Right

    Label1

    Property Name	Property Value
    Location	0, 0.25 in
    Size	6.5, 0.2 in
    BackColor	White (creates white space after the subtotal)
    Text	Note: Delete the default text.

1. Click in the grey area below the report to select it, and in the Properties Window, change the ScriptLanguage property for the report to the scripting language you want to use. The default setting is C#.
2. Click the Script tab located at the bottom edge of the report designer to access the scripting editor. Add the scripting code.

The following example shows what the scripting code looks like.

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 script in Visual Basic.NET.

Private Shared m_reader As System.Data.OleDb.OleDbDataReader
Private Shared m_cnn As System.Data.OleDb.OleDbConnection

Public Sub ActiveReport_ReportStart()
  'Set up a data connection for the report
  rpt.DataSource = ""  
  Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\[User Folder]\Documents\GrapeCity Samples\ActiveReports 9\Data\NWIND.mdb"
  Dim sqlString As String = "SELECT * FROM categories INNER JOIN products ON categories.categoryid = products.categoryid ORDER BY products.categoryid, products.productid"
     
  m_cnn = new System.Data.OleDb.OleDbConnection(connString)
  Dim m_Cmd As System.Data.OleDb.OleDbCommand = new System.Data.OleDb.OleDbCommand(sqlString, m_cnn)
   
  If m_cnn.State = System.Data.ConnectionState.Closed Then
     m_cnn.Open
  End If
  m_reader = m_Cmd.ExecuteReader
End Sub

Public Sub ActiveReport_DataInitialize()
  'Add data fields to the report
  rpt.Fields.Add("CategoryID")
  rpt.Fields.Add("CategoryName")
  rpt.Fields.Add("ProductName")
  rpt.Fields.Add("UnitsInStock")
  rpt.Fields.Add("Description")
  rpt.Fields.Add("TotalLabel")
End Sub

Public Function ActiveReport_FetchData(ByVal eof As Boolean) As Boolean
  Try
    m_reader.Read
    'Populated the fields with data from the data reader
    rpt.Fields("CategoryID").Value = m_reader("categories.CategoryID")
    rpt.Fields("CategoryName").Value = m_reader("CategoryName")
    rpt.Fields("ProductName").Value = m_reader("ProductName")
    rpt.Fields("UnitsInStock").Value = m_reader("UnitsInStock")
    rpt.Fields("Description").Value = m_reader("Description")
    'Concatenate static text with data
    rpt.Fields("TotalLabel").Value = "Total Number of " + m_reader("CategoryName")+ " Products:"
    eof = False
  Catch
    'If the end of the data file has been reached, tell the FetchData function
    eof = True
  End Try
 Return eof
End Function

Public Sub ActiveReport_ReportEnd()
  'Close the data reader and connection
  m_reader.Close
  m_cnn.Close
End Sub

To write the script in C#.

//C#
private static System.Data.OleDb.OleDbDataReader m_reader;
private static System.Data.OleDb.OleDbConnection m_cnn;

public void ActiveReport_ReportStart()
{
   //Set up a data connection for the report
   rpt.DataSource = "";   
   string m_cnnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\[User Folder]\Documents\GrapeCity Samples\ActiveReports 9\Data\NWIND.mdb";
   string sqlString = "SELECT * FROM categories INNER JOIN products ON categories.categoryid = products.categoryid ORDER BY products.categoryid, products.productid";
   m_cnn = new System.Data.OleDb.OleDbConnection(m_cnnString);
   System.Data.OleDb.OleDbCommand m_Cmd = new System.Data.OleDb.OleDbCommand(sqlString,m_cnn);
  
   if(m_cnn.State == System.Data.ConnectionState.Closed)
   {
      m_cnn.Open();
   }
   m_reader = m_Cmd.ExecuteReader();
}

public void ActiveReport_DataInitialize()
{
   //Add data fields to the report
   rpt.Fields.Add("CategoryID");
   rpt.Fields.Add("CategoryName");
   rpt.Fields.Add("ProductName");
   rpt.Fields.Add("UnitsInStock");
   rpt.Fields.Add("Description");
   rpt.Fields.Add("TotalLabel");
}

public bool ActiveReport_FetchData(bool eof)
{
   try
   {
     m_reader.Read();
     //Populated the fields with data from the data reader
     rpt.Fields["CategoryID"].Value = m_reader["categories.CategoryID"].ToString();
     rpt.Fields["CategoryName"].Value = m_reader["CategoryName"].ToString();
     rpt.Fields["ProductName"].Value = m_reader["ProductName"].ToString();
     rpt.Fields["UnitsInStock"].Value = m_reader["UnitsInStock"].ToString();
     rpt.Fields["Description"].Value = m_reader["Description"].ToString();
     //Concatenate static text with data
     rpt.Fields["TotalLabel"].Value = "Total Number of " + m_reader["CategoryName"].ToString() + " Products:";
     eof = false;
   }
   catch
   {
     //If the end of the data file has been reached, tell the FetchData function
     eof = true;
   }
   return eof;
}

public void ActiveReport_ReportEnd()
{
   //Close the data reader and connection
   m_reader.Close();
   m_cnn.Close();
}

1. From the Report menu, select Save Layout.
2. In the Save dialog that appears, enter a name for the report, i.e. rptScript.rpx, and click the Save button.

• Click the preview tab to view the report at design time.

OR

• Open the report in the Viewer. See Using the Viewer for further information.