Help with correct bands to display a grouped report please

hsm

I have a dataset documenting some SQL code showing a routine name and the tables or other routines that it uses (see example below). Please can someone help me with the report bands I need to get a simple report showing the routine name once, followed by two lists, one showing the other routines it uses and one showing the tables it uses.The dataset has the master routine name duplicated for each row for which there is either a table or another routine used by it. If possible I'd like to group the used routines and used tables by their respective types, maybe all the functions used before the procedures used and all the base tables used before the views used.

I did experiment with sub reports but am still unclear how to set up the report bands

I'm using Fast Report 4

Here is an example of my dataset (blank lines for clarity only, nulls are where there is no data for that field)

This is the sort of report I would like to get

hsm

(small error, the bottom line of Routine_A in the dataset should have (null) for referenced_routine_type and referenced_routine _name.

hsm

I think I have found the solution.

I needed three separate queries, one for the 'master' routine name, one for the uses routines and one for the used tables.

the sub queries used a parameter in a where clause to pick up the value from the master query eg

SELECT distinct

referenced_routine.

referenced_routine_type

FROM

table1

WHERE table1.routine_name = :routine_name    -- <-- parameter

ORDER BY  

referenced_routine_type,

 .referenced_routine

Arrange the two sub queries as a master detail relationship with the master routine name query using parameters. Then have two side by side sub reports, each getting their data from one of the sub queries.

In Delphi this means having a datasource (ds1) with its Dataset pointing at the master query

Both sub queries have MasterSouce := ds1 and the 'params' set to routine_name as a string

Put those subreports in the group header band of the main report (nothing in the Master data band)

Put the used routine type in the Group header of one subreport with the used routine name in a master data band and put the used table type in the Group header of the other subreport with the used table name in a master data band.

Main page

sub report1

sub report2

Delphi form design

hsm

...and the report looks like