Variables to calulate hours

[pleashelp]

I still have trouble figuring out variables! I am using CR 9.0 and SQL 2000.

I have a group1=Dept and Group 2 =Employee. My only time field is totalhours. So to get the regular hours I have a formula that says:
if Sum ({table.totalHours}, {table.Employee})>= 40
then 40 else Sum ({table.totalHours}, {table.Employee}) and put this formula in the GF2.

To get the overtime hours my formula says: (Sum(table.totalHours)) - (regularhours) and put this formula in GF2 also.

Now I need to get the total of the regular hours and OT hours for the GF1 Dept total. I assume that I need to use variables for this, but I can't get it to work.

Thanks for any help.

 

[mbarron]

Instead of using variables, take a look at using running totals.

If you really want to use variables:
You'll need 3 formulas (4 in your case since you are adding two variables) - 1 to initialize and re-set the variable, 1 to collect data and on to display the data.

The initializer/re-retter:
Formula one (goes into GH1 and is suppressed)
whileprintingrecords;
numbervar RegHours:=0
numbervar OTHours:=

The collector:
Formula two (goes into GF2 and is suppressed)
whileprintingrecords;
numbervar RegHours:=RegHours + {regular.hours.field}
numbervar OTHours:=OTHours + Sum ({table.totalHours}, {table.Employee}) - {regular.hours.field}

The display fields:
Formula 3 (goes in GF1 and is displayed)
whileprintingrecords;
numbervar OTHours

Formula 4 (goes in GF1 and is displayed)
whileprintingrecords;
numbervar RegHours


Mike

 

[pleashelp]

I have tried to do this using a running total, and I get this message: A running total cannot refer to a printtime total. Details: @Overtime


So I will try these variables. Thanks

 

[pleashelp]

OK. Here are the formulas that I used for the variables.

//this goes into the dept HEADER
whileprintingrecords;
numbervar reghrs= 0;
numbervar othrs = 0

//This goes into the EMPLOYEE group footer
whileprintingrecords;
numbervar reghrs:= reghrs + {@RegularHrs};
numbervar othrs:= othrs+ (Sum ({table.Hours}, {Table.Employee}))-{@RegularHrs}

//This goes into the DEPT footer
whileprintingrecords;
numbervar reghrs

//This goes into the DEPT group footer
whileprintingrecords;
numbervar othrs

What these give me is CUMULATIVE totals instead of one individual total for each department.

I'm sure I am doing something dopey, but I sure can't see what it is?

Thanks again

 

[mbarron]

numbervar reghrs = 0;
numbervar othrs = 0

should be

numbervar reghrs:= 0;
numbervar othrs := 0



Mike

 

[pleashelp]

OKAY!! That works great ! Now just one more little thing....How do I get grand totals for these two fields?

Thanks again!

 

[pleashelp]

Sorry!! I take that back. The first dept total is incorrect , but the next dept total is correct!!!!

 

[mbarron]

Hmmm... Don't know why it would add one correctly and not another one.

Do you have any suppressed Employee footers? The formula will still add those numbers even if the band is suppressed.

How is the first total wrong? Is a value(s) duplicated - not present...

Do you have a "whileprintingrecords;" at the beginning of this formula?
if Sum ({table.totalHours}, {table.Employee})>= 40
then 40 else Sum ({table.totalHours}, {table.Employee})



Mike

 

[pleashelp]

For whatever reason, it is only adding the hours for the employees on one page. It is not picking up the hours on the previous page. So I guess that's why the second dept is correct, but not the first.

 

[mbarron]

A-ha. I forgot about the "Repeat group header on each page" option.

Change the first formula to:

whileprintingrecorders;
numbervar reghrs;
numbervar othrs;

If InRepeatedGroupHeader then
(reghrs:=reghrs;
othrs:=othrs)

else

(numbervar reghrs:= 0;
numbervar othrs:= 0)

Mike

 

[pleashelp]

AAAHHHH!!! That's great! Now I get the correct totals for each department. Thanks so much!

Now back to the other part of it. How do I get Grand Totals for all departments?

 

[mbarron]

Use another set of Running total formula. Put the initializer in the report header the collector in the department footer the the dispay in the report footer.



Mike

 

[pleashelp]

Just for my own edification!!! This is what I did. Using the formulas from the department variables for the OT and Regular hours. Is this the way it should be done?

//this goes into the Report HEADER

whileprintingrecords;
numbervar GTreghrs;
numbervar GTothrs;

If InRepeatedGroupHeader then
(GTreghrs:=GTreghrs;
GTothrs:=GTothrs)

else

(numbervar GTreghrs:= 0;
numbervar GTothrs:= 0)

//This goes into the DEPT group footer

whileprintingrecords;
numbervar GTreghrs:= GTreghrs + {@RegHrs_Display}; //using formula from group variables
numbervar GTothrs:= GTothrs+ {@OThrs_display} //using formula from group variables

//This goes into the Report footer

whileprintingrecords;
numbervar GTreghrs;

//This goes into the Report footer

whileprintingrecords;
numbervar GTothrs;



BTW....THANKS for all of your help! You have been great!

 

[mbarron]

Looks good to me.

Since the formula resides in the report header, your first formula can model my first post.

whileprintingrecords;
numbervar GTreghrs:= 0;
numbervar GTothrs:= 0

Mike