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.

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. 

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:

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:

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:

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