How To: create a Sub Report using a "SubReport" object

Summary:

This document shows you how to create a Report containing a Sub Report using the Report Designer. The assigned task is to display data originating from two different related tables in the one same database. In this example, the solution we have proposed involves inserting a "SubReport" object available from the Report Desinger's object list.

In the below described Movicon example project we are going to create a recipe where we will insert Client list and a DataLogger to record production data.  Each production record will be associated to a Client and the production batches will be grouped according to Client in the report.

Requirement:

To create a Sub Report you will need to use .NET code in order to configure the "SubReport" object and create a DataSet. You should also need to have some basic know-how on both using the Report Designer for creating a report and .NET programming, and know how to use databases and their access functions.

Creating:

The example project we are going to create as set out in the assigned task in the above "Summary" section is described below step-by-step:

Creating a Movicon Project

This is what we plan to do: the project will be created to contain a table containing the Client list and a table containing the production records. A recipe will be used to insert the Client list so that clients can be inserted, deleted or modified during runtime. The recipe and its relating table will be called "Customers" and will contain three columns corresponding to customer name, address and phone number (CustomerName, CustomerAddress and CustomerPhoneNum). The DataLogger used for recording the production data will be called "Details" and will contain six columns corresponding to customer name, code and product description, good products, rejected products, revised products (CustomerName, ProductCode, ProductDescription, TotalGood, TotalBad, TotalReview). Values of the variables associated to the DataLogger are inserted in the appropriated display and the Data Logger will record when using the "Save" button. In this way simulation data can be inserted in the "Details" table.

Creating the  Report

The "SubReport" object that can be inserted in a report needs to be associated with an already existing report to embed during the Preview phase. Therefore, in this example we are going to create two report files, one linked to the "Details" table and a main one linked to the "Customers" table where the "SubReport" object will be inserted afterwards. The standard Report Designer's End-User components do not allow you to set the "SubReport" object's properties, meaning that you won't be able to associate the reference report to the "SubReport" object. However we can wangle this out in another way within the report itself by inserting, for example, code into the "SubReport" object's "OnBeforePrint" event. We shall now proceed in creating the report step-by-step. The details is the first report to be inserted, which will then be associated to the "SubReport" object. Insert the name of the report to be created, i.e. "details.repx" in the "Details" DataLogger's "Report File" property and click on the "Create-Edit Report File..." command button to open the Report Designer's editor window. The report will be initally created empty and linked to the Details table. At this point just add the Bands desired, i.e. GroupHeader, Detail and GroupFooter. Insert a table with 4 fields in the GroupHeader  Band to represent the Details table field names to be displayed. In this example we have decided to display the "Product Code", "Total Bad", "Total Good" and "Total Reviewed" fields. Insert the relative texts in the table fields. Now insert another table with four field into the Detail Band which will show the Detail table's values corresponding to the fields inserted in the ReportHeader.

In the table fields, in this case you should select the relative Details table field. Finally, insert yet another table with 4 fields which will show the amount of pieces listed in the details table. The first table field will be in simple text, ie.  "Summary", while the remaining three fields will be set as total amount fields: "Total Bad", "Total Good" and "Total Reviewed".

You should get this result:

Select the "GroupField" property from the GroupHeader Band to open a dialog window then add a new group by selecting the "CustomerName" column as "FieldsName".

The second report to be created will be the one which opens in runtime mode containing the "SubReport" object. Insert the name of report to be created in the "Customers" recipe's "Report File" property, for instance "reportproduction.repx", and click on the "Create-Edit Report File..." button to open the Report Designer editor. The report to be created will initially be empty and linked to the Customers table. At this point you just have to add the Bands desired, i.e. the Pager PageHeader, GroupHeader, Detail and PageFooter. Then enter a title, print date and time in the PageHeader  band and page number in the PageFooter. Enter the relative customer information by taking the fields from the 'Customers' table and adding them to the GroupHeader band. The end result should look like this:

Select the "GroupFields" property from the GroupHeader Band properties to open a dialog window, then add a new group by selecting the "CustomerName" column as "FieldName".

At this point insert the "GroupFooter" Band and add the "SubReport" object from the ToolBox within the Band.

Now select the Script property from the SubReport property and insert the code in the "OnBeforePrint" to set the object's report source:

The code needed is:

using System;

using System.Data;

using System.Data.Odbc;

using System.IO;

using System.Windows.Forms;

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {

XtraReport Report = new XtraReport();

String ReportFileName = Directory.GetCurrentDirectory() + "\\DLOGGERS\\Details.repx";

if (!File.Exists(ReportFileName))

return;

Report.LoadLayout(ReportFileName);

xrReportDetails.ReportSource = Report;

OdbcDataAdapter ReportAdapter;

ReportAdapter = (OdbcDataAdapter)Report.DataAdapter;

if (ReportAdapter == null)

return;

ReportAdapter.SelectCommand.CommandText =

"SELECT * FROM Details WHERE CustomerName = '" + xtraReport1.GetCurrentColumnValue("CustomerName") + "'";

//MessageBox.Show(ReportAdapter.SelectCommand.CommandText);

DataSet ReportDataSet = new DataSet();

ReportAdapter.Fill(ReportDataSet, "Details");

Report.DataAdapter = ReportAdapter;

Report.DataSource = ReportDataSet;

Report.DataMember = "Details";

}

In the above code the "xtraReport1" object represents the report name displayed in the "Report Explorer" window and "xrReportDetails" represents the name of the inserted SubReport.

The "Details.repx" report file is associated to the SubReport through this code in C-Sharp along with the DataSet containing the "Details" table.

This is the end result you should get in runtime:

Example: ES_SubReport

References: