Newbie question - subqueries (or outer joins) and reports

rbuser

Hi.

I have a report, getting its data at the moment from

select

r.somefield, p.Surname + ', ' + p.GivenNames as PatientWholename, p.Address
as PatientAddress, p.Suburb as PatientSuburb, p.Postcode as PatientPostCode,
p.State as PatientState, p.MedicareNo as PatientMedicare, p.Title as
PatientTitle, p.RecordNo as PatientRecordNo, p.DateOfBirth as
PatientDateOfBirth, p.Gender as PatientGender, s.Surname + ', ' +
s.GivenNames as StaffWholeName, s.Title as StaffTitle
from Requests r, Patients p, Staff s
where r.patientID = :PatientID
and r.patientID = p.patientID
and s.StaffID = r.RequestingMO

So thus far it is all inner joins. The request table is the basic 'item'
data, and hanging off that is information regarding patient and staff member
with one patient and one staff member per request. I have set up the report
display nicely for this.

Now I need to add 'extra copy recipients' (multiple copy recipients for each
request item) and 'tests' (multiple tests associated with each request).
Conceptually I think of this as for a request, do a subquery to get all the
associated extra copy recipients and another to get all the tests for each
query, and then insert all the items in to the report. Maybe I need to be
thinking in terms of outer joins instead.

What is the way to do this? Do I need to create subreports or subbands on my
report for this? If so, does the band need to take up the whole horizantal
width of a page or can it be squeezed into say half a page?

Thanks for any help on this.

Lauchlan Mackinnon.

digitalmetaphors

Unless you just love Joe Celko, I'd probably create a detail query or two
for this. Pull the needed data, link using the DataSource property of the
detail TQueries. Use subreports to traverse this additional data.
Subreports support just about any format. Right-click and set ParentWidth to
False to get them printing side-by-side. (See example 73 in
RBuilder\Demos\1. Reports for side-by-side.)

--
Cheers,

Tom Ollar
Digital Metaphors Corporation

rbuser

> Unless you just love Joe Celko, I'd probably create a detail query or two
to

Thanks Tom.

You might find it useful to add a blurb about subreports to your online help
with essentially the info you just mentioned. Presently the online help only
has the class definition (hierarchy, properties, methods): it would have
been useful to have an entry that said there is a subreport component icon
at the top right of the report designer, that to set it up you need linked
master-detail tables, and so on. Just a suggestion!

Thanks again.

BTW, who's Joe Celko?

Lauchlan M.

rbuser

to

Tom,

I did this, and when I added the sub report to my report and clicked
preview, none of the report was visible.

Any ideas?

Thanks,

Lauchlan Mackinnon.

rbuser

> I did this, and when I added the sub report to my report and clicked

Sorted this out, but my sub-report (which is meant to show all the detail
rows) shows only the first row of the detail dataset items.

Is there any trick to making it show all the detail items?

Thanks,

Lauchlan Mackinnon.

digitalmetaphors

Please make sure that the subreport is assigned to the detail pipeline. Most
likely the subreport is not traversing all of the detail data if this is the
case.

--
Cheers,

Alexander Kramnik
Digital Metaphors

digitalmetaphors

Tom was just saying that Joe Celko is known for creating queries which you
might think were impossible to build: http://www.celko.com/


Cheers,

Jim Bennett
Digital Metaphors

rbuser

> Please make sure that the subreport is assigned to the detail pipeline.
Most
the

Alexander,

The subreport property 'datapipeline' has my detail pipeline
'plExtraCopyDetail' assigned. Is this what you meant? I am not sure what you
mean exactly by "the subreport is assigned to the detail pipeline". I think
the detail pipeline is assigned to the subreport.

Thanks,

Lauchlan M.

digitalmetaphors

Try hooking a DBGrid to the detail pipeline to make sure you are actually
retrieving more than one row of data for the master records.

--
Cheers,

Alexander Kramnik
Digital Metaphors