Running Total based on Formula in Group Footer 1

[Crewdawg]

I am using CR10 on a SQL2000 Server via OBDC.
I am creating a summary for a payroll report which summarizes total hours as well as how many of those hours were regular or overtime.

The report is grouped by Employee Code (as in Intials, also user name in DB). I have a running total which adds all of the hours that are applicable to that user code. I then use a fomrula to determine if the #RT is over 40 hours and if it is report 40 hours in Regular Hours column. Then anything over 40 is displayed in Overtime column.

I need to create a Running Total (or sum of) how many hours were regular and how many overtime for the Reoort Footer, any ideas on how I can accomplish this.

FirstN LastN Hours Regular Overtime
Joe Blow 42.15 40.00 2.15
Joe Schmoe 39.45 39.45 0.00
-----------------------------------------------
82.00 79.45 2.15

Bold Items are what I need.

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[lbass]

Create formulas like:

//{@accum} to be placed in the group footer:
whileprintingrecords;
numbervar reg := reg + {@Regular};
numbervar ot := ot + {@Overtime};

//{@totreg} to be placed in the report footer:
whileprintingrecords;
numbervar reg;

//{@totot} to be placed in the report footer:
whileprintingrecords;
numbervar ot;

-LB

 

[Crewdawg]

Here are my formulas. For some reason the totals do not add up.

//1Accum
whileprintingrecords;
numbervar reg1 := reg1 + {@1RegHours};
numbervar ot1 := ot1 + {@1OverHours};
numbervar reg2 := reg2 + {@2RegHours};
numbervar ot2 := ot2 + {@2OverHours};
numbervar totreg := totreg + {@ToTReg};
numbervar totot := totot + {@TotOT};

//1TotOT (All output formulas are the same except for appropriate variable names)
whileprintingrecords;
numbervar ot1;

Week 1 Week 2 Totals
FirstN LastN Hours Regular Overtime Hours Regular Overtime Total Reg Total OT
Joe Blow 42.15 40.00 2.15 42.15 40.00 2.15 80.00 4.30
Joe Schmoe 39.45 39.45 0.00 39.45 39.45 0.00 79.30 0.00
------------------------------------------------------------------------------------------------------------------
82.00 79.45 2.15 82.00 79.45 2.15 159.30 4.30

The items in BOLD are all the ones passed through the variables and do not add up correctly. Using this example (159.30 + 4.30 = 164 Hours)=(82.00 + 82.00 = 164.00). In my report (8513.51 + 2533.24 = 11047.15) <> (5526.56 + 5864.54 = 11400.50) for a difference of 353 Hours. +/- 3% Is to far off for a payroll report.

Any ideas as to why only these totals done through variables are incorrect?

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[lbass]

Did you place {@accum} in the group footer for the name group, and NOT in the detail section?

-LB

 

[Crewdawg]

Yes it is in the group footer.

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[lbass]

I'm guessing you have an outer group, e.g., for {table.dept}. Then you would need a reset formula in the department header, where you set each variable to 0 as in:

whileprintingrecords;
numbervar reg1 := 0;
numbervar ot1 := 0; //etc.

-LB

 

[Crewdawg]

I do not yet have an outer group for dept. I will as soon as I can nail this problem down and present it as 110% accurate. I had considered the problem being a reset formula but where to put it... report header will reset it before they are declared.

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[Crewdawg]

110% = 100% Accurate... Doooh

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[lbass]

Do you have ANY outer groups? That's where the reset goes. If your formulas are correct in the employee code group footer, then I see no reason why the variable wouldn't give correct results--unless maybe you have conditional section suppression on the group footer.

-LB

 

[Crewdawg]

I have no outer groups.

The //1Accum formula from above is in the group footer for the employee code (no other groups specified). I also do not have any conditional supression on the group footer.


Please see my images.

http://www.etnafirerescue.org/crystal/crystalscreen.JPG

http://www.etnafirerescue.org/crystal/repexample.JPG

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[Crewdawg]

FYI There are 3 pages to that report and that is just page 3.

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.

 

[lbass]

Notice that the totals for people who are missing hours in one of the periods are not being carried into the total columns--this is why the results are inaccurate. You need to change your formulas for the total columns to allow for nulls. Don't know what your formulas are exactly, but they should be in this form:

(
if isnull({@2-6-05hours}) then 0 else
{@2-6-05hours}
) +
(
if isnull({@2-13-05hours}) then 0 else
{@2-13-05hours}
)

-LB

 

[Crewdawg]

Adding Null check to the @1Accum was what eventually solved my problem. See below:

whileprintingrecords;
numbervar reg1 := reg1 + (if isnull({@1RegHours}) then 0 else {@1RegHours});
numbervar ot1 := ot1 + (if isnull({@1OverHours}) then 0 else {@1OverHours});
numbervar reg2 := reg2 + (if isnull({@2RegHours}) then 0 else {@2RegHours});
numbervar ot2 := ot2 + (if isnull({@2OverHours}) then 0 else {@2OverHours});
numbervar totreg := totreg + (if isnull({@TotReg}) then 0 else {@TotReg});
numbervar totot := totot + (if isnull({@TotOT}) then 0 else {@TotOT});

Once again your expertise has lead me to the solution of my problems. I aspire to one day have half the skills and knowledge you posses.

-Sean H
----------------------------------------
Multithreading is just one thing after, before, or simultaneous with another.