Exporting Actual Work by Day or BiMonthly

[John Napoli]

I want to export actual hours for analysis, reconcilliation to the company
time system, and use in creating billing justification. Resource usage view
gives me a start only:
e.g.
Mon 8/1 Tue 8/2 Wed 8/3 Thu 8/4 ...
Adenuga 7 8 0 0
Brown 8 8 8 8
.....
I want it like this:
Name Date Hours
Adenuga 8/1/2005 7
Adenuga 8/2/2005 8
Adenuga 8/3/2005 0
Adenuga 8/4/2005
....
Adenuga 8/15/2005
Brown 8/1/2005 8
Brown 8/2/2005 8
Brown 8/3/2005 8
Brown 8/4/2005 8
....

Or Better (the categorization is in task text3):
Name Date Admin Plan Mtgs Interview FC RCM Rvw
Adenuga 8/1/2005 4 3
Adenuga 8/2/2005 8
Adenuga 8/3/2005
Adenuga 8/4/2005
....
Adenuga 8/15/2005
Brown 8/1/2005 8
Brown 8/2/2005 8
Brown 8/3/2005 8
Brown 8/4/2005 8
....

Any Ideas?

Thanks,

John Napoli
Senior Associate, KPMG LLP Risk Advisory Services
345 Park Avenue
New York, NY 10154-0102

 

[John Napoli, PMP]

I found a MANUAL solution:
I. Repeat for every time period (for me the 15th and end of month):
a: Copy Resource Usage View to Excel:
1. Filter the view to include only tasks that start before the end of the
period and finish after the begining of the period.
2. From left pane, copy Resource Name, ID, Task Summary Name to columns
C,D,E in excel
3. From Right pane, copy the time data into columns G-V

b: Fix spreadsheet
1. Copy appropriate resource name to column B for every task.
2. Put time period end date in Column A
3. Calculate total in column F by adding columns G-V
4. Replace " h" in the time area with ""
5. Add a header row. For the day data the columns get numbers 1-16 for the
number of the day in the period.

c. Copy Task view to Excel
1. Hide summary tasks via tools/options
2. Create a view showing an unprotected ID coulmn and other fields (I need
text 3 to categorize the time).
3. Copy the columns to excel columns B-...
4. Put time period end date in Column A

d. Import to above spreadsheets to Access
1. use normal Access import function

e. Run queries
1. Hours by day: I just filter the loaded Resource usage data where the task
summary name is null. These rows are the resource total rows

2. Categorized time
a. Create a query joining the two spreadsheets on the id column and the
period end date column. Display the columns you want (for me resource name,
total and text 3). Exclude rows for just resource names (task summary name <>
null).
b. Create a crosstab query where resource is row heading, text3 is column
heading, total is the value and a period total is calculated.

f. copy query results back to excel and / or powerpoint.

Before using project to gather actual hours, a manual spreadsheet based
process took 2-3 days to create the above reports. With the actual hours in
Project and the above manual process, it can be done in 1/2 to one hour. It
is still a bit maunal intensive, but a big improvement.