Master / Detail
i cannot get my current master/detail to work correctly. i'm getting
all the detail records for each master which means my link or sorting is
incorrect but i cannot for the life of me figure this out. when i look
at the order of the records it appears to be in the correct sort order
but...
master
select distinct t.id1, t.id2, t.TypeSort
from table1 t
join table2 Po on t.id1 = Po.id1
join table3 Pe on t.id2 = t.id2
where t.typesort = 10
order by
po.tin, PE.name, pe.tin, t.TypeSort
detail (each typesort has a separate detail table - i'm only working
with 1 detail at this point)
SELECT r.id1,r.id2, recId, accountNo, fieldMask, amt1, amt2
FROM table_10 r
join table2 Po on t.id1 = Po.id1
join table3 Pe on t.id2 = t.id2
order by
po.tin, PE.name, pe.tin, fieldMask, accountNo
my masterfield link for the detail pipeline is based on
id1 and id2.
my report main pipeline is to the master with groups id1, id2 (page
break after id2 changes);
i have a subreport to the detail pipeline. eventually i want to add a
group break on fieldMask on this subreport.
the master data as it appears at run time is:
id1, id2, typeSort
3 17 10
3 14 10
the detail data for typesort 10 table appears
id1, id2, fieldMask, accountNo, amt1, amt2
3 17 1 xxx1 100 200
3 14 0 xxx2 150 250
3 14 1 xxx2 25 100
3 14 1 xxx3 75 25
3 14 2 xxx4 10 0
when i run the report i get
3, 17
all 6 detail records in order as above
3, 14
all 6 detail records in order as above
i was really hoping ;-) for
3, 17
first detail
3, 14
detail 2-6
any light you can shed on this would be great!
thanks!
-martha
all the detail records for each master which means my link or sorting is
incorrect but i cannot for the life of me figure this out. when i look
at the order of the records it appears to be in the correct sort order
but...
master
select distinct t.id1, t.id2, t.TypeSort
from table1 t
join table2 Po on t.id1 = Po.id1
join table3 Pe on t.id2 = t.id2
where t.typesort = 10
order by
po.tin, PE.name, pe.tin, t.TypeSort
detail (each typesort has a separate detail table - i'm only working
with 1 detail at this point)
SELECT r.id1,r.id2, recId, accountNo, fieldMask, amt1, amt2
FROM table_10 r
join table2 Po on t.id1 = Po.id1
join table3 Pe on t.id2 = t.id2
order by
po.tin, PE.name, pe.tin, fieldMask, accountNo
my masterfield link for the detail pipeline is based on
id1 and id2.
my report main pipeline is to the master with groups id1, id2 (page
break after id2 changes);
i have a subreport to the detail pipeline. eventually i want to add a
group break on fieldMask on this subreport.
the master data as it appears at run time is:
id1, id2, typeSort
3 17 10
3 14 10
the detail data for typesort 10 table appears
id1, id2, fieldMask, accountNo, amt1, amt2
3 17 1 xxx1 100 200
3 14 0 xxx2 150 250
3 14 1 xxx2 25 100
3 14 1 xxx3 75 25
3 14 2 xxx4 10 0
when i run the report i get
3, 17
all 6 detail records in order as above
3, 14
all 6 detail records in order as above
i was really hoping ;-) for
3, 17
first detail
3, 14
detail 2-6
any light you can shed on this would be great!
thanks!
-martha
This discussion has been closed.
Comments
Looking at your SQL, it does not look like you are sorting your detail band
on the linking field(s). Coincidentally, it looks like the sort order is
descending in which case you would need to set the
TppMasterFieldLink.DetailSortOrder to soDescending for the link to properly
work. You can access the TppMasterFieldLink object at run time using the
Pipeline.Links property.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
just trying to make this too complicated or i cannot use the
masterFieldLinks and let report builder automatically traverse my data.
i'm not sure.
my master query:
select distinct po.tin, po.name1, PE.NAME, pe.tin, t.TypeSort,
t.id1, t.id2, t.retTypeCd, t.retTypeSort, ...
from table1 t
join tablePE Pe on t.ID1 = PE.ID1
join tablePO Po on t.ID2 = Po.ID2
order by po.tin,PE.NAME, pe.tin, t.TypeSort
example of 2 detail queries are:
SELECT po.tin, PE.NAME, pe.tin, r.STMTID, id1, id2, stmtid,
r.FieldMask, r.Acctno, r.accountType, Amt1, amt2
from detail1 r
join tablePE Pe on t.ID1 = PE.ID1
join tablePO Po on t.ID2 = Po.ID2
order by po.tin,PE.RNAME, pe.tin, fieldMask, acctno, stmtid
SELECT po.tin, PE.NAME, pe.tin, r.STMTID, id1, id2, stmtid,
r.FieldMask, r.Acctno, r.accountType, Amt1, amt2
from detail2 r
join tablePE Pe on t.ID1 = PE.ID1
join tablePO Po on t.ID2 = Po.ID2
order by po.tin,PE.NAME, pe.tin, fieldMask, acctno, stmtid
my masterFieldLinks for each of the detail queries is
po.tin -> po.tin
pe.name -> pe.name
pe.tin -> pe.tin
table1
id2 is a foreignkey to tablepo
id1 is a foreignkey to tablepe
table1 is a combination of detail records from our detail1..x tables
stmtid is a primary key on table1, and each detail table (this key value
is shared between table1 and details) and is the unique link between them.
what i'm trying to do is use the master detail table (looking up
customer information) to link to each of the individual detail tables to
display information based on the master/sub customer grouping.
the queries return:
master:
po.tin name pe.tin typesort po.id2 po.id1
91712345 Belinda Sands 000000 1 3 17
91712345 Belinda Sands 000000 2 3 17
91712345 Daryl Drake 921322 1 3 14
91712345 Daryl Drake 921322 2 3 14
91712345 John Smith 000000 2 3 47
91712345 Lizzie Star 678543 2 3 36
91712345 MK Design 983454 2 3 40
91712345 Startling 921322 2 3 44
detail 1:
po.tin name pe.tin fieldMask stmtid po.id2 po.id1
91712345 Belinda Sands 000000 0 14 3 17
91712345 Daryl Drake 921322 1 615 3 14
91712345 Daryl Drake 921322 1 617 3 14
91712345 Daryl Drake 921322 1 11 3 14
91712345 Daryl Drake 921322 1 616 3 14
91712345 Daryl Drake 921322 2 100 3 14
detail 2:
po.tin name pe.tin fieldMask stmtid po.id2 po.id1
91712345 Belinda Sands 000000 0 81 3 17
91712345 Daryl Drake 921322 2 618 3 14
91712345 John Smith 000000 2 108 3 47
91712345 Lizzie Star 678543 0 114 3 36
91712345 MK Design 983454 0 119 3 40
91712345 Startling 921322 0 124 3 44
my output is:
91712345 - belinda Sands
detail1 - stmtid 14
detail2 - stmtid 81
91712345 - daryl drake
detail1 - stmtid 615
stmtid 617
stmtid 11
stmtid 616
stmtid 100
detail2 - stmtid 618
91712345 - MK Design
detail2 - stmtid 119
stmtid 124 <---- this does not belong to this combination
< and what about details for john smith, lizzie star, startling>
i've tried not doing a 'distinct' in the master queriy and including
fieldmask, acctno, stmtid in the orderBy and masterFieldlinks but then
i'm unable to get the grouping i need.
the orderby fields are not keys and can have duplicate values which may
be part of the problem. if i can not use masterfieldlinks how else
should i proceed?
any insight would be greatly appreciated.
thanks!
-martha
When creating field links, you generally want to make a one to many
relationship between the Master field and the Detail field. From what I can
see all of your linking fields are many to many. This is probably confusing
the report engine when traversing your data. Your master dataset does not
seem to contain a unique linking field (i.e. some fields are repeated). I
would strive for a master table that looked like the following in ascending
order based on the id1 and id2.
master:
po.tin name pe.tin typesort po.id2 po.id1
91712345 Daryl Drake 921322 1 3 14
91712345 Belinda Sands 000000 1 3 17
91712345 Lizzie Star 678543 2 3 36
91712345 MK Design 983454 2 3 40
91712345 Startling 921322 2 3 44
91712345 John Smith 000000 2 3 47
Then I would order and link my detail datasets based on the same two fields
(id1 and id2) since the combination of the two seems unique.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com