grouping over different queries

rbuser

Dear all,
I am using D6 and RB5. I want to create a report which lists visits
to schools, and documents linked to a school, sorted by school.

I do not think I can do this in one query, visit and document are both
children of school, but are not related (no jokes please), so I have 2
queries,
select * from school, visits where visits.schoolpkey=school.pkey

select * from school, documents where documents.schoolpkey=school.pkey


I can get 2 sub reports which will link all visits to a school (by school),
and then all documents for a school ( by school).

How do I get the report to list both visits and documents (by school).

regards
John Evans

digitalmetaphors

Create a master dataset on school, and then link the two detail datasets up
to the master. Order the school dataset however you want, and the detail
should print in the same ordering.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Dear Jim,

thanks for this. I am nearly there, I can get records from the two
independent child queries showing in the same detail band, however I only
get the first record from each.
I am using ADO queries, and am linking the detail query to the school query
by using the SQL
"select * from visits where visits.schoolpkey=:pkey"
then I set the detail query dataset to the dataset of the school query (pkey
is the primary field in School).
Using grids on the form I can clearly see the school and multiple detail
records, but only the first detail is appearing in the report.

What am I doing wrong??

regards

John

digitalmetaphors

Be sure to connect the Subreport.DataPipeline property up to the data
pipelines:)


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Dear Jim,
yes the subreport pipelines are connected to the main pipeline,
although if I disconnect them it seems to make no difference. Why would this
be.
To summarise:
I have 3 ADO queries, with 3 datasources and three pipelines. the three
pipelines are connected to the three datasources, the three datasources are
connected to the three ADOqueries. The master/detail relationship is defined
by linking two of the ADO queries to the datasource of the first query. I
have set the master pipeline property of two pipelines to be the first
pipeline.
At this point if I set the three queries to active and open the fields
editor, I can see the number of child records for the two child queries.
I have a single ppreport, linked to the first pipeline. I have created a
group using the key field of "School" from the first query.
I have added two subreports to the detail section of my report.
In each of the subreports I have selected the other two pipelines and then
fields from the other two datasources.
In both preview mode and running the app, I can see only the first record
for the two child queries.

I have gone through the notes in the developers guide several times, and I
cannot see what I am doing wrong.

regards
John

digitalmetaphors

Please create a single master detail report as shown in the developer's
guide. Do not try linking the pipeline through the master field links and
master data pipeline properties. Once you get the single master detail
report working for this report, then try adding the second subreport. First,
use two TDBGrids on a form and try to get the datasets to appear in the
proper way using the delphi query linking. If the datsets are configured
correctly, you should be able to select a record in the master grid and the
detail records should show in the detail grid.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Dear Jim,
you will be relieved to know that all is now well. I cannot
see what is different to what I had before, however deleting the 2 child
pipelines and the 2 subsections and then rebuilding has done the trick.
Thanks for sticking it through.

regards
John