chaining 4 pipelines and sub-reports

rbuser

I am trying to create a multi-detail report. Since our data is not
normalized, I have create four queries to do the grouping and aggregation.
I have joined them together in the pipelines as shown below.



My goal is to show a line with the Line totals and aggregates. Within that
there may be 1 or 2 scales, A scale will several drops and a Drop will have
up to four grades.



Scales, Drops and Grades on each line are n ot the same scales, Drop and
Grades on other lines.



Line 1 Totals for Line

Scale 1 Totals for Scale

Scale 2

Drop1 Totals
for Drop


GradeA Totals for Grade


GradeB Totals for Grade


.

Drop2 Totals
for Drop


GradeA Totals for Grade


GradeB Totals for Grade


GradeC Totals for Grade

Drop3 Totals
for Drop


GradeA Totals for Grade


GradeB Totals for Grade

Line 2

Scale 1 Totals for Scale

Scale 2

Drop1 Totals
for Drop


GradeA Totals for Grade


GradeB Totals for Grade


.

Drop2 Totals
for Drop


GradeA Totals for Grade


GradeB Totals for Grade


GradeC Totals for Grade

Drop3 Totals
for Drop


GradeA Totals for Grade


GradeB Totals for Grade











plLine

select lineId, count(RecNum) CountRecNum,

sum(WeightLbs)SumWeightLbs, avg(WeightLbs) AvgWeightLbs

from ProductionData

Where DateWeighed >= '5/4/2005 12:00 AM'

and DateWeighed <= '5/5/2005 12:00 PM'

group by lineid

order by LineID

================================================



plLineScale =>plLine

LineID = LineID



select lineId, scaleID, count(RecNum) CountRecNum,

sum(WeightLbs)SumWeightLbs, avg(WeightLbs) AvgWeightLbs

from ProductionData

Where DateWeighed >= '5/4/2005 12:00 AM'

and DateWeighed <= '5/5/2005 12:00 PM'

group by lineid, scaleID

order by LineID, ScaleID

================================================







plScaleDrop=>plLineScale

LineID=LineID

ScaleID=ScaleID



plScaleDrop

select lineId, scaleID, dropNbr, count(RecNum) CountRecNum,

sum(WeightLbs)SumWeightLbs, avg(WeightLbs) AvgWeightLbs

from ProductionData

Where DateWeighed >= '5/4/2005 12:00 AM'

and DateWeighed <= '5/5/2005 12:00 PM'

group by lineid, scaleID, dropnbr

order by LineID, ScaleID,dropnbr





plDropGrade=> plScaleDrop

LineID=LineID

ScaleID=ScaleID

dropNbr= dropNbr



plDropGrade

select lineId, scaleID, dropNbr, Grade, count(RecNum) CountRecNum,

sum(WeightLbs)SumWeightLbs, avg(WeightLbs) AvgWeightLbs

from ProductionData

Where DateWeighed >= '5/4/2005 12:00 AM'

and DateWeighed <= '5/5/2005 12:00 PM'

group by lineid, scaleID, dropnbr, Grade

Order by lineid, scaleID, dropnbr, Grade



I have tried creating groups for each pipeline and place sunreports within
each break but it is not giving me the correct results. Can you get me
started?



Thanks

rbuser

Okay Digital-metaphors,
I got it wired together on my own.
Thanks for all your assistance for our > 20 Report Builder/Server Licenses.

nardmoseley

Glad to hear you got it working.

This was a long post - that can take extra time for us to read, re-read and
try to understand. Perhaps we should have responded to this on Friday and
simply asked you questions about it. That's my fault, I read the post
probably three times and still did not understand the question - so I put it
off and was going to come back to it today. Sorry about that.

If you ask a specific question about ReportBuilder, the chances that we can
answer quickly are much higher. The shorter, simpler, easier it is to
understand the question, then the quicker we can respond.


--
Nard Moseley
Digital Metaphors Corporation
http://www.digital-metaphors.com

rbuser

Nard,
Sorry for sounding so short. Have been under the thumb for this one.