Report with two data levels (Master-Detail)

Report with Two Data Levels (Master-Detail)

So far our example reports have used only one data band (“First level data” or ”MasterData”) to control data output. This was adequate for the output of data from one database. Pantheon also allows reports to be designed having up to six data levels, all on the one design page. Furthermore, an unlimited number of data levels in reports can be achieved by the use of the “Subreport” object - this object is covered later on. Most reports in general need only one, two or three data levels, larger numbers of data levels are rare.

Let's look at how to design a two data level report. The report will output data from the tables: “tHE_SetSubj” and “tHE_Order”. The first table is a list of subjects and the second one is a list of orders placed by those subjects (customers).

The second table contains the list of all the orders placed by all the customers. To view the orders from the tHE_Order table which are placed by one customer in the Customers table, the two tables are linked on the “acSubject” field from customers and "acConsignee" field from order table.

Let's design the report. Create a new report and place two “TfrxDlDBTable” components on the form. Set the component properties as shown here:

Table1:

TableName = 'tHE_SetSubj'

UserName = 'Subjects'

Table2:

TableName = 'tHE_Order'

UserName = 'Orders'

Now add a “MasterData” and a “DetailData” band to the page:

Note that the “MasterData” band must be placed above the “DetailData” band! Drag it there if necessary. If the Master is placed under the Detail, Pantheon will generate an error message when you preview the report.

If you previewed the report now, you would see that the list of orders remains the same for every customer and contains all the records from the “Orders” table. In code on the report add two lines as you can also see on the picture

Now we have set a 'Master-Detail' relationship and link between fields in two sources.

When creating the preview FastReport does the following. After a record is output from the master table (Customer), it sets the filter on the detail table (Orders). Only those records which meet the 'Orders.acConsignee = Subjects.acSubject' condition will remain in the table. This means that for each customer only those orders which belong to the current customer will be displayed in the detail band. This is an important concept to grasp. Even though data bands may be of master or of detail type, they only control the positioning of the data on the output page (order and number of times displayed). The data displayed by the objects in the bands is dependent on which fields the objects are linked to and on the external linking of the two tables.

This is the final output:

Reports can containing up to 6 data levels can be constructed in a similar manner.

In a lot of cases Master-Detail relationship is in set up in the program code.

Let's look an example of the report '3C0 - Travel Management Report'  that is in the program menu 'Personnel | Travel Management | Reports' .

On the report master table is 'main' table of travel orders, detail table is the table with positions of travel order. In this case we have also one more level (subdetail table) namely costs on the position of travel order.

On this report we have also two bands with "RowCount" property set to value 1 and subreports which we will meet later.