ActiveReports Developer 7
Script for Subreports
See Also Support Forum
ActiveReports Developer 7 > ActiveReports Developer Guide > Samples and Walkthroughs > Walkthroughs > Section Report Walkthroughs > Layout Files with Embedded Script > Script for Subreports

Glossary Item Box

ActiveReports Developer allows you to use scripting to permit reports saved to an XML file to contain code. By including scripting when reports are saved into XML, the reports later can be loaded, run, and displayed directly to the viewer control without needing to use the designer.

This walkthrough illustrates how to use scripting when creating a subreport.

This walkthrough is split up 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 Northwind database. By default, in ActiveReports, the Northwind.mdb file is located at [User Documents folder]\ComponentOne Samples\ActiveReports Developer 7\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 runtime.

Design Time Layout (main report)


Runtime Layout (main report)


ShowTo 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 7 Section Report (xml-based) and in the Name field, rename the file as rptMain.
  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.

ShowTo 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 Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID ORDER BY CompanyName, OrderDate
  7. Click OK to save the data source and return to the report design surface.

ShowTo add a report for the subreport

  1. From the Project menu, select Add New Item.
  2. In the Add New Item dialog that appears, select ActiveReports 7 Section Report (xml-based) and in the Name field, rename the file as rptSub.
  3. Click the Add button to open a new section report in the designer.
  4. Right-click the PageHeader or PageFooter section and select Delete. Subreports do not render these sections, so deleting them saves processing time.
  5. Click in the grey area below the report to select it, and in the Properties window, change the report's ShowParameterUI property to False. This prevents the subreport from requesting a parameter from the user.

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

ShowTo connect the subreport 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 [order details] inner join products on [order details].productid = products.productid
  7. Click OK to save the data source and return to the report design surface.

ShowTo create a layout for the main report

  1. Right-click the design surface of rptMain and select Insert then Group Header/Footer to add group header and footer sections to the report.
  2. In the Properties Window, make the following changes to the group header.
    Property Name Property Value
    Name ghCompanies
    BackColor LemonChiffon
    CanShrink True
    DataField CompanyName
    GroupKeepTogether All
    KeepTogether True
  3. In the Report Explorer, expand the Fields node, then the Bound node. Drag the CompanyName field onto ghCompanies and in the Properties window, set the properties as follows.
    Property Name Property Value
    Size 4, 0.2 in
    Location 0, 0 in
    Font Bold True
    Font Size 12
  4. Right-click the design surface of rptMain and select Insert then Group Header/Footer to add the second group header and footer sections to the report.
  5. In the Properties Window, make the following changes to the second group header.
    Property Name Property Value
    Name ghOrders
    BackColor LightYellow
    CanShrink True
    DataField OrderDate
    GroupKeepTogether All
    KeepTogether True
  6. From the toolbox, drag three TextBox controls onto ghOrders and set the properties for each control as follows.

    ShowTextBox1

    Property Name Property Value
    DataField OrderDate
    Location 1.1, 0 in
    Size 1, 0.2 in
    OutputFormat MM/dd/yy

    ShowTextBox2

    Property Name Property Value
    DataField RequiredDate
    Location 3.5, 0 in
    Size 1, 0.2 in
    OutputFormat MM/dd/yy

    ShowTextBox3

    Property Name Property Value
    DataField ShippedDate
    Location 5.5, 0 in
    Size 1, 0.2 in
    OutputFormat MM/dd/yy
    Alignment Right
  7. From the toolbox, drag three Label controls onto ghOrders and set the properties for each control as follows.

    ShowLabel1

    Property Name Property Value
    Location 0, 0 in
    Size 1, 0.2 in
    Text Ordered:
    Font Bold True

    ShowLabel2

    Property Name Property Value
    Location 2.5, 0 in
    Size 1, 0.2 in
    Text Required:
    Font Bold True

    ShowLabel3

    Property Name Property Value
    Location 4.8, 0 in
    Size 0.65, 0.2 in
    Text Shipped:
    FontStyle Bold
  8. Select the Detail section and in the Properties window, set the CanShrink property to True.
  9. From the toolbox, drag the Subreport control onto the Detail section and in the Properties window, set the properties as follows.
    Property Name Property Value
    ReportName C:\full project path\rptSub.rpx
    Name SubReport1
    Size 6.5, 1 in
    Location 0, 0 in
    GroupKeepTogether All
    KeepTogether True

ShowTo create a layout for the subreport

  1. Right-click the design surface of rptSub and select Insert then Group Header/Footer to add group header and footer sections to the report.
  2. In the Properties window, make the following changes to the group header.
    Property Name Property Value
    Name ghOrderDetails
    BackColor LightSteelBlue
    CanShrink True
    DataField OrderID
  3. From the toolbox, drag four label controls to ghOrderDetails and set the properties for each label as follows.

    ShowLabel1

    Property Name Property Value
    Location 0, 0 in
    Text Product Name
    Font Bold True
    Alignment Left

    ShowLabel2

    Property Name Property Value
    Location 3.25, 0 in
    Text Quantity
    Font Bold True
    Alignment Right

    ShowLabel3

    Property Name Property Value
    Location 4.4, 0 in
    Text Unit Price
    Font Bold True
    Alignment Right

    ShowLabel4

    Property Name Property Value
    Location 5.5, 0 in
    Text Discount
    Font Bold True
    Alignment Right
  4. From the toolbox, drag four Line controls to ghOrderDetails and set the properties for each line as follows.

    ShowLine1

    Property Name Property Value
    X1 3.2
    X2 3.2
    Y1 0
    Y2 0.2

    ShowLine2

    Property Name Property Value
    X1 4.3
    X2 4.3
    Y1 0
    Y2 0.2

    ShowLine3

    Property Name Property Value
    X1 5.45
    X2 5.45
    Y1 0
    Y2 0.2

    ShowLine4

    Property Name Property Value
    X1 0
    X2 6.5
    Y1 0.2
    Y2 0.2
  5. Click the Detail section and in the Properties window, set the following properties.
    Property Name Property Value
    BackColor Gainsboro
    CanShrink True
  6. From the toolbox, drag four TextBox controls onto onto the Detail section and set the properties as follows.

    ShowTextBox1

    Property Name Property Value
    DataField ProductName
    Location 0, 0 in
    Size 3.15, 0.2 in
    Alignment Left

    ShowTextBox2

    Property Name Property Value
    DataField Quantity
    Location 3.25, 0 in
    Size 1, 0.2 in
    Alignment Right

    ShowTextBox3

    Property Name Property Value
    DataField Products.UnitPrice
    Location 4.4, 0 in
    Size 1, 0.2 in
    Alignment Right
    OutputFormat Currency

    ShowTextBox4

    Property Name Property Value
    DataField Discount
    Location 5.5, 0 in
    Size 1, 0.2 in
    Alignment Right
    OutputFormat Percentage
  7. From the toolbox, drag four Line controls to the Detail section and set the properties as follows.

    ShowLine5

    Property Name Property Value
    X1 3.2
    X2 3.2
    Y1 0
    Y2 0.2

    ShowLine6

    Property Name Property Value
    X1 4.3
    X2 4.3
    Y1 0
    Y2 0.2

    ShowLine7

    Property Name Property Value
    X1 5.45
    X2 5.45
    Y1 0
    Y2 0.2

    ShowLine8

    Property Name Property Value
    X1 0
    X2 6.5
    Y1 0.2
    Y2 0.2

ShowTo embed script in the main report

  1. Change the ScriptLanguage property for the report to the appropriate scripting language. The default setting is C#.
  2. Click the Script tab located below the report designer to access the scripting editor.
  3. Embed script to set the data source for the main report and pass data into the subreport.

The following example shows what the script looks like.

ShowTo write the script in Visual Basic.NET

Visual Basic.NET script. Paste in the script editor window. Copy Code 
Dim rptSub As GrapeCity.ActiveReports.SectionReport
Sub ActiveReport_ReportStart
  'Create a new instance of the generic report
  rptSub = new GrapeCity.ActiveReports.SectionReport()
  'Load the rpx file into the generic report
  rptSub.LoadLayout(me.SubReport1.ReportName)
  'Connect data to the main report
  Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\[User Folder]\Documents\ComponentOne Samples\ActiveReports Developer 7\Data\NWIND.mdb;Persist Security Info=False"
  Dim sqlString As String = "Select * from orders inner join customers on orders.customerid = customers.customerid order by CompanyName,OrderDate"
  Dim ds As new GrapeCity.ActiveReports.Data.OleDBDataSource()
  ds.ConnectionString = connString
  ds.SQL = sqlString
  rpt.DataSource = ds
End Sub

Sub Detail1_Format
  Dim rptSubCtl As GrapeCity.ActiveReports.SubReport = me.SubReport1
  Dim childDataSource As New GrapeCity.ActiveReports.Data.OleDBDataSource()
  childDataSource.ConnectionString = CType(rpt.DataSource, GrapeCity.ActiveReports.Data.OleDBDataSource).ConnectionString
  'Set a parameter in the SQL query
  childDataSource.SQL = "Select * from [order details] inner join products on [order details].productid = products.productid where [order details].orderid = <%OrderID%>"
  'Pass the data to the subreport
  rptSub.DataSource = childDataSource
  'Display rptSub in the subreport control
  rptSubCtl.Report = rptSub
End Sub

ShowTo write the script in C#

C# code. Paste in the script editor window. Copy Code 
GrapeCity.ActiveReports.SectionReport rptSub;
public void Detail1_Format()
{
  GrapeCity.ActiveReports.SectionReportModel.SubReport rptSubCtl = this.SubReport1;
  GrapeCity.ActiveReports.Data.OleDBDataSource childDataSource = new GrapeCity.ActiveReports.Data.OleDBDataSource();
  childDataSource.ConnectionString = ((GrapeCity.ActiveReports.Data.OleDBDataSource) rpt.DataSource).ConnectionString;
  //Set a parameter in the SQL query
  childDataSource.SQL = "Select * from [order details] inner join products on [order details].productid = products.productid where [order details].orderid = <%OrderID%>";
  //Pass the data to the subreport
  rptSub.DataSource = childDataSource;
  //Display rptSub in the subreport control
  rptSubCtl.Report = rptSub;
}

public void ActiveReport_ReportStart()
{
  //Create a new instance of the generic report
  rptSub = new GrapeCity.ActiveReports.SectionReport();
  //Load the rpx file into the generic report
  rptSub.LoadLayout(this.SubReport1.ReportName);
  //Connect data to the main report
  string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\[User Folder]\Documents\ComponentOne Samples\ActiveReports Developer 7\Data\NWIND.mdb;Persist Security Info=False";
  string sqlString = "Select * from orders inner join customers on orders.customerid = customers.customerid order by CompanyName,OrderDate";
  GrapeCity.ActiveReports.Data.OleDBDataSource ds = new GrapeCity.ActiveReports.Data.OleDBDataSource();
  ds.ConnectionString = connString;
  ds.SQL = sqlString;
  rpt.DataSource = ds;
}

ShowTo view the report

  • 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 on how to load the xml-based section report onto the viewer.

See Also

©2014. ComponentOne, a division of GrapeCity. All rights reserved.