How To: create a Sub Report using a DetailReport Band

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. The solution demonstrated in this example is to create a Master-Detail report using a "Detail Report Band". The DataSet (being the DataSource for the Report Designer) should contain two tables which are related.

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 create a DataSet and a relationship between the two data tables. 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 Report Designer's standard End-User components do not consent to DataSet modifications (being the DataSource for the Report Designer) which includes adding tables or relationships between them. Nevertheless this is still programmable within the Report itself by inserting, for instance, a code in the report's "OnBeforePrint" print event. We will now explain step-by-step how to create the report:

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 "DDetail Report" within which data from the project's "Detail" tabel will be displayed.  Once you have the "Detail Report" insert the relative "Report Header" and "Report Footer".

Now select the Script property from the report properties and insert the code in the "OnBeforePrint" for creating the DataSet:

The code needed is:

using System;

using System.Data;

using System.Data.Odbc;

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

{

/*** This code creates a relationship from the 'Customers' and 'Details' tables and assigns it to the Detail Report ****/

OdbcDataAdapter ReportAdapter;

ReportAdapter = (OdbcDataAdapter)xtraReport1.DataAdapter;

if (ReportAdapter == null)

return;

OdbcConnection Connection = new OdbcConnection(ReportAdapter.SelectCommand.Connection.ConnectionString);

OdbcDataAdapter Adapter1 = new OdbcDataAdapter("SELECT * FROM Customers", Connection);

OdbcDataAdapter Adapter2 = new OdbcDataAdapter("SELECT * FROM Details", Connection);

DataSet ReportDataSet = new DataSet();

Adapter1.Fill(ReportDataSet, "Customers");

Adapter2.Fill(ReportDataSet, "Details");

ReportDataSet.Relations.Add("ProdDetails", ReportDataSet.Tables["Customers"].Columns["CustomerName"], ReportDataSet.Tables["Details"].Columns["CustomerName"], false);

xtraReport1.DataSource = ReportDataSet;

xtraReport1.DataMember = "Customers";

DetailReport.DataAdapter = Adapter2;

DetailReport.DataSource = ReportDataSet;

DetailReport.DataMember = "ProdDetails";

}

In the code reported above the "xtraReport1" object represents the report's name displayed in the"Report Explorer" window.

A DataSet is created through this code in C-Sharp and containes the "Customers" table, the "Details" table and a relationship between the two tables set in the "CustomerName" field. This relationship will then be used in the DetailReport for extracting data from the Details table based on the CustomerName displayed in the GroupHeader  Band.

Now go ahead and insert a table with four fields in the DetailReport's ReportHeader which will represent the Details table's field names to be displayed.  The fields we are going to display in this example are "Product Code", "Total Bad", "Total Good" and "Total Reviewed". Insert the relative texts in the table fields.

Then insert a table with 4 field in the DetailReport's Detail which will show the Detail tables values corresponding to the filed inserted in the ReportHeader. In the table's fields you should now select, in this case, the field of the relationship defined in the DataSet, using the script code, as "Data Binding". This field must be written manually and accurately:

dataSet1 - Customers.ProdDetails.ProductCode

dataSet1 - Customers.ProdDetails.TotalBad

dataSet1 - Customers.ProdDetails.TotalGood

dataSet1 - Customers.ProdDetails.TotalReview

on the assumption that the FieldList (displayed in the Report Explorer window) has been declared in the "dataSet1" name.

The report should finish looking like this:

However, in runtime it should look like this:

Example: ES_DetailReport

References: