Master-Detail Problems

rbuser

I've read the tech tip, Linking SQL Queries for Master/Detail Data. I'm
using the DataPipeline method. I've followed the steps listed in the tech
tip. The report (and sub-report) produce output, but the sub-report/detail
query only shows one record for each master. Yes, the detail query is
ordered on the foreign key.

The master report has a group keyed on the primary key of the master
dataset. The detail report is a sub-report, which has a group keyed on the
foreign key field of the detail dataset.

Why is this not working? What should I be looking for?
--
Steven S. Weston

rbuser

Steve,

you have a group in the sub report on the foreign key
so there is only 1 group for each master?, set the grouping on the primkey
of the detail data set
furthermore you could look at your pipeline to see if it is not suppressing
repeating values
but i guess altering the grouping in the subreport will solve it
cu
marc

rbuser

Setting the sub-report's group to key on the detail primary key did show all
items instead of just one. But, it also printed a group header for each
detail item -- not what I want. Grouping on the detail primary key would,
logically, produce a group header for each detail record.

I also tried moving the group header boxes to the header band. In that case
all detail records printed, but no header boxes were printed.

Grouping on the detail foreign key, the key they all have in common, should
print one group header and all of the common detail records -- but that
situation only prints one detail record, not all of them.

What gives here?

--
Steven S. Weston

nicocizik

Hi Steven,

ReportBuilder will traverse you data top to bottom one by one in the order
you set for you dataset. If you would like to group on a non-primary key
field, you need to be sure you order your dataset on that field. This way,
ReportBuilder will be able define each group correctly.

--

rbuser

Did that. Order By is correct for the sub-report's detail table. As I wrote
in my first post, I have followed the Tech Tip closely. I either get
multiple headers for each detail item, or just one detail item.

Here's my detail query:
SELECT GLDistST.*,
Br.BranchRC AS BranchNumber,
GL.GLAcct || ' ' || GL.Name AS GLNumbName
FROM GLDistST
LEFT OUTER JOIN Branches Br ON (GLDistST.BranchKey = Br.PKey)
LEFT OUTER JOIN GlAccts GL ON (GLDistST.GLAcctKey = GL.PKey)
ORDER BY GLDistST.RefKey

Grouping on RefKey (the foreign key that matches the master record) prints
only one record when there are more that should be printed. Grouping on the
detail table's primary key (which can't be right!) prints all of the
matching detail records and a group header for each record (as I would
expect using that group key).

The master/detail relationship properties were set as described in the Tech
Tip.

Why doesn't it work?
--
Steven S. Weston

nicocizik

Hi Steven,

Based on the information you've given, I'm unsure why this is not showing
all your records for each master record. Just to be sure, you do realize
that grouping on the linking field in a subreport is only going to give you
a couple extra bands to work with? If you were to remove the group all
together, you should see the same report only no GroupHeader or GroupFooter
bands. At no time inside the Subreport will the group break because the
subreport stops printing when the linking field changes and gives control
back to the main report. Below is a small test I created to be sure
everything is working correctly in ReportBuilder. Perhaps you can use it to
find the issue in your application.

http://www.digital-metaphors.com/tips/MasterDetailGroupingTest.zip

--

rbuser

Your sample works fine here. My report is setup the same except for
cosmetics. I'm going to audit all of the component properties to locate a
difference if I can. The only one that is apparent is that your report is on
a TForm and mine is on a DataModule.

--
Steven S. Weston

rbuser

Got It!! The ChildReport's AutoStop property was set True. Everything works
when set False.

Many thanks!!

--
Steven S. Weston