Help with correct bands to display a grouped report please

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


Comments

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

  • 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


  • ...and the report looks like


Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.