ProjectReporter Overview

ProjectReporter comes in three parts:

  1. A SQL Server or Microsoft Access database. The database holds all data exported from Microsoft Project for reporting purposes and all Risk, Action, Issue, Decision and Change Control data saved from the weekly Excel spreadsheet. ProjectReporter runs well on Microsoft's SQL Server Express (the free version of SQL Server).
  2. A VBA macro or Add-in for Project 2007 or Project 2010. This provides a number of macros: exporting to the database, importing central Resources, inter-project links and more.
  3. An Excel workbook for each project that shows milestones, current tasks, Risks, Actions, Issues, Decisions and Change requests for a project. Al data is saved in the database as well.
  4. An Excel Spreadsheet for the ProjectReporter Admin person to manage resources and their availability and rates.

Microsoft Project Component

A Menu or Ribbon provides access to the following:

  • Publish all data to SQL Server for reporting purposes. NOTE: all project data remainins in its .mpp file. The published data is purely for reporting purposes only.
  • Add ProjectReporter Resources macro that imports a Resource and all rates and calendar data for it.
  • Update Calendars macro to update all calendars in the active project with the latest calendar exceptions stored in SQL Server and enetered via the Admin Excel Workbook.
  • Update Resource Rates macro to update resource rates from SQL Server.
  • Add a Deliverable macro to select a milestone from another project already published to SQL Server and update the selected milestone with its dates. This avoids all risk of file corruption when using links teh normal way using copy and paste-link.
  • Update deliverables macro to refresh milestones in the active project that are setup as deliverables from the Add a Deliverable macro. This means that any date changes published to SQL Server in other projects are refleected via the deliverable in the active project.
  • Create Summary Report macro creates a new project into which there is one new task for each project in SQL Server. This is a great way to review multi-project progress in one report.
  • Who Does What When report macro that exports all Resource data from the active proejct and creates a pivot table report in Excel to show Who Does What When. Low cost, low tech, but high added value!

ProjectReporter Menu - Project 2007 ProjectReporter menu in Project 2007 ProjectReporter Ribbon in Project 2010 ProjectReporter ribbon in Project 2010

SQL Server Component

Installation is simply a matter of running a .SQL script and filling in the blanks for a ProjectReporter.ini file to be saved in a folder on your server, wherever all project managers have read access to. The .ini file holds the connection string for the ProjectReporter database. Once users have browed to this shared location once, it is used from then on to get the latest connection data. An change to the Server or database name or other security information and all your administrator has to do is update the ProjectReporter.ini file and all users will use the new database information from then on.

The Tables (Project, Tasks, Resources, Timephased etc.) have fields that exactly match the names of columns in Project. The code reads the names of each field to determine which columns should be read for each Project, Task etc. In fact if want another field (or custom field) added to the ProjectReporter database, such as Task Text2, then add an extra field to the Task Table and Text2 will now be automatically populated once the Project is next published.

If you want your reports to hold different names, then create a View in SQL Server that uses field aliases to rename the fields. The Excel Templates have examples of doing this and the ProjectReporter database comes pre-populated with some Views for the Excel templates.

Excel Templates Component

There are two Excel Templates:

  1. ProjectReporter Admin - Enables Administrators (who should be the only people with access to this template) to add Calendar exceptions for any Base or Resource Calendar. Most common use for this is for Annual Leave. Also enables Administrators to enter latest Resource rates and effective dates.
    All information is stored in the ProjectReporter database ready for project managers to update their projects.
  2. ProjectReporter Weekly Report - Enables Project Managers to create a weekly report using data published in the ProjectReporter database.
    This template also has worksheets for Risks, Actions, Issues, Decisions and Change Control requests. Data entered in these worksheets is copied to the ProjectReporter database at the click of a button.
    Any project in the ProjectReporter database can be selected and reported on.

With a goal to deliver a low cost, low tech and high added value solution, there is no web front end. If you do want a web front end for your project reporting and updates, then Rod Gill recommends Project Server 2010. Call me for advice on implementing Project Server and automating your project reporting.

 


Scheduling Made Easy - an effective new course from Rod Gill