Custom project report/analysis

[Riko Wichmann]

Dear all,

I try to get the following report/summary/analysis out of MS Project -
either from Project itself or through an Excel export.

For the top level tasks (which are our so called work packages) I need
to see the planned investments (material resource), the personnel
costs (work resource * cost rate) and the total costs in time slices
(say quarters) over the full project life time. Investments and
personnel costs are calculated via custom fields using total cost and
work in hours.

So far, I managed to get either all the fields I want printed for all
top level tasks for the whole project life time (not sliced in
quarters) or just work and cost in quarter slices (but not the
customized fields I need).

I played with the reports, tables, views (both predefined and
customized) and the excel export without getting the desired results.
The excel export would mean to do all the calculations in excel which
is quite a lot of handy work since it's a big project. Since we are
still iterating the planning this is not really an acceptable
solution.

If anybody has any hints or ideas how to get this kind of table in an
automatic way, I would really appreciate it.

Thanks and Cheers,

Riko

 

[Frank]

From what I've seen in the help, you need the Anaylis tool
bar then analyze Time Phase Data in Excel selection.
However, I'm having trouble getting the Analysis toolbar
altogether. If you have luck getting it, let me know how
you got the toolbar.

 

[jane]

Hello,
I downloaded the tool bar and didn't have any problems.
Have you displayed the toolbar?

 

[John]

Riko,
By using the "analyze timescaled data in Excel" add-in you can get
timescaled data (similar to the Resource Usage or Task Usage) view but
you will not get timescaled data for custom fields because they are
static fields. You mention personnel costs as being Resource Work *
Resource Rate. That is exactly what the Cost field is (assuming nor
material or Fixed Costs are included on the same task) so why do you
need a custom field? Also be aware that the add-in does not export the
rolled up summary line values.

You also mention that even if the data were in Excel, it would mean a
lot of "handy work". That need not be the case. It isn't all that
difficult to write a custom VBA macro to export timescaled data from
Project to Excel AND perform whatever calculations are necessary
automatically. I do it all the time.

If for some reason the 'Analysis' toolbar is not available when a blank
area of the toolbars is right clicked, it probably means that one of the
options was not loaded when Project was installed. Try running the
installation again and add components as necessary. The add-in is also
available as a download from Microsoft at:
http://microsoft.com/downloads/details.aspx?FamilyId=A7E06469-ED3E-4DAB-8
EFD-4BAE1B918EC3&displaylang=en

Hope this helps.
John

 

[Riko Wichmann]

Hi Frank,

I had no problem with the analysis tool bar: just went to View ->
Toolbars -> Analysis an there it was. However, it still is not quite
what I need, but getting closer

Cheers,

Riko

 

[Riko Wichmann]

Hi John,

By using the "analyze timescaled data in Excel" add-in you can get
timescaled data (similar to the Resource Usage or Task Usage) view but
you will not get timescaled data for custom fields because they are
static fields. You mention personnel costs as being Resource Work *
Resource Rate. That is exactly what the Cost field is (assuming nor
material or Fixed Costs are included on the same task) so why do you
need a custom field? Also be aware that the add-in does not export the
rolled up summary line values.

And here we have the problem. I'm in fact only interested in the rolled
up summary task corresponding to our high level WBS. The details
planning of the different work packages (as we call them) is (at the
moment of no concern).
However, that also means, that the Cost field is the sum of material
cost and work cost (we don't have fixed).

You also mention that even if the data were in Excel, it would mean a
lot of "handy work". That need not be the case. It isn't all that
difficult to write a custom VBA macro to export timescaled data from
Project to Excel AND perform whatever calculations are necessary
automatically. I do it all the time.

I already thought, that I would have to dive into the VBA programming.
It seems to me that if I get the rolled summary tasks work and cost
fields in table ion the desired timescale, an Excel would be the better
way to go. Especially, since further analysis of this data for
presentation would be done in Excel anyhow.

Thanks for your help. At least now I know, that I didn't miss anything
not getting the custom fields exported the way I wanted.

Cheers,

Riko