Home Subreports
New Blog Posts: Merging Reports - Part 1 and Part 2

Two detail tables

edited June 2004 in Subreports
Hi,

I have one master table and two detail tables. I'd like my report print
records in the following order:

First, master record.
Then, all the detail records from the first detail dataset.
Finally, all the detail records from the second detail dataset.

How can I do that?

Thanks in advance.

Comments

  • edited June 2004
    Hi Pavel,

    Please check out the following articles on on how to create a "Master with
    two details" report, and how to link your master detail data.

    ------------------------------------------------
    TECH TIP: Fundamentals of Report Data Traversal
    ------------------------------------------------


    1. Single Table Listing Report

    Assign the Report.DataPipeline property and leave the
    DetailBand.Pipeline unassigned.

    The report will traverse the data from start to end (based on the
    datapipeline range settings and honoring any filters you've placed on
    the datset etc.)


    2. Master/Detail Report:

    A. Assign the Report.DataPipeline property to the master. Create a
    subreport in the detail band and assign the childreport.DataPipeline to
    the detail datapipeline.

    Use either the visual linking features available from the Report
    Designer's Data tab, or Use standard Delphi dataset linking to define
    the relationships between the datasets.

    The Report will traverse the master records and for each, the subreport
    will traverse the detail data related to the master.


    3. Master with 2 Details

    Configure as in 2 above. Add an additional subreport to the detailband.
    Set subreport.ShiftRelativeTo property to the point to the first
    subreport. Connect the ChildReport's DataPipeline to the detail data.


    4. Report connected to no datapipelines.

    When Report.AutoStop is set to True, the Report will print a single
    detail band.

    When Report.AutoStop is set to False, the Report will print detail bands
    until instructed
    to stop. You can control when the report stops by setting the
    Report.PageLimit property or by calling Report.DataTraversalCompleted
    method. Otherwise the report will never stop.


    Additional Notes:
    ----------------

    1. SubReports have a Report property that is of type TppChildReport.
    Thus, programmatically you can code SubReport.Report.DataPipeline :=
    myDataPipeline.

    2. ChildReport's traverse data following the same rules as above. A
    ChildReport prints in its entirety each time it gets a turn to print.

    3. For a Child style SubReport use the Title/Summary band rather than
    the Header/Footer (or use a GroupHeader/GroupFooter combo). A standard
    Header/Footer will not work because these always print at the very
    top/bottom of the page.

    4. Do not filter the dataset of modify it any way once the report.Print
    command is issued. If you need to do master/detail and cannot use
    linked datasets, then use the master DataPipeline.OnRecordPositionChange
    event to filter the detail dataset.

    5. The reports and datapipelines use dataset bookmarking. Make sure
    the dataset can support bi-directional navigation.



    Check out the RBuilder\Demos\Reports\Demo.dpr project. Reports 0071,
    0072, 0073 show examples of master w/mutliple detail style reports.
    Number 0072 has two detail reports.

    ------------------------------------------------------
    Tech Tip: Linking SQL Queries for Master/Detail Data
    ------------------------------------------------------

    The following example shows two options for linking SQL queries to
    create a master/detail relationship.

    In this example, we are using Delphi's DBDemos data to create a
    Customer/Order relationship. Thus we wish to link the Orders detail to
    the Customer master.

    I. Delphi Query Linking
    ------------------------

    a. Set the detail TQuery.DataSource property to point to the master
    query's TDataSource component.

    b. In the SQL "Where" clause for the detail query use a ':' followed by
    the linking field name from the master:

    example
    select *
    from orders
    where orders.CustNo = :CustNo

    Now each time the master record position changes, the detail query will
    automatically be refreshed with the correct result set.


    II. RB DataPipeline Linking
    -----------------------------

    a. Set the detail DataPipeline.MasterDataPipeline to point to the master
    DataPipeline.

    b. Use the detail DataPipeline.MasterFieldLinks property to define the
    linking relationship

    c. In the SQL for the detail, retrieve all records and sort them by the
    linking master field:

    select *
    from Orders
    order by CustNo


    Notes:

    1. Using RB DataPipeline, each query is executed only a single time -
    thus performance is much faster.

    2. RB Professional and Enterprise Editions include a visual Data
    environment for creating SQL queries, defining linking relationships,
    and creating Ask-At-Runtime parameters. Using the RB tools you could
    create the above linked queries in about 10 seconds.


    --
    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.