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....

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....

Crystal Reports

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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

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

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

A = {qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGT

B and C are {qryCWProfiles.SUM_PIECE_C

B + C = Sum({qryCWProfiles.SUM_PIE

By resetting on each group it is the group sum

mlmcc

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

True so it could become

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

mlmcc

Count({qryCWProfiles.COST}

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

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

{qryCWProfiles.COST} * {qryCWProfiles.PIECE_LENGT

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

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

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

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.

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:

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:

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:

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