RESOURCE USAGE DATA

[Lamar Atkinson]

I am trying to extract data from Project Professional 2003 to compare planned
hours to hours charged in our time system. I intend to do the comparison in
Excel via a VLOOKUP Function, but need the data from project in order to do
this. The data needs to be in a table like this:

Task ID Resource Name Units Duration Rem. Duration Text1

1 Pipe 2 4 2 A12345
2 Inst 1 2 1 A12346
3 Elec 2 8 6 A12347
1 Weld 1 2 2 A12345

Note that the 1st and last records are the same except a different resource
record is shown. The 'Text1' field is what I need vor the Vlookup to compare
with our time system.

Any ideas on how to get this data? I have tried table views and reports to
no avail.

Thanks in advance for any help.

 

[JulieS]

Hello Lamar,

You can export the data you suggest through the standard export
wizard. Start with File > Save As, use a new map and select "Tasks"
on the Map Options page -- also make sure you check the option on
the Map Options to "Include Assignment Rows in output" On the Task
Mapping Page select the Task fields - ID, Name, Assignment Units,
Duration, Remaining Duration, and Text1). Save the map. Export to
Excel and you'll have what you describe below.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Lamar Atkinson]

Julie - this helps and might be a way to go. Only issue is the way the data
comes across. It has one record that contains all resources and the sum of
their work, followed by the individual assignments and their individual work.
Only the first line contains the 'Text1' field. It is almost like the
report imposes a 'by' statement. If I can figure our a way in Excel to
replicate the Text1 value for each record I may be able to use this.

Thanks for the response

Ony other ideas?

 

[Lamar Atkinson]

Julie - I forgot to include an example of the data:

137 RT042457-05 COMBO WLD-1,PIPEFITTER-1[2] 45 hrs 0 hrs
5 PIPEFITTER-1 2 30 hrs 0 hrs
6 COMBO WLD-1 1 15 hrs 0 hrs

 

[JulieS]

HI Lamar,

The Text1 field is a task field -- the rows with the assignments are
assignment fields -- not task fields, so the data in the Text1 field
only shows on the Task line in the export.

Jack Dahlgren posted a bit of VBA code to copy data from Task fields
to Assignment fields. If you run that macro before exporting the
data, it should copy the Task data to the assignment fields for you.

Look for Jack's macro at:
http://masamiki.com/project/macros.htm

See the link named "Copy Task Fields". You'll need to edit the code
to refer to Text1 instead of Text5.

I hope this helps. Let us know how you get along.

Julie
Project MVP

Visit http://project.mvps.org/ for the FAQs and additional
information about Microsoft Project

 

[Lamar Atkinson]

Julie - the macro you put me on to really helped! I still have the issue
with the 'roll up' records that contain all the resources etc. Any idea on
how to eliminate them from being in the export?

Thanks for all your help

 

[JulieS]

Hi Lamar,

Glad to know Jack's macro helped. I'm not sure you really want to
eliminate the Task lines from the export as they are the records
which contain your duration and remaining duration values. If you
want the work and remaining work you should be all set with the
assignment lines, but you wouldn't know which tasks the assignments
belonged to.

As a suggestion, try including the Assignment field in the export
map. For task rows it will say "no" for assignment rows, "yes".
You could then use autofilters in Excel to hide those rows.

Julie