Passing AutoSearch criteria to Subreport

rbuser · October 2006

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

rbuser · 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

nicocizik · 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

rbuser · 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

nicocizik · 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

Passing AutoSearch criteria to Subreport

Comments