ActiveReports 14 .NET Edition
ActiveReports 14 User Guide / Samples and Walkthroughs / Walkthroughs / Section Report Walkthroughs / Script / Script for Simple Reports
In This Topic
    Script for Simple Reports
    In This Topic

    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:

    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.
    Note: This walkthrough uses the the Northwind database. The NWIND.mdb file can be downloaded from GitHub: ..\Samples14\Data\NWIND.mdb.

    When you have finished this walkthrough, you will have a report that looks similar to the following at design time and at run time.

    Design-Time Layout

    Report with Embedded Script - Design time Layout

    Report with Embedded Script - Design time Layout

    Run-Time Layout

    Report with Embedded Script - Run time Layout

    Report with Embedded Script - Run time Layout

    To add an ActiveReport to the Visual Studio project

    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 14 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 Quick Start for information on adding different report layouts.

    To connect the report to a data source

    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.

    To create a layout for the report

    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.

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

    Visual Basic.NET script. Paste in the script editor window.
    Copy Code
    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=[User Folder]\Samples14\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# script. Paste in the script editor window.
    Copy Code
    //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]\Samples14\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.

    To view the report

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

    OR