Exporting time phased task information to Excel

[JPD]

Ok, let me see if I can explain my situation.

My company has a system we use for doing Earned Value for most of our
projects. I am working a project has some special circumstances.
1: Resources working the program are not always charged to the
customer there "normal rate" for example: I may be listed with my
company with a rate of X, however we charge the client for my time a
rate of Y. This means we do all of our resource mapping and cost
calculations in Excel.
2: Our client funds us incrementally, so this requires a lot of
"sorting" of different activities into "Work Packages", calculating
the cost, then requesting funding, etc.
3: Client likes to see things on a weekly basis.

So I guess the question I have is an easy way to get things out of
Project into an Excel sheet in a list or Database format. Ideally
formatted by weekly cost (or hours).

I have entered rates into Project so cost that it generates is
correct.

For example it would be nice to get an export that would be
essentially a database or list to do Pivot Tables with these columns:

ID - Chrge Num(text5) - WP(text4) - Task Name - Date (week starting or
week ending) - Cost

The key to the list would have each task show a different line with
the cost for that week. I need to look at Time Phased cost by Work
Package or Chrge Num. I have found a way to do it by exporting after
filtering by each charge num, but this is cumbersome with the number
of charge num & WPs.

Basically I need a customizable EVM system using Project .... if there
are off the shelf systems out there I could use I'll take
recommendations on that also.

Thanks!
J

 

[Jim Aksel]

Project2007 does pretty good with Pivot Tables and time phased data. You can
download a free trial.

As for differing rates, consider use the cost rate tables in MS Project. Go
to the Resource Sheet, double click a resource, pull the cost tab and you
will see Cost Rate Tables that can be assigned to each resource. You can
then assign the desired table to all the tasks in the project by inserting
the Cost Rate Table column in the Task Usage view (or Resource Usage View).
Of course, you can establish different baselines for each of the cost rate
tables as well.

Will any of that help?
--
If this post was helpful, please consider rating it.

Jim

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

 

[JPD]

Project2007 does pretty good with Pivot Tables and time phased data. You can
download a free trial.

As for differing rates, consider use the cost rate tables in MS Project. Go
to the Resource Sheet, double click a resource, pull the cost tab and you
will see Cost Rate Tables that can be assigned to each resource. You can
then assign the desired table to all the tasks in the project by inserting
the Cost Rate Table column in the Task Usage view (or Resource Usage View).
Of course, you can establish different baselines for each of the cost rate
tables as well.

Will any of that help?
--
If this post was helpful, please consider rating it.

Jim

Visithttp://project.mvps.org/for FAQs and more information
about Microsoft Project

The cost rate table in Project will not help me. That is an issue
with our financial systems calculating actual cost.

 

[JPD]

The cost rate table in Project will not help me. That is an issue
with our financial systems calculating actual cost.

I tried downloading MS Project 2007 demo and it downloads a .img file
that I can not open / execute ....

anyone else have input on my original question / problem ???

 

[JulieS]

I tried downloading MS Project 2007 demo and it downloads a .img file
that I can not open / execute ....

anyone else have input on my original question / problem ???

Hello,

For help on the *img file see the following link which contains
instructions on using *img files.
http://msdn2.microsoft.com/en-us/express/aa718401.aspx

See the section under "manually installing an Express Edition on a local
machine."

To the question of getting custom information from Project to either
Excel or a db program: Assuming you are using a release prior to
Project 2007, you may save data from MS Project as an Access database.
You can then get to the raw data through the tables. If you are
interested in pursuing that option, find and read fully the information
on working with the database structure in project which is in a file
named PJDB.HTM (project 2003 version) or prjdb.htm (previous versions).
You could also export the data through VBA to Excel. If you are
interested in pursuing that route, I suggest purchasing Rod Gill's book
on Project VBA. He has several chapters on working with timephased data
as well as working with the project database through VBA. You can find
information on Rod's book at:
http://www.projectvbabook.com/

I hope this helps.

Julie

 

[John Garay - DecisionEdge]

Hi J,

You mentioned being interested in Off the Shelf systems.

My company has a reporting tool called DecisionEdge Suite for Microsoft
Office Project. It comes with lots of pictures and reports. In context of
your question, what is key is that it can export any data in a chart or
report to an Excel file. In other words, if you have the fields you need in
a chart (time-phased data, including the custom fields), you could simply
click a few buttons to get the data into Excel. Charts and reports also
have filters, which means you can filter the data, and then export to Excel,
so you can focus just one a portion of your data. (You're not alone in
wanting to export data, including spread data, to Excel - we've created some
custom reports for just this purpose)

You can download a 15-day evaluation at http://www.decisionedge.com
In regards to your custom needs, just send a note to me at jgaray at
decisionedge.com and we can see about creating a custom chart or report for
your specific needs (so you can plug it into the main software and get
exactly what you need).

Hope to hear from you soon.

Regards,
John Garay
DecisionEdge, Inc.