none
Reporting Project 2007 from Excel 2003 RRS feed

  • Question

  • Hi All,

    previously when i did a lot of reporting on MS Project, i saved the plan as an access database and queried the database from excel. This allowed easy updating of the excel reports, all one had to do was click refresh on the relevant queries.

    In project 2007 i notice that te option to save as database has been removed. The only way i can see to report on project is to open each plan, and save as in excel, preferably with a mapping correctly set up.

    Is there anyway other way to report on MS Project quickly?

    Friday, May 18, 2012 11:27 AM

Answers

  • I think one of the Visual Reports will do much the same.  Can't remember which one off hand though.

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Monday, May 21, 2012 12:21 PM
    Moderator
  • rapscalli,

    As Andrew mentioned you can probably get what you need using Project/Reports/Visual Reports and selecting the Resource Work Summary report. However, there have been a few issues with the Visual Reports feature in Project 2010, mostly a hang up when building the OLAP cube. You also have to understand how to manipulate the Pivot Table once the data is in Excel.

    An option is to use VBA. With it you can create and format any report you need.

    John

    Monday, May 21, 2012 3:12 PM
  • Agree with John - whose response jogged my memory a bit.  Not sure Visual Reports will work into Excel 2003.  I seem to recall 2007 as being the first version to support OLAP cubes within Excel itself.

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Monday, May 21, 2012 3:20 PM
    Moderator

All replies

  • Pls check the help article
    Why can't I save my project to a database server?
    http://office.microsoft.com/en-us/project-help/why-can-t-i-save-my-project-to-a-database-server-HA010169107.aspx

    And another thread on the same subject:

    http://social.technet.microsoft.com/Forums/en-US/projectprofessional2010general/thread/17c076d4-0d4b-4bb3-bd34-29a09e85bb4e


    Friday, May 18, 2012 11:38 AM
  • You can still export to Access in 2007 (and 2010)....but it's a bit different now.

    Go to Visual Reports....Save Data....Save Database.

    Not sure if the schema is exactly the same, but it looks pretty close to me.


    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Friday, May 18, 2012 3:38 PM
    Moderator
  • Hi Andrew, thanks for the pointer. The previous version of project that i used allowed for the project plan to be saved as an mpd, so all i had to do to get up to date data was for the PM to clicksave and me to click refresh on the queries i set up. It appears to me that the method you mention is more of an export. But beggars cant be choosers!

    The data i'm trying to report on is mostly contained in the "resource usage" view. Currently i am copy / pasting the resource info on the left, and the table on the right into excel.

    For many reasons this is not a good way of doing things - is there a more efficient way of getting the "resource usage" table into excel then copy pasting the 2 areas from project into excel?

    Monday, May 21, 2012 10:37 AM
  • I think one of the Visual Reports will do much the same.  Can't remember which one off hand though.

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Monday, May 21, 2012 12:21 PM
    Moderator
  • Hi Aall,

    here's a screenshot of the data I'd like to get into excel. I know i can copy and paste but this isn't desirable. Is there any more automated way of creating this in excel? The source data is a resource pool project plan. 


    • Edited by rapscalli Monday, May 21, 2012 1:51 PM
    Monday, May 21, 2012 1:48 PM
  • rapscalli,

    As Andrew mentioned you can probably get what you need using Project/Reports/Visual Reports and selecting the Resource Work Summary report. However, there have been a few issues with the Visual Reports feature in Project 2010, mostly a hang up when building the OLAP cube. You also have to understand how to manipulate the Pivot Table once the data is in Excel.

    An option is to use VBA. With it you can create and format any report you need.

    John

    Monday, May 21, 2012 3:12 PM
  • Hi John, i've had a look at that report, i cannot see where it brings through task / project information by resource by date. Here's the pivot table fields available:-

    Is there a step i missed?

    Monday, May 21, 2012 3:19 PM
  • Agree with John - whose response jogged my memory a bit.  Not sure Visual Reports will work into Excel 2003.  I seem to recall 2007 as being the first version to support OLAP cubes within Excel itself.

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky

    Monday, May 21, 2012 3:20 PM
    Moderator