how to best approach the report layout

rbuser

hello again. i have managed to rewrite all but 1 of our reports to
eliminate the master/detail relationship because of performance. i now
need some help determining how best to approach this final report design
from a single query containing all data (instead of our previous
master/detail) - maintaining grouping.

the data from this query is as follows:
Vendor, company, type, mask, amount1, amount2 ...
grp1 grpA A 1 400 200
grp1 grpA A 1 200 300
grp1 grpA B 1 300 300
grp1 grpB A 1 100 400
grp1 grpB B 1 200 300
grp1 grpC B 2 100 100

previously our master had the vender and company information and was
linked to the detail via the pipeline link.
the main/master report had 2 groups - vender (group1) and company
(group2) - with start new page on group2.
there were 3 child subreports (one for each type - of which there are a
possible 3) - so the detail could contain any or all 3 subreports.
each subreport then was grouped on the mask field.

i haven't been able to get the desired output from a single query approach.


the output we need is as follows:

grp1
grpA
typeA 400
200
typeB 300
------
new PAGE
grp1
grpB
typeA 100
typeB 200
100
NEW PAGE
grp1
grpC
typeB 100


what appears to be happening is i get all data for group1/group2
combination -
grp1
grpA
typeA 400
200
100
100
grp1
grpB
typeA 400
200
100
100

any suggestions?
thanks!
-martha

nicocizik

Hi Martha,


I have not heard of this issue. Which database/connectivity are you using?
Is the data being accessed over a network?

I'm a bit confused about your output. Are you printing amount1 or amount2
or both? Are you trying to keep these groups together? (i.e. the
KeepTogether property is set to True?

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

sorry this is a bit confusing. in the original master/detail report the
master report was grouped on (example) vendor (group1) company (group2)
group1's options were keepTogether = false; newpage = false;
reprintonsubsequentpage = false; resetpageno=false;
group2's options were keeptogether false; newpage true;
reprintonsubsequentpage = true; resetpageno=true;

the detail contained 3 child subreports - each using a tppdbpipeline to
a tdatasource to a tiboquery; each were masterlinked on the
tppdbpipeline. each subreport contained a group on a field 'fieldmask'
and had a group header and group footer that displayed calculated db
fields (sum) of the detail records for that group. and each subreport
would only print if there was a corresponding detail record for that type.

within each of these detail subreports - they also contains 2-4
subreports - the value of the subreports' group fieldmask would
determine which subreport would print.

so the result would be
vendor1 company1
type B
fieldMask 0
amount1 amount2 amount3
fieldMask 1
amount1 amount2 amount3
amount1 amount2 amount3
tot(1) tot(2) tot(3)
type I
fieldMask 1
amount1 amount2 amount3
fieldMask 2
amount1 amount2 amount3
amount1 amount2 amount3
tot(1) tot(2) tot(3)
type O
fieldMask 0
amount1 amount2 amount3

new page
vendor1 company2
typeB
fieldMask2
amount1 amount2 amount3 ....etc

this was hard enough to do with the master/detail relationships, but
because of performance issues in master/detail relationships approach i
was forced to rework all our reports to use a single query. until now
this didn't present an issue (since all other reports have a single
record per page and i was able to use a single query and use section
subreport for each different type).

the report i'm working on is more of a summary - many details to a
master on a single page. since i only have a single query i'm having
difficulties with the grouping so that all records for that
vendor/company group print the details in the correct order with group
headers/footers... which because there isn't a master/detail this has
proven extremely difficult. i can verify the query records are in order
but i haven't been able to get the processing to recoginize groups. if
i assign the datapipeline to the subreports it appears i get all records
regardless if they are for the group or not. if i do not assign a
datapipeline the details print individually - not grouped together. sigh...

hoping there was a best approach for using single queries and groupings.

thanks!
-martha

rbuser

well i have managed to get it almost working - just not the calcdb items
in a group footer. the report layout is as follows:
a single tiboquery to a tdataset to a tppdbpipeline to the tppreport

^group header[0]: payorpkid
subhdrpage1:no data pipeline assigned

subhdrpagex:no data pipeline assigned

^group header[1]: payeepkid
^group header[2]: typeCode
subFldMaskHdrB : no data pipeline assigned
region1 : visible if fieldMask = 1
region2 : visible if fieldMask = 2
subfldMaskHdrI : no data pipeline assgined
region1 : visible if fieldMask = 1
region2 : visible if fieldMask = 2
subfldMaskHdrO : no data pipeline assigned
region1 : visible if fieldMask = 1
region2 : visible if fieldMask = 2
region3 : visible if fieldMask = 3
^group header[3]: fieldMask
subReportB : no data pipeline assigned
subBFieldMask0 : no data pipeline assigned
subBFieldMask1 : no data pipeline assigned
subBFieldMask2 : no data pipeline assigned
subReportI : no data pipeline assigned
subIFieldMask0 : no data pipeline assigned
subIFieldMask1 : no data pipeline assigned
subIFieldMask2 : no data pipeline assigned
subReportO : no data pipeline assigned
subOFieldMask0 : no data pipeline assigned
subOFieldMask1 : no data pipeline assigned
subOFieldMask2 : no data pipeline assigned

subOFieldMask3 : no data pipeline assigned
^detail
subFtrB : no data pipeline assigned
regionB1 sum(amount1) sum(amount2) ... resetgroup group[3] fieldmask

regionB2 sum(amount1) sum(amount3) ... resetgroup group[3] fieldmask

subFtrI : no data pipeline assigned
regionI1 sum(amount1) sum(amount2) ... resetgroup group[3] fieldmask

regionI2 sum(amount1) sum(amount3) ... resetgroup group[3] fieldmask

subFtrO : no data pipeline assigned
regionO1 sum(amount1) sum(amount2) ... resetgroup group[3] fieldmask

regionO2 sum(amount1) sum(amount3) ... resetgroup group[3] fieldmask

regionO3 sum(amount1) sum(amount4) ... resetgroup group[3] fieldmask

^group Footer[3]: fieldMask
^group Footer[2]: retTypeCD
subInstructionsB : no data pipeline assigned
subInstructionsI : no data pipeline assigned
subInstructionsO : no data pipeline assigned
^group Footer[1]: payeepkid
^group Footer[2]: payorpkid

^Footer


this gets me close to what we need, however, the calcdbs in the group[3]
footer do not sum the groups detail records - rather it just contains
the last details values. any suggestions on how to get totals for the
group's details?

thanks!
-martha

nicocizik

Hi Martha,

Instead of using DBCalc objects, try using a couple TppVariables. For
instance, place one invisible TppVariable inside the detail band and in its
OnCalc event update the value of the other TppVariable inside the group
footer.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com