Export the Resource Usage view from MS Project to Excel

[Tobias]

Hi,

Is their a possibility to have that same view of data exported to Excel,
i.e. Resource Name with all linked tasks and amount of by each day of the
week?

Kind regards
Tobias

 

[John]

Hi,

Is their a possibility to have that same view of data exported to Excel,
i.e. Resource Name with all linked tasks and amount of by each day of the
week?

Kind regards
Tobias

Tobias,
Yes but it will vary depending on which version of Project you are
using. For Project versions 2000 through 2003, you will need to use the
"Analyze timescale data in Excel" utility found on the Analysis toolbar.
If you are using Project 2007, you can use the Visual Reports feature
found under the Reports menu. I haven't tried the beta version of
Project 2010, it may have even more flexibility.

With any version of Project, you can always use VBA to export whatever
data you need. This is the most flexible and automated approach but it
does require knowledge of Project VBA.

Hope this helps.

John
Project MVP

 

[Tobias]

OK, thanks for your feedback. I'm using 2007 and the issue I have is that I
would like to export similar information as I can find in the Resource Usage,
i.e. Named Resources with tasks and hours on a daily/weekly basis.

I've tried by using "save as" and modifying different maps but I can't get
the day by day view combined of task data and resource data:

Named Resource, Custom Resource fields like a "Role" field, Tasks,
Allocation of hours per day/week/month

I guess I need to use VBA to achieve that?

I'm also trying to achieve this via using Visual Reports. However, using
these templates I'm not able to add any custom fields to these templates or
even include them in new tempates which I tried to create.

Kind regards
Tobias

 

[Andrew Lavinsky]

if you didn't have the requirement for custom fields, I'd say that Visual
Reports will get you what you need. The problem is that, as you've figured
out, it's hard to get custom fields to export into Visual Reports - which
is a feature I would consider to be a bit buggy.

Perhaps consider using a combination of Save As for the custom fields and
Visual Reports for the Usage View - then merging them in Excel....or VBA
as has already been suggested.

- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

 

[Andrew Lavinsky]

One more option that I forgot about is that you can save the file as an Access
..mdb file. I was able to do that with a custom field w/ little difficulty.
Just look at Visual Reports, Save Data, Save As Database - and remember
to add your custom field. Then I suppose, you just need to build a query/report
in Access.


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

 

[John]

OK, thanks for your feedback. I'm using 2007 and the issue I have is that I
would like to export similar information as I can find in the Resource Usage,
i.e. Named Resources with tasks and hours on a daily/weekly basis.

I've tried by using "save as" and modifying different maps but I can't get
the day by day view combined of task data and resource data:

Named Resource, Custom Resource fields like a "Role" field, Tasks,
Allocation of hours per day/week/month

I guess I need to use VBA to achieve that?

I'm also trying to achieve this via using Visual Reports. However, using
these templates I'm not able to add any custom fields to these templates or
even include them in new tempates which I tried to create.

Kind regards
Tobias

Tobias,
It looks like Andrew gave you a few options to try before going to VBA
but as I indicated, VBA gives the most flexibility and once the macro is
created, VBA is the easiest to use.

The main reason spare fields are not available when exporting timescaled
data is that Project has no idea how to "spread" the data in the
customized fields. I suppose they could be exported as static value
fields in addition to the timescaled data but the developers didn't see
fit to provide that option.

John
Project MVP

 

[Tobias]

Hi John and Andrew,

Thank you very much for your feedback. It's very helpful for us to
understand the limitations and our possibilities of setting up our MS
Projects.

We are using 2007 but not MS Project Server and also no experience of the
2010 beta version.

We will start by trying the Access DB approach.

I assume it would not help our reporting requirements if we used MS Project
Server?

Kind regards
Tobias

 

[Andrew Lavinsky]

Actually, with Project Server you could put what you need into an OLAP cube
in SQL Analysis Services, then surface the data either in the Data Analysis
View or using Excel & Excel Web Services.


- Andrew Lavinsky
Blog: http://blogs.catapultsystems.com/epm

 

[mafify95]

when i make a visual report resource usage " Resource work summary" and after i add custom field the data of custom field didn't read and equal zero in pivot Table in Excel

Can U Help me, please?

thanks advanced