Brain Cramp on Simple Sum.

[itsgenekramer]

Hi. Please forgive me but it's been one of those days. Actually 5 of those days if you know what I mean. What I'm trying to do is get a sum of all employees time by paycode. For example, if the employees worked during the date parameters that are selected and they had a paycode of 101 or 102, I want to add up the minutes for those 2 paycodes. Then I want to add up another set of paycode minutes with different paycode ID's, for instance 103, 104, 105,... So 101 and 102 would be productive and anything else would be non-productive. Then I need to take the productive minutes and divide that by the productive minutes + the non-productive minutes.

Any help is appreciated.

Gene

W2k, SQL 7, CR 8.5, ODBC

 

[question911]

How about using "Running Totals." First create a running total. Second, in the "Running Total" object you just created, choose the minutes field as your "field to summarize" and select "sum" as your type of summary. Third, select "use a formula" in the "Evaluate" section. add this to your formula, paycode in [101, 102]. In the "Reset" section, select Employee drop down list on the "change of Group.". I am assuming you will be creating a group based on Employee ID or something of that nature.

P.S, You can take the same approach with your other request you mentioned in your post.

 

[lbass]

If you use the running total approach, you would then create a formula that divides the "Productive" running total by the sum of minutes per employee:

{#productive}/sum({table.mins},{table.employeeID})

Place it in the group footer where the running totals are located.

Another approach would be to group on {table.employeeID} and then create three formulas:

//{@productive}:
if {table.paycode} in ["101","102"] then {table.mins}

//{@nonproductive}:
if not({table.paycode} in ["101","102"]) then {table.mins}

Then right click on each formula and insert a summary (sum) to display results at the group level.

//{@ratio}:
sum({@productive},{table.employeeID})/
sum({table.mins},{table.employeeID})

This assumes that {table.mins} is a number field.

-LB