Show All

Hide All

ActiveReports allows you to use scripting to embed code in reports saved to the XML-based RPX file format. By embedding script in reports saved as RPX files, you can later load, run, and display reports directly in the viewer control without using the designer. This walkthrough illustrates how to include scripting in a simple report.

This walkthrough is split into the following activities:

• Temporarily connecting the report to a data source
• Adding controls to a report to display data
• Adding scripting to supply data for the controls
• Saving the report to an RPX file

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 Windows 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 temporarily connect the report to a data source

1. Add an ActiveReports 6 File (xml-based) to a Visual Studio project and rename it rptScript.

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

2. Click the gray report DataSource icon on the Detail section band to open the Report Data Source dialog.
   
3. On the OLE DB tab, next to Connection String, click the Build button.
4. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button.
5. Click the ellipsis (...) button to browse to the Northwind database. Click Open once you have selected the appropriate access path.
6. Click OK to close the window and fill in the Connection String field.
7. In the Query field, 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

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

To add controls to the report

1. Right-click on 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. In the Properties Window, make the following changes to the group header:
   • BackColor: LightBlue
   • CanShrink: True
   • DataField: CategoryID
   • GroupKeepTogether: All
   • KeepTogether: True
4. Add the following controls to the GroupHeader section and set the properties as indicated.

   Group header controls

   Control	DataField	Text	Location	Size	Miscellaneous
   TextBox	CategoryName		0, 0 in	6.5, 0.2 in	BackColor = CadetBlue Font Style = Bold Font Size = 12
   TextBox	Description		0, 0.2 in	6.5, 0.2 in	BackColor = CadetBlue
   Label		Product Name	0, 0.4 in	1, 0.2 in	Font Style = Bold
   Label		Units in Stock	5.5, 0.4 in	1, 0.2 in	Font Style = Bold Alignment = Right

5. In the Report Explorer, expand the Fields node, then the Bound node, and drag the following fields onto the detail section, setting the properties as indicated.

   Detail section fields

   Control	DataField	Location	Size	Alignment
   TextBox	ProductName	0, 0 in	5.5, 0.198 in
   TextBox	UnitsInStock	5.5, 0 in	1, 0.198 in	Right

6. Set the CanShrink property of the detail section to True.
7. Select both of the text boxes in the detail section, right-click and select Format Border.
   • Select dark cyan 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. Make the following changes to the group footer:
   • BackColor: PaleGreen
   • CanShrink: True
10. Add the following controls to the GroupFooter section, setting the properties as indicated.

    Group footer controls

    Control	DataField	Size	Miscellaneous	Location
    TextBox	TotalLabel	3, 0.198 in	Font Style = Bold	2.5, 0
    TextBox	ProductName		SummaryType = Subtotal SummaryFunc = Count SummaryRunning = Group SummaryGroup = GroupHeader1 Alignment = Right	5.5, 0
    Label		6.5, 0.198 in	BackColor = White (creates white space after the subtotal) Delete default text from Text property	0, 0.25

To add scripting to the report to supply data for the controls

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.

   

3. 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:\Program Files\GrapeCity\ActiveReports 6\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:\Program Files\GrapeCity\ActiveReports 6\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();
}

To save the report to an XML-based RPX file

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.