Genius123

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

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

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

sum.rpt

Yeah, mlmcc's formula (without the multiplication)

Sorry for the confusion. :-)

James

__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

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

mlmcc

ASKER

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