Crystal Report - sum field group Genius123 used Ask the Experts™
on
totals.rptHi,

I'm trying to sum the field of @Cost per group.   For example, the sum of @Cost for  Heat Barrier Series Glazing Bead should be \$320.92.

Thansk\ for your help....
Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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;
""

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

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

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
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
You can do this with a summary.

Create a formula
{qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGTH} * {qryCWProfiles.SUM_PIECE_COUNT}

Now create a summary of that formula over the appropriate group

mlmcc

Commented:
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
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
Commented:
But he's doing A * (B+C) on every record.  If there are 2 records in the group and B is SUM_PIECE_COUNT from record 1 and C is SUM_PIECE_COUNT from record 2, then the calculation for each record would be (A * record1) + (A * record2).

To use a simple example:
If you had 3 records in the group and the COST, PIECE_LENGTH and SUM_PIECE_COUNT in those 3 records were:
1 1 2
1 1 2
1 1 2

His formula would give him:
1 * 1 * 6 = 6
1 * 1 * 6 = 6
1 * 1 * 6 = 6
Total: 18

Your formula would give him:
1 * 1 * 2 = 2
1 * 1 * 2 = 2
1 * 1 * 2 = 2
Total: 6

James
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
True so it could become

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

mlmcc

Commented:
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

Commented:
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

Commented:
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
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Commented:
Thank you both!  I really appreciate your time.

Do more with Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.