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

Subreport with Autosearch

edited March 2003 in Subreports
I'm developing a fairly straight forward parts inventory master report. My
data
looks like this:

Master Table: MASTER
Detail Table1: WAREHOUSE
Detail Table2: VENDOR

So this data is a One (master) to Many (warehouse and vendor)


In the detail band of the main report I have:

-----
Fields for the master record
Subreport #1 | Subreport #2
------

I have linked the query's and set the link to only include records from the
master for which matching items exist.

As expected, if either of the detail subreports have data the master get's
included and printed on the report. My problem is I'd like to include the
master ONLY when BOTH subreports have data. This would make it possible to
Autosearch for a vendor ID for exmaple, and only include master records that
have both a warehouse and the desired vendor ID. Is this possible?

Thanks! Jon Gray

RB 7.02 Server based via Delphi 6 SP2

Comments

  • edited April 2003
    On the two detail data pipeline links, there is an option in the Links
    Dialog to show the master records only when detail records exist. This is
    the SkipWhenNoRecords property setting, via the Link Dialog in DADE.
    Double-click on the colored link betweent the datavies to launch the Link
    Dialog.


    Cheers,

    Jim Bennett
    Digital Metaphors


  • edited April 2003
    This works well for a single Master -> Detail. My problem is with two
    detail tables. If either detail tables have data then the master will show
    which does make sense. Really the SkipNoRecords property is operating as an
    "OR" condition when there is more than one detail table.

    However, I need this to be an AND condition somehow. So both detail tables
    must have data for the master to show. Maybe even a RAP OnPrint or
    something to further control subreport behavior?


    An example of this:

    Autosearch field "warehouse" is set to 1
    Autosearch field "vendor" is set to XYZ Company

    Just about all my part master records have a warehouse "1" but few have
    vendor "ZYX Company" as a vendor. When the report is run I want to limit
    what gets shown to only those records that have both warehouse = 1 AND
    vendor = XYZ Company. Right now it gives me all master records that have
    warehouse = 1 regardless of vendor.



    Thanks!



  • edited April 2003
    It should be implemented as an AND. Basically, there needs to be a
    SkipWhenNoRecordOptions set property with values like [swnrAnd, swnrOr].

    Thanks for the suggestion we'll put it on the todo list.

    As a workaround, you may be able to peek (make sure the record position is
    always restored) at the detail records when the master detail band traverses
    its pipeline to see if the detail pipes have detail data by checking the key
    linking field value on the peeked detail record. Then set the visibility of
    the master detail band in the Detailband.BeforePrint event based on the
    details not having any data for the AND or OR case, as you wish.

    Cheers,

    Jim Bennett
    Digital Metaphors


This discussion has been closed.