Report with two data levels (master-detail) in Delphi/Lazarus by the example of FastReport VCL / FMX

2021-10-15

Report with two data levels (master-detail) in Delphi/Lazarus by the example of FastReport VCL / FMX

In this article I would like to tell you about such a powerful opportunity of FastReport as multilevel reports. Their structure can be compared to a tree – a trunk, large branches, thinner branches growing from them, and so on up to leaves – or with a company structure: divisions, subdivisions, employees. They are often called master-detail or master-subordinate and consist of several tables. One table contains a list of the main entities; another table bound with the first one contains a list of subordinate entities with a reference to the first table specifying which entity from the first table a certain entity from the second one is subordinate to, and so on.

FastReport supports nesting of up to six levels (possibly more by using the Nested report object, but this will be described later). In real applications, one rarely has to print reports with large nesting of data; usually, 1–3 levels are enough.

An example of building a master-detail report

Let us consider creation of a two-level report. It will contain data from Customer and Orders tables. The first table is a list of customers; the second is a list of orders made by the customers. The tables contain the data of the following types:

Customer:

CustNo       Company
1221          Kauai Dive Shoppe
1231          Unisco
1351          Sight Diver

Orders:

OrderNo       CustNo       SaleDate
1003            1351          12.04.1988
1023            1221          01.07.1988
1052            1351          06.01.1989
1055            1351          04.02.1989
1060            1231          28.02.1989
1123            1221          24.08.1993

As one can see, the second table contains a list of all orders made by all companies. To get a list of orders made by a specific company, on should select the data from the table, for which the field CustNo is equal to the number of the selected company. The report built with these data will look like this:

1221       Kauai Dive Shoppe
    1023       01.07.1988
    1123       24.08.1993
1231       Unisco
    1060       28.02.1989
1351       Sight Diver
    1003       12.04.1988
    1052       06.01.1989
    1055       04.02.1989

Now we start making a report. We create a new project in Delphi and set for a form two TTable components, a TDataSource component, two TfrxDBDataSet components, and one TfrxReport component.

Form with the components

Connecting data from the base to the report objects

We set the components as follows:

Table1:
DatabaseName = 'DBDEMOS'
TableName = 'Customer.db'
Table2:
DatabaseName = 'DBDEMOS'
TableName = 'Orders.db'
 
DataSource1:
DataSet = Table1
 
frxDBDataSet1:
DataSet = Table1
UserName = 'Customers'
 
frxDBDataSet2:
DataSet = Table2
UserName = 'Orders'
 

In the report designer, we connect our data sources in the Report|Data… window. 

Connecting data sources

Add the 1st level data (master) and the 2nd level data (detail) bands to the page. From the data panel (on the right), we pull the table fields to the respective bands (master and detail). It will look like this:

Report template

Attention – the 1st level data band must be located above! If it is located below the 2nd level data band, FastReport will inform of an error when a report is started.

After starting we will see that the list of orders is the same for every customer and contains all records from the Orders table. This is because we did not switch on filtration of records in the Orders table.

Let us return to our data sources. For the Table 2 component, we set the MasterSource = DataSource1 property. Thus, we set the master-subordinate connection. Now we have to set the condition of records filtration in the subordinate source. To do that, call the editor of the MasterFields property at the Table 2 component:

Editor of the MasterFields property

We have to connect two CustNo fields in both sources. To do that, select the CustNo index in the list above, select the fields and click the Add button. The bunch of fields will be relocated into the lower window. After that, close the editor with ОК button.

When a report is launched, FastReport will do the following. It will select the next recording from the main table (Customer) and set the filter to the subordinate table (Orders). Only the recordings which satisfy the condition Orders.CustNo = Customer.CustNo will be left in the table. That is, for each customer only the orders of that customer will be shown:

Final report

Similarly, you may build reports with up to six data levels.

August 12, 2024

How to build and install the Postgres plugin in FastReport .NET

This article describes how to connect to the database using the FastReport .NET plugin for the report designer from Visual Studio via the NuGet server.
August 08, 2024

How to install FastReport .NET and its components on Windows

Step-by-step instructions for online and manual installation via the FastReport registration code.NET and its components in Windows.
July 26, 2024

Updating HTMLObject as a plugin for FastReport .NET

Detailed instructions for using the new HTMLObject plugin, which uses splitting DOM HTML into FastReport report objects.
Fast Reports
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314

© 1998-2024 Fast Reports Inc.