Crystal Report - sum field group

Genius123
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
""

Open 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

Open 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

Open 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
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}

Open in new window


Now create a summary of that formula over the appropriate group

mlmcc
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
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Mike McCrackenSenior 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
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

Author

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
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
Mike McCrackenSenior 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

Author

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

Do more with

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

Start 7-Day Free Trial