This tutorial illustrates creating a subreport using scripts.
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.
Note: We are using Visual Studio 2019 and will be connecting to Microsoft Jet 4.0 OLE DB Provider. Since Visual Studio 2022 runs in 64 bit, it does not show Microsoft Jet 4.0 OLE DB provider; you should use one of the compatible providers (e.g. Microsoft.ACE.OLEDB.12.0).
This walkthrough uses the Northwind database. The NWIND.mdb file can be downloaded from GitHub: ..\Samples18\Data\NWIND.mdb.
When you have finished this walkthrough, you will have main report that looks similar to the following.
SQL Query |
Copy Code
|
---|---|
SELECT * FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID ORDER BY CompanyName, OrderDate |
SQL Query |
Copy Code
|
---|---|
SELECT * FROM [order details] inner join products on [order details].productid = products.productid |
Property Name | Property Value |
---|---|
Name | ghCompanies |
BackColor | LemonChiffon |
CanShrink | True |
DataField | CompanyName |
GroupKeepTogether | All |
KeepTogether | True |
Property Name | Property Value |
---|---|
Size | 4, 0.2 in |
Location | 0, 0 in |
Font Bold | True |
Font Size | 12 |
Property Name | Property Value |
---|---|
Name | ghOrders |
BackColor | LightYellow |
CanShrink | True |
DataField | OrderDate |
GroupKeepTogether | All |
KeepTogether | True |
TextBox1
Property Name | Property Value |
---|---|
DataField | OrderDate |
OutputFormat | MM/dd/yy |
TextBox2
Property Name | Property Value |
---|---|
DataField | RequiredDate |
OutputFormat | MM/dd/yy |
TextBox3
Property Name | Property Value |
---|---|
DataField | ShippedDate |
OutputFormat | MM/dd/yy |
Alignment | Right |
Label1
Property Name | Property Value |
---|---|
Text | Ordered: |
Font | Bold:True |
Label2
Property Name | Property Value |
---|---|
Text | Required: |
Font | Bold:True |
Label3
Property Name | Property Value |
---|---|
Text | Shipped: |
Font | Bold:True |
Property Name | Property Value |
---|---|
ReportName | full project path\rptSub.rpx |
Name | SubReport1 |
Property Name | Property Value |
---|---|
Name | ghOrderDetails |
BackColor | LightSteelBlue |
CanShrink | True |
DataField | OrderID |
Label1
Property Name | Property Value |
---|---|
Text | Product Name |
Font | Bold:True |
Alignment | Left |
Label2
Property Name | Property Value |
---|---|
Text | Quantity |
Font | Bold:True |
Alignment | Right |
Label3
Property Name | Property Value |
---|---|
Text | Unit Price |
Font | Bold:True |
Alignment | Right |
Label4
Property Name | Property Value |
---|---|
Text | Discount |
Font | Bold:True |
Alignment | Right |
Property Name | Property Value |
---|---|
BackColor | Gainsboro |
CanShrink | True |
TextBox1
Property Name | Property Value |
---|---|
DataField | ProductName |
Alignment | Left |
TextBox2
Property Name | Property Value |
---|---|
DataField | Quantity |
Alignment | Right |
TextBox3
Property Name | Property Value |
---|---|
DataField | Products.UnitPrice |
Alignment | Right |
OutputFormat | Currency |
TextBox4
Property Name | Property Value |
---|---|
DataField | Discount |
Alignment | Right |
OutputFormat | Percentage |
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=[User Folder]\Samples18\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 Detail_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 |
C# code. Paste in the script editor window. |
Copy Code
|
---|---|
GrapeCity.ActiveReports.SectionReport rptSub; public void Detail_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=[User Folder]\Samples18\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; } |
Click the preview tab to view the report at design time.
OR
Open the report in the Viewer. See Save Section Reports for further information on how to load the xml-based Section Report onto the viewer.