Subtotals of formula results in group footers 3

[jguida]

My situation involves a payroll report.&nbsp;&nbsp;The end result needs to be total number of regular hours and total overtime by department.&nbsp;&nbsp;In the details section, I have the dept number, employee number and a number of hours field.&nbsp;&nbsp;There are 2 groups.&nbsp;&nbsp;Department and EmployeeID.&nbsp;&nbsp;I have made running totals to figure out the total for week 1 and for week 2 for each employee.&nbsp;&nbsp;Then I made a formula that adds the 2 together after it figures out if the total of each weeks hours is greater than or less than 40 hours.&nbsp;&nbsp;That said, my subtotals by employee works great, but how would I then do that by department?&nbsp;&nbsp;I cannot just do a summary by the hours field by department because that would include all overtime hours.&nbsp;&nbsp;Any ideas?&nbsp;&nbsp;Thanks in advance! <img src=

http://www.Tek-Tips.com/images/happy.gif>

 

[jguida]

PS.&nbsp;&nbsp;I am using CR v8, with a Merant v3.5 ODBC driver for Progress databases

 

[Ina]

You need to do a separate RT by department, resetting it at the department group level.&nbsp;&nbsp;You can't do a summary of employee to get dept total.&nbsp;&nbsp;You can check your results by temporarily displaying dept RT at the employee group level so that when employee resets, dept keeps on adding cumulative.&nbsp;&nbsp;<br><br>To determine if the work was overtime, you'll need another RT counter for number of employees so you can multiply that by 40 and then subtract this number from total numbers to see if Overtime.

 

[jguida]

Ina,<br><br>Thanks for the info, but using the RT in that way would not work.&nbsp;&nbsp;Say you have 3 people who each have 5 hours of overtime each, 45 hours total for the first week.&nbsp;&nbsp;You also have 10 part time employees that each work 10 hours for the first week of the pay period.&nbsp;&nbsp;The total number of hours would be 235 hours.&nbsp;&nbsp;Using your method, we have a count of 13 employees, times 40 hours equals 520 hours.&nbsp;&nbsp;Subtract that from the 235 and the employees would collectively have -285 hours.&nbsp;&nbsp;<br><br>I think the solution to my problem would have some sort of&nbsp;&nbsp;formula that would do the following for each person in the department and then add them together.&nbsp;&nbsp;For instance, my current formula that calculates the total number of regular time hours at the employee level is the following:<br><br>if isnull({#week2}) and {#week1}&lt;40 then {#week1} else<br>if isnull({#week1}) and {#week2}&lt;40 then {#week2} else<br>if isnull({#week1}) and {#week2}&gt;40 then 40 else<br>if isnull({#week2}) and {#week1}&gt;40 then 40 else<br>if {#week1}&gt;40 and {#week2}&gt;40 then 80 else<br>if {#week1}&lt;=40 and {#week2}&gt;40 then {#week1}+40 else<br>if {#week2}&lt;=40 and {#week1}&gt;40 then {#week2}+40 else<br>if {#week1}&lt;=40 and {#week2}&lt;=40 then {#week1}+{#week2} else<br>if isnull({#week1}) and isnull({#week2}) then 0<br><br>How could I get a grand total of the results of this formula by department?&nbsp;&nbsp;Is it possible to name a variable that I could subtotal by department?&nbsp;&nbsp;Thanks in advance.<br>-Jerod

 

[MalcolmW]

An aside - if an emp has Null hours in one week, and exactly 40 in the other, this formula would not return a value.<br><br>To get a RT, you could add a variable like RegHrs, and increment it with this formula<br>ie<br>... then RegHrs := RegHrs + {#week1} else ...&nbsp;&nbsp;etc<br>Then you could display the result in another formula<br><br>WhilePrintingRecords ;<br>NumberVar RegHrs<br><br>If you wanted it to show dept totals, and your report is grouped by department, just reset the variable to zero in the group header<br><br>WhilePrintingRecords ;<br>NumberVar RegHrs := 0<br>

 

[Ina]

Sorry, I simplified your problem.<br><br>How about this: at employee level, you are already calculating whether or not she has OT.&nbsp;&nbsp;At this point, you can also add to a variable,&nbsp;&nbsp;the OT amount.&nbsp;&nbsp;Increment the variable, everytime OT &gt; 0.&nbsp;&nbsp;When the dept changes, you can recall the OT for the dept and the variable will be reset in the group header.<br>

 

[jguida]

OK.&nbsp;&nbsp;I have the variable working now.&nbsp;&nbsp;I just have one last roadbump to go over.&nbsp;&nbsp;My report is grouped by Department and then by employee.&nbsp;&nbsp;Here are my formulas:&nbsp;&nbsp;First I declared the global variable x in the report header which is suppressed with:<br><br>NumberVar x;<br>x:=0;<br><br>Then I use a formula in group footer 2 (employee group) that adds that employees total regular hours to x.&nbsp;&nbsp;<br><br>NumberVar x;<br>x:=x+{@Total Regular}<br><br>That all works great, but when I try to get it into the group footer 1 (department group) it adds the last employees hours one more time.&nbsp;&nbsp;Obviously a newbie question, but is there a way to have it just return the current value of a variable?&nbsp;&nbsp;I tried<br><br>NuberVar x;<br>x:=x<br><br>in group footer 1, but with no success.&nbsp;&nbsp;It returned a 0.&nbsp;&nbsp;This is probably part of the answer to my question, but does the report process the group header and footer for group 1 before it goes on to group 2?&nbsp;&nbsp;That would explain the zero result I suppose.&nbsp;&nbsp;<br><br>Thanks again in advance and for helping me this far.&nbsp;&nbsp;It is greatly appreciated.

 

[jguida]

I got it.  Thanks for all your help!  Sorry to be a darn newbie.

 

[svm]

Hi JGuida,

How did you got it ? I have the same problem. On my grand totals, it is adding the last record again. I'll appreciate any help. Thanks.