Need to get distinct counts and then sum them

I am using Crystal Reports 2011.  I have a report that lists all the rolls of material on an order.  I have three groups: Order Number, Line Number, Pallet ID so that all pallets for a particular line item are under that line and all rolls on a particular pallet are under that pallet.

I now need to get a count of all the rolls for each roll length.  For example, I have an order that has one line item with three pallets: one pallet with three rolls (40yds, 60yds, 80yds) and two pallets with five rolls on each (all ten rolls are 80yds ea).  I need a summary in Group Footer 2 (Line Number group) that says:
1 roll @ 40 yds
1 roll @ 60 yds
11 rolls @ 80 yds

How can I accomplish this?


Edited by GrisCorp - 11 May 2016 at 11:22am

I am assuming that there is a field that designates the roll length.  Make three running totals (one for each length) to do a count (Pallet ID?).  Evaluate when the field (for the roll length) matches the correct value, Reset on the GF2.

kevlray,

Yes there is a field that designates the roll length.  If I understand correctly, making a Running Total for each length would not work for other parts as the lengths are not the same for every part or even every order.

I solved it.  I just used a subreport.  I passed the order number and line number over and then grouped by the length field.  I suppressed all sections except the group footer.  I left the Group Name in the footer and put a Count of the length field into the GF.

I created a formula called @Roll to use "roll" or "rolls" based on the count:
If Count ({Table.Length},{Table.Length}) = 1
then
"roll"
else
"rolls"

I then added two text fields containing:

1. {Count of Table.Length} {@Roll} @
2. yds
   

and arranged everything like this in the GF:
Text_Field_1 {GroupName} Text_Field_2

so that I get this:
    1 roll @  40 yds
    1 roll @  60 yds
 11 rolls @  80 yds


Hopefully this will help someone in the future with a similar need.