Show All

Hide All

ActiveReports 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:

• Temporarily connecting the main report to a data source
• Connecting the subreport to a data source
• Adding controls to each report to display data
• Adding the scripting code for rptMain
• Loading an xml-based report into the viewer

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 finished this walkthrough, you will have a report that looks similar to the following.

To temporarily connect the main report to a data source

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

   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 Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID ORDER BY CompanyName, OrderDate

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

To temporarily connect the subreport to a data source

1. Add a second ActiveReports 6 (xml-based) File to the project and rename it rptSub.

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

2. Right-click the PageHeader or PageFooter section and select Delete. Subreports do not render these sections, so deleting them saves processing time.
3. 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.
4. Click the gray report DataSource icon on the Detail section band to open the Report Data Source dialog.
5. Click the Build button.
6. In the Data Link Properties window that appears, select Microsoft Jet 4.0 OLE DB Provider and click the Next button. 
7. Click the ellipsis button to browse to the NorthWind database. Click Open once you have selected the appropriate access path.
8. Click OK to close the window and fill in the Connection String field.
9. In the Query field, enter the following SQL query.

   SQL Query	Copy Code
   Select * from [order details] inner join products on [order details].productid = products.productid

10. Click OK to return to the report design surface.

To add controls to rptMain to display data

1. Right-click on 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:
   • 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 following field onto ghCompanies and set the properties as indicated.

   Group header ghCompanies field

   Field	Miscellaneous	Size	Location
   CompanyName	Font style = Bold Font Size = 12	4, 0.2 in	0, 0 in

4. Add a second GroupHeader/Footer section to rptMain.
5. Make the following changes to the group header:
   • Name: ghOrders
   • BackColor: LightYellow
   • CanShrink: True
   • DataField: OrderDate
   • GroupKeepTogether: All
   • KeepTogether: True
6. Drag the following fields and controls onto ghOrders and set the properties as indicated.

   ghOrders controls

   Control	DataField	Size	Text	Miscellaneous	Location
   TextBox	OrderDate	1, 0.198 in		OutputFormat = MM/dd/yy	1.13, 0 in
   TextBox	RequiredDate	1, 0.198 in		OutputFormat = MM/dd/yy	3.5, 0 in
   TextBox	ShippedDate	1, 0.198 in		OutputFormat = MM/dd/yy Alignment = Right	5.5, 0 in
   Label		1, 0.198 in	Ordered:	Font style = Bold	0, 0 in
   Label		1, 0.198 in	Required:	Font style = Bold	2.5, 0 in
   Label		0.65, 0.198 in	Shipped:	Font style = Bold	4.8, 0 in

7. Change the CanShrink property of the detail section to True.
8. Drag the following control onto the detail section and set the properties as indicated.

   Detail section control

   Control	ReportName	Name	Size	Location
   Subreport	C:\full project path\rptSub.rpx	SubReport1	6.5, 1 in	0, 0 in

To add controls to rptSub

1. Right-click on the design surface of rptSub and select Insert then Group Header/Footer to add group header and footer sections to the report.
2. Make the following changes to the group header:
   • Name: ghOrderDetails
   • BackColor: LightSteelBlue
   • CanShrink: True
   • DataField: OrderID
3. Add four label controls to ghOrderDetails and set the properties as indicated.

   ghOrderDetails labels

   Font style	Text	Alignment	Location
   Bold	Product Name	Left	0, 0
   Bold	Quantity	Right	3.25, 0
   Bold	Unit Price	Right	4.4, 0
   Bold	Discount	Right	5.5, 0

4. Add four line controls to ghOrderDetails and set the properties as indicated.

   ghOrderDetails line controls

   Name	X1	X2	Y1	Y2
   Line1	3.2	3.2	0	0.2
   Line2	4.3	4.3	0	0.2
   Line3	5.45	5.45	0	0.2
   Line4	0	6.5	0.2	0.2

5. Make the following changes to the detail section:
   • BackColor: Gainsboro
   • CanShrink: True
6. In the Report Explorer, expand the Fields node, then the Bound node. Drag the following fields onto the detail section and set the properties as indicated.

   Detail section fields

   Field	Size	Alignment	OutputFormat	Location
   ProductName	3.15, 0.198 in	Left		0, 0 in
   Quantity	1, 0.198 in	Right		3.25, 0 in
   Products.UnitPrice	1, 0.198 in	Right	Currency	4.4, 0 in
   Discount	1, 0.198 in	Right	0%	5.5, 0 in

7. Add four line controls to the detail section and set the properties as follows (or copy and paste them from ghOrderDetails):

   Detail section lines

   Name	X1	X2	Y1	Y2
   Line5	3.2	3.2	0	0.2
   Line6	4.3	4.3	0	0.2
   Line7	5.45	5.45	0	0.2
   Line8	0	6.5	0.2	0.2

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

To write the script in Visual Basic.NET

Dim rptSub As New DataDynamics.ActiveReports.ActiveReport()
Sub ActiveReport_ReportStart
  'Create a new instance of the generic report
  rptSub = new DataDynamics.ActiveReports.ActiveReport()
  'Load the rpx file into the generic report
  Dim xtr As New System.Xml.XmlTextReader(me.SubReport1.ReportName)
  rptSub.LoadLayout(xtr)
  xtr.Close()
  'Connect data to the main report
  Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\GrapeCity\ActiveReports 6\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 DataDynamics.ActiveReports.DataSources.OleDBDataSource()
  ds.ConnectionString = connString
  ds.SQL = sqlString
  rpt.DataSource = ds
End Sub

Sub Detail1_Format
  Dim rptSubCtl As DataDynamics.ActiveReports.SubReport = me.SubReport1
  Dim childDataSource As New DataDynamics.ActiveReports.DataSources.OleDBDataSource()
  childDataSource.ConnectionString = CType(rpt.DataSource, DataDynamics.ActiveReports.DataSources.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
  

To write the script in C#

DataDynamics.ActiveReports.ActiveReport rptSub;
public void Detail1_Format()
{
  DataDynamics.ActiveReports.SubReport rptSubCtl = this.SubReport1;
  DataDynamics.ActiveReports.DataSources.OleDBDataSource childDataSource = new DataDynamics.ActiveReports.DataSources.OleDBDataSource();
  childDataSource.ConnectionString = ((DataDynamics.ActiveReports.DataSources.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 DataDynamics.ActiveReports.ActiveReport();
  //Load the rpx file into the generic report
  System.Xml.XmlTextReader xtr = new System.Xml.XmlTextReader(this.SubReport1.ReportName);
  rptSub.LoadLayout(xtr);
  xtr.Close();
  //Connect data to the main report
  string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\GrapeCity\ActiveReports 6\Data\NWIND.MDB;Persist Security Info=False";
  string sqlString = "Select * from orders inner join customers on orders.customerid = customers.customerid order by CompanyName,OrderDate";
  DataDynamics.ActiveReports.DataSources.OleDBDataSource ds = new DataDynamics.ActiveReports.DataSources.OleDBDataSource();
  ds.ConnectionString = connString;
  ds.SQL = sqlString;
  rpt.DataSource = ds;
}

To write the code to load the xml-based report into the ActiveReports viewer

1. From the Visual Studio toolbox, drag the ActiveReports viewer control onto the Windows Form and set its Dock property to Fill.
2. Double-click the title bar of the Windows Form containing the viewer to create a Form_Load event.
3. Add code to load the RPX into a generic ActiveReport and display it in the viewer.

The following example shows what the code for the method looks like.

To write the code in Visual Basic.NET

Dim rpt As New DataDynamics.ActiveReports.ActiveReport()
Dim xtr As New System.Xml.XmlTextReader("C:\MyProjectPath\rptMain.rpx")
rpt.LoadLayout(xtr)
xtr.Close()
rpt.Run()
Viewer1.Document = rpt.Document				

To write the code in C#

DataDynamics.ActiveReports.ActiveReport rpt = new DataDynamics.ActiveReports.ActiveReport();
System.Xml.XmlTextReader xtr = new System.Xml.XmlTextReader(@"C:\MyProjectPath\rptMain.rpx");
rpt.LoadLayout(xtr);
xtr.Close();
rpt.Run();   
viewer1.Document = rpt.Document;