Passing AutoSearch criteria to Subreport

edited October 2006 in Subreports
I have a report that displays several calculated fields in the detail band
(count, sum, min, max, avg). Each record corresponds to a Part Number (PN)
that is displayed. The report has a drill-down subreport that displays
details of orders for that PN - i.e. clicking the PN displays the drop-
down.

I am using two separate datapipelines - one for the main report and the
second for the subreport. The two were originally linked by a Sales Order
number, but I have had to drop that field because it causes duplicate
records in the main report - multiple PNs in one Sales Order. I have
AutoSearch criteria on the main report that include a range of Order Dates
(using Between) and a range of Part Class (also Between). I need to be able
to pass those criteria to the subreport as AutoSearch criteria when the PN
field is clicked in the main report.

I am using RB 7.04. Can I manipulate the AutoSearch criteria of the
subreport with this version? I can define those criteria in the Report
Designer, but I don't want the user to have to enter the search data twice.

Thanks,
Scott Moon

Comments

  • edited October 2006
    I know this can be done in RB version 9 and later, and we do plan to
    upgrade to v10 in the near future, but it's not an option at the moment,
    and I've been working on this report for WAY too long!

    Thanks,
    Scott

  • edited October 2006
    Hi Scott,

    It is possible to create a report that has autosearch criteria for multiple
    datapipelines. From your description below, it looks like you need to
    create a master-detail report with Parts as the master dataset and Orders as
    the detail dataset. You would want to link on the Part Number field and be
    sure to order your detail dataset by the Part Number field as well. Now
    when you first search on the Part Number you will get a list of parts, then
    you can search on order date and when you drill down each part you will see
    the orders from within that date range.

    Are you using Dade to create your datsets?

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited October 2006
    Not exactly. The main report is a summary of order data grouped by
    customer type, part class, and part number. The drill-down shows details
    on individual orders by part number. Something like this:

    ------------Main Report--------------
    Customer Type (e.g. Fire Station)
    Part Class (e.g. Cleaning Supplies)
    Part# Num_Orders Min_Qty Max_Qty Total_Sold Avg_Qty
    ABC1234 3 1 5 12 4
    ------------Drill-Down SubReport--------------
    Cust_Name1 Order_Date Qty_Ordered Sales_Order_#
    Cust_Name2 Order_Date Qty_Ordered Sales_Order_#
    Cust_Name3 Order_Date Qty_Ordered Sales_Order_#


    The problem is with the database structure. In order to get all the data
    I need to display, I am joining 8 or 9 tables together - some strictly as
    bridges between tables containing required fields. To help simplify
    things, I have created two views in the database - one with the main
    report data (including summary data) and one for the subreport. I was
    linking them via the Sales Order Number, but as mentioned earlier, it was
    causing redundant records in the main report. I tried to create the same
    data set in DADE but thought it would be easier using straight SQL.

    The report will typically be used to answer questions such as "I want to
    know the statistics for orders placed for part classes 7500-7600 between
    Oct 1, 2006 and Oct 31, 2006." Drilling down on a part number will show
    the individual order data by customer and sales order.

    Don't know if this makes the problem any more, or less, clear!

    Scott


  • edited October 2006
    Hi Scott,


    Correct. This is a many to one relationship and therefore would give you
    multiple master records for each detail. Typically when creating a Master -
    Detail relationship, you want a one to many relationship. This is why I
    recommended linking on the Part Number because for each single part, there
    are multiple orders.

    So if I understand correctly, you are not linking your datapipelines at all
    now? Without this link, ReportBuilder has no way to know how the part
    number you click relates to the detail dataset. In this case AutoSearch
    will not help. You will most likely need to alter your current datasets in
    order to successfully get the report you need.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

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