Crystal Report - sum field group

CR won't do a summary on a summary.  Your Cost formula uses Sum (), so CR won't summarize that formula.  Unless you can calculate the cost without using the sum for SUM_PIECE_COUNT, you'll have to use a variable to calculate the total cost manually.

 From your post, it seems that you're trying to get a total for group 2.  Do you also need a total for any other group, or for the entire report?  If so, you'll need a separate variable for each total.

 Where do you want to display the total cost?  The variable will be updated as the records are "printed", so you won't have a total until you get to the last record in the group/report.  IOW, the total will have to be displayed at the end of the group/report.

 With all of that in mind, this will get you a total for group 2:

 Create a formula like the following (call it whatever you like) to initialize the variable:

WhilePrintingRecords;
Global NumberVar total_cost;
total_cost := 0;
""

Select allOpen in new window

 The "" at the end is just so the formula doesn't produce any visible output on the report.  You could also put the formula in a suppressed section, or suppress that field on the report.  In my experience, the formula will still be evaluated if it's suppressed.

 Put that formula in a report header section (to declare the variable for the report), and in GH2 (to reset the total for each group 2).

 Change your Cost formula as follows:

WhilePrintingRecords;
Global NumberVar total_cost;
Local NumberVar cost;

cost := {qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH} * Sum ({qryCWProfiles.SUM_PIECE_COUNT}, {@AngleAndLength});
total_cost := total_cost + cost;
cost

Select allOpen in new window

 The cost variable is just so the formula doesn't have to calculate the cost twice (once to add it to total_cost, and once to display the cost on the report).

 Create a formula like the following (call it whatever you like) and put it in GF2 to display the total cost for group 2:

WhilePrintingRecords;
Global NumberVar total_cost;
total_cost

Select allOpen in new window

 That should give you a total for group 2.  Like I said, if you need totals for other groups, or for the entire report, then you'll need additional variables.

 James

mlmcc,

 Unless I'm missing something, that won't work.  You're calculating a value for the detail records and summing the result.  Changing the calculation to use the individual SUM_PIECE_COUNT, instead of the group total, will obviously change the detail values, giving you a different (probably much smaller) total.

 James

Remember the distributive formula

A * B + A * C = A * (B+C)

A = {qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH}
B and C are {qryCWProfiles.SUM_PIECE_COUNT}
 B + C = Sum({qryCWProfiles.SUM_PIECE_COUNT})

By resetting on each group it is the group sum

mlmcc

True so it could become

Count({qryCWProfiles.COST}) * Sum({@NewFormula})

mlmcc

That works for my example, but only because it has the same values (for both A+B and C) in each record.  If they're not the same, it doesn't work.  For example:

1 1 2
1 1 2
1 2 3

1 * 1 * 7 = 7
1 * 1 * 7 = 7
1 * 2 * 7 = 14
 Total: 28

1 * 1 * 2 = 2
1 * 1 * 2 = 2
1 * 2 * 3 = 6
 Total: 10
 10 * 3 = 30

 James

Guys, this all started to get really confusing for me.  I finally figured it out by making this formula as mlcc suggested, then summing it and then putting it in group footer 2.  

{qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH} * {qryCWProfiles.SUM_PIECE_COUNT}

I think mlcc would get credit for this. Please let me know if you disagree.  I've attached the file too.

sum.rpt

Yeah, mlmcc's formula (without the multiplication) does seem to work.  I tried it in the first report that you posted (that included some sample data) and got the same results that I got when I used a variable.  This confused me greatly.  :-)  I finally figured out where I went wrong.  You're calculating the cost (using the @Cost formula) at the group 4 level, but I got it in my head that you were working at the detail level.  Doing the calculation at the group level changes things, and I think mlmcc's simple summary will take care of it.

 Sorry for the confusion.  :-)

 James

James - Your arguments made sense and I forgot the values were being calculated at the group footer level.

James did provide a reasonable solution.  You could split the points between the 2 and choose your final comment as the solution.

mlmcc

Thank you both!  I really appreciate your time.