Home› DADE
ReportBuilder 22.04 now available including Delphi 12.1 support!
New Blog Post: Helper Routines

Master - Detail dataviews with SUM(FieldName)

rbuserrbuser
edited May 2004 in DADE
I was wondering if it was possible to create 2 dataviews where Dataview_1
contained a joined query and Dataview_2 containing the sum of a column
(SumFieldX) and joined to dataview 1 by their unique field (Field0). This is
so I can do a calc field in the detail of the report that will use
DataView_1.Field1 + DataView_1.Field2 - DataView_2.SumFieldX.
For example:

DataView_1

Client -> Investment -> Transactions

DataView_2

Transactions with Calc field Sum(Withdrawals) grouped by investment
(SumFieldX)

DataView_1 is left joined to DataView_2 via the investment key field
(FieldA)

The problem I am getting is that as soon as I provide this join in my report
(by adding the second dataview) it is filtered by DataView_2.

I thought this may have worked like datasets in delphi that are joined
together in a master - detail chain so that every time master record is
iterated then the detail dataset is filtered by their matching key field.

How can I get this working?

Jarrod

Comments

  • nardmoseleynardmoseley
    edited May 2004

    1. Below is an article on how linking dataviews works in ReportBuilder. The
    issue you are encountering is that the linking SQL generated for the detail
    dataview is altering the desired results.

    2. One option is to create the a custom dataview template. A custom dataview
    can contain multiple pipelines and you could use Delphi TDataSet linking
    rather than DataPipeline linking. (You can use either type of linking but
    never both).

    3. Another option would be to create the dataviews using the Query tools,
    but write code to manually create the datapipeline links.


    www.digital-metaphors.com/tips/LinkDADEPipelines.zip





    -------------------------------------------
    Tech Tip: Linking DataViews
    -------------------------------------------

    Overview
    ---------

    Linking DataViews in the ReportBuilder Data workspace (DADE), requires that
    the detail data be sorted by the linking fields. When DataViews are linked,
    DADE generates special linking SQL for the detail DataView. The linking SQL
    (Magic SQL) can be viewed by positioning the mouse over the DataView window
    and pressing Ctrl + Left Mouse button. The results of the Magic SQL query
    can be viewed by pressing Shift + Left Mouse button.

    ReportBuilder's DataPipeline architecture includes support for linked data
    traversal. Whenver the record position of the master data changes, the
    traversal logic compares the master and detail field link values and then
    determines where to locate the record position for the related detail data.
    The linking logic will only work properly if the Database engine's collation
    of the detail query result set is in sync with ReportBuilder's internaly
    field link comparison logic.


    --
    Nard Moseley
    Digital Metaphors Corporation
    www.digital-metaphors.com


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
This discussion has been closed.