Link to home
Create AccountLog in
Avatar of Genius123
Genius123Flag for United States of America

asked on

Crystal Report - sum field group

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....
Avatar of James0628
James0628

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
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Avatar of Genius123

ASKER

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.