Report with Groups

Report with Groups

In the previous example we constructed a two-level report based on the data from two tables. Another report which looks the same can be constructed in FastReport, this time based on a dataset obtained from a joined query.

To do this, a SQL query which returns data from both tables and sorted in a particular way is needed. In our example, the tables will be joined on the  "acSubject" field from customers and "acConsignee" field from order table. The query might be:

select *
from tHE_SetSubj, tHE_Order 
where tHE_SetSubj.acSubject = tHE_Order.acConsignee
order by tHE_SetSubj.acSubject

The "order by" line is required to sort the records on the “acSubject” (customer) field.

How can a multi-level report be designed using this data? There is a special band, the “Group Header”. A condition (a database field value or an expression) is specifeid for the band; the band is output every time the condition's value changes. The following example illustrates this.

Let's create a new project in Delphi and place “TfrxDlDBQuery” component. Set properties of new component named "DlDBQuery​1" as shown here:

​SQL =

'select *
from tHE_SetSubj, tHE_Order       
where tHE_SetSubj.acSubject = tHE_Order.acConsignee
order by tHE_SetSubj.acSubject'

UserName = 'Group'

Add a “Group header” band to the report. Set a condition (in this case the “Group.acSubject” data field) in the “Group header” band editor:

Also link the data band to the “Group” data source and arrange some objects as shown (note that the group header must be placed above the data band):

The “Group header” band is output only when the field to which it is linked changes in value. Otherwise the data band connected to the “Group” dataset is displayed. If we compare this report to the master-detail report constructed earlier, it is obvious that order numbers are not sorted at all. This can be easily corrected by changing the SQL query’s 'order by' clause:

select *
from tHE_SetSubj, tHE_Order
where tHE_SetSubj.acSubject = tHE_Order.acConsignee
order by tHE_SetSubj.acSubject, tHE_Order.acKey

Reports having nested groups can be designed in a similar way. The depth of nesting of groups is unlimited. Reports which use groups have some advantages over reports of the master-detail type:

• the whole report needs only one dataset (query)
• the number of data grouping levels is unlimited
• data sorting becomes possible
• more optimal usage of the database resources (the query returns only one dataset for output, with filtering done by the query).

The only disadvantage is the need to write queries in the SQL language. However, a basic knowledge of SQL is virtually obligatory for any programmer working with databases.