Problem with Parameters in Queries

rbuser

Hi

I have a Master/Detail report. For each Customer, orders are printed. The
master source are the Customer DataSet. I create my own CommandText for the
Orders DataSet. Let say there are 10 Customer records in the Customer
DataSet and I am generating SQL for the Order DataSet that will extract the
orders for 1 of those customers (say customer code 'C0001').

If I don't use parameters (for the customer selectecion) in the order
query's CommandText, the report generate correctly - thus the orders for
that 1 customer ('C0001') is printed. If I use parameters (for the customer
selectecion) in the order query's CommandText, the report generates as
following - for each customer (there are 10) the records in the orders
dataset (that is the orders for customer 'C0001') are printed.

I'm using ADO.

Thanks
Rico Koegelenberg

rbuser

Rico,

Firstly, I would avoid using Master/Detail relationships as far as possible
when accessing data on SQL-servers. I found that the Delphi data access
components open and close the detail dataset much more than is needed -
especially if you edit the master. Combining the two queries into one and
using client-side filtering will be much faster.

But on to the issue at hand:
Your problem seems to be more related to Borland's ADO components than
ReportBuilder, so I will try to explain how master/detail relationships work
when using TADODataSet components.

There two different ways of having a master/detail relationship when using
the Borland ADO components:
1) Whenever the master dataset is scrolled, the detail dataset is closed,
new parameter values for the detail dataset are assigned from the current
field values of the master record and the detail dataset is reopened (i.e.
requery is called).
2) Whenever the master dataset is scrolled, a client-side filter is imposed
on the detail dataset and records are filtered out according to the
master-detail field relationship that you specified.

Unfortunately you cannot specify which scenario you want to use with the
Borland components - it decides that for you. If you have ANY parameters in
your detail query, it will use scenario 1, otherwise it will use scenario 2.
If you want to use parameters you're stuck on scenario 1.

I think what is probably happening in your case is that your parameter names
do not correspond to actual field names in the master dataset, so after each
record scroll the detail dataset is closed, delphi fails to set new
parameter values since it cannot match the corresponding detail parameters
and master field names, and detail dataset is reopened with exactly the same
records as before.

The solution? If you want to go the way of (1), you have to make sure that
your linked fields are exposed as parameters, and the names of these
parameters must correspond to the names of fields in the master.

However, as I said before, I don't like Master/Detail relationships -
especially with the many record scrolls that occur while ReportBuilder
generates a report.

If you cannot combine the two queries into one, why not make the current
detail dataset fetch ALL the records (instead of just the records relevant
to the current master) and on the TppDBPipeline.OnOpen event change the
filter on the detail so you see only the records you want. Now you can get
rid of the nasty master/detail relationship and you can use parameters in
the detail dataset as well.

The only snag you might hit is if your detail result set is large and you
run out of memory. Then I suppose you could pull your own master/detail
trick by opening the detail query in the TppDBPipeline.OnOpen event and
retrieve only the records for the current master record. It'll be much
faster than going the route of (1) and you can use your own parameters. The
drawback of this is now your report's output may change from preview to
printout (if someone changes data from under you). It's a tricky situation
.

Hope this helps,

Pierre

customer