Trying to do a running total on a summarized group field

[tbrandao]

Hi,

I'm having problems getting the "grand total" for a field that is summing estimated costs. I get the running total in the details section as a "check", but when I try to use the field to create a Grand total to be placed in the Report footer, the calculations are off. Can someone help me? Here is some of my data. I'm Grouping by County, then by Classification. Group footer 2- has totals for total requests, proj estimated cost, and average estimated cost. The group footer 1 sums the total requests by County. My Report footer sums each of these fields, except the average project field. Can someone please help me?

County
Montgomery
Building
Total Requests Estimated Proj Cost Average Proj Costs
3 $221,500.00 $73,833.33

Building/Highway
Total Requests Estimated Proj Cost Average Proj Costs
1 $850,000.00 $850,000.00


Highway
Total Requests Estimated Proj Cost Average Proj Costs
2 $554,000.00 $277,000.00


I created another column to sum the Average Proj Costs and it works great in the group footer, but I need it to sum and put the value in the Report footer. Any ideas, please? Thanks in advance.

 

[lbass]

Please show us the formulas you are using so far for summing the average costs.

-LB

 

[tbrandao]

Here is the "experimental" code to sum the average cost. It works fine in the detail section, but when I try to use the formula and place it in the Report Footer, it adds the last value twice.

WhilePrintingRecords;
shared currencyVar total;

If GroupName ({request.PROJ_LOC_CNTY}) = GroupName ({request.PROJ_LOC_CNTY}) AND GroupName ({request.PROJ_CONSTR_CLASS}) = GroupName ({request.PROJ_CONSTR_CLASS})

Then total := Average ({@Estimated Proj Cost}, {request.PROJ_CONSTR_CLASS});

 

[tbrandao]

Here is a second formula that I created as a "check" or option to use, but again when I use this formula and place it in the Report Footer, it adds the last value twice.

WhilePrintingRecords;
currencyVar total;

total := total + CCur ({@AVG Proj Cost})

 

[tbrandao]

Please disregard the formula for the summing the average cost that I first sent you:

WhilePrintingRecords;
shared currencyVar total;

If GroupName ({request.PROJ_LOC_CNTY}) = GroupName ({request.PROJ_LOC_CNTY}) AND GroupName ({request.PROJ_CONSTR_CLASS}) = GroupName ({request.PROJ_CONSTR_CLASS})

Then total := Average ({@Estimated Proj Cost}, {request.PROJ_CONSTR_CLASS});



The correct "experimental" code is below.
CCur ({@Sum AVG Proj Cost})

This sums the code that is copied directly above the CCur({@Sum AVG Proj Cost})

 

[lbass]

If you are not using subreports, you don't need to use shared variables. After removing your current formulas, you should set up formulas like this:

//{@reset} to be placed in the group header #1:
whileprintingrecords;
currencyvar grptot;
if not inrepeatedgroupheader then
grptot := 0;

//{@accum} to be placed in the Group #2 group header or group footer:
whileprintingrecords;
currencyvar grptot := grptot + ccur(Average ({@Estimated Proj Cost}, {request.PROJ_CONSTR_CLASS}));
currencyvar rpttot := rpttot + ccur(Average ({@Estimated Proj Cost}, {request.PROJ_CONSTR_CLASS}));

//{@displaygrptot} to be placed in the Group #1 footer:
whileprintingrecords;
currencyvar grptot;

//{@displayrpttot} to be placed in the report footer:
whileprintingrecords;
currencyvar rpttot;

Note that you have to use a separate display formula as above instead of adding the same formula to the footer, because otherwise it will calculate again, using the last value in the group.

Note also that your groupname comparisons are only saying "if a = a and b = b" which will always be true. I think you were trying to deal with the need to reset, but that is taken care of with the separate formula.

-LB

 

[tbrandao]

THank you. I was trying anything and everything that I thought would work in "my perfect world". lol. I will let you know how it goes.

 

[tbrandao]

Thank you so much. That worked just fine. I've been working on this for over a week. I see by your examples where I made the mistakes. You are awesome. Thank you so much again.