Check out the "group by" option you can use when exporting. If you use Cost Code as your "group by" field and have your records already sorted by this field, you should be able to produce an export of just one line for each cost code.
When I have Group By "Cost Codes" it then only shows the entrie for 1 employee on 1 date
Project Number Cost Codes Description Hours Employee ID Work Date 10009 15-1 SAFETY 5 SHEPE 07/08/2011 10009 50-H1 SUPERVISION 21 SHEPE 07/08/2011 10009 520-1-H SLEEVING 10 SHEPE 07/08/2011
Leave the Hours, EmployeeID and Work date fields out of your export.
Add whatever field you are using for Estimated Hours as it appears to be working for you before.
You'll then need to include either summary or calculation fields that correctly return the other three columns of data (hours to date, hours this week and % complete).
I don't know how you've structured your data, so I can't be more specific than that.
I'm having a hard time figuring out how to get the summary field for hours input for the most current week only, any ideas?
An unstored calculation field defined like this:
If ( ( Get ( CurrentDate ) - DayofWeek ( Get ( CurrentDate ) ) = ( Work Date - DayofWeek ( Work Date ) ) ; Hours )
Will only return the hours for the current week and then your summary field can summarize this field for your hours for current week total.
Ok I have both summary fields working, but the report is being really finicky... I'm not sure why sometimes it only shows the code once with the info I need but other times it shows the codes multiple times.
Project Number Cost Codes Description Est Hours Hours TD by Cost Codes Hours TW by Cost Codes 10009 15-1 SAFETY 90 20 10009 50-1 SUPERVISION 3456 23 10009 510-1-H MATERIAL HANDLING 756 29 10009 50-1 SUPERVISION 3456 6 6 10009 510-1-H MATERIAL HANDLING 756 10 16 10009 15-1 SAFETY 90 12 28 10009 50-1 SUPERVISION 3456 12 40 10009 510-1-H MATERIAL HANDLING 756 12 52 10009 15-1 SAFETY 90 20 72
Make sure that you've sorted your records by Cost Codes so that they are grouped by this field's value before you export the records.
That did the trick!
I have one more thing I would like clarification and one last question and it should be working perfectly!
For this equation "If ( ( Get ( CurrentDate ) - DayofWeek ( Get ( CurrentDate ) ) = ( Work Date - DayofWeek ( Work Date ) ) ; Hours )" I'm having a hard time figuring out what it is calculating... will it always use the most current date as the "weekly" values? What it looks like to me is that it will only get the values entered the same day? Although it is working today for values I entered yesterday...
My other question is, I'm trying to calculate the % complete... (hours TD/est. hours)*100, but this isn't working... in my export the column i am exporting for the Hours TD shows as "Hours TD by Cost Code". Is there a way I can get the % complete field to calculate the percentage using that field?
As long as the calculation field is unstored, Get ( CurrentDate ) returns the date from your system clock so this calculation always updates with the current date. The calculation subtracts the day of the week from this date (a number where 1 = Sunday, 2 = Monday, etc.) this converts all dates to the Saturday date of the week before. Dates that fall in the same week will thus show the same "Saturday Date" on both sides of the equal sign and your Hours value is then returned. The Field will be empty for all cases where this is not true.
Hours TD is a summary field, correct?
If so, referring to it in a calculation returns the grand total, not the current cost code based sub total. Use get Summary to get the sub totals:
get Summary (hours TD ; Cost codes)/est. hours * 100
You are extremely helpful! Thanks for everything Phil!
Ok so I know it has been a while since this last post but there is something wrong with my program.
The "Hours for Current Week" are adding up each week. Its not distinguishing that only the hours entered in the current week are to be kept in the field.
I have been trying to play with it and figure it out for hours so any help is appreciated.