ProjectReporter - Multi-project reporting tool

Do you have multiple Microsoft Project .mpp files? Do you need a low cost, low tech, high added value project reporting tool? Then ProjectReporter will help you. ProjectReporter is made up of three components:

  1. In Project Professional or Standard 2003 or later, a VBA macro that exports the data for the active project to SQL Server, purely for reporting purposes. You continue to use your usual .mpp file.
  2. A database in SQL Server to hold your project data. You can either use SQL Express or any existing SQL Server instance you already have.
  3. Excel templates for weekly reports and administration tools to help manage calendars and Resource rates etc.

Microsoft Project Component

The Microsoft Project Component comes either as a protected VBA Macro or as a Microsoft Project add-in. A ProjectReporter menu provides access to the following commands:

  • Publish Project Copies project data to SQL Server for reporting purposes.
  • Add Deliverable Displays a form showing all milestones in all projects listed by project and sorted by Task ID. Provides inter-project links with no risk of file corruption (Copy Paste-link uses old DDE technology that is prone to causing file corruptions. Uses Text20 to save the Project and Task details of the original milestone, Any Deadline from the original project is also copied.
  • Update Deliverables Uses the data stored in Text20 by the Add Deliverable command to update the date of the Deliverable Milestone. By refreshing the date from the ProjectReporter database, there is no DDE link to the original project and so no risk of file corruption.
  • Project Summary Displays a form showing project titles and status for you to update.
  • Import Resources Displays a form showing all Unique Resources in the ProjectReporter Enterprise Resources Table. Selecting resources then clicking import not only adds the Resources as a new Resource in your project but updates the new Resource with the latest calendar exceptions and Resource rate data from the ProjectReporter database.
    A separate button updates all your Resources with the latest Calendar and Rate information. Using this option it's easy to keep all projects current with latest availability levels and Annual leave etc.

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 low cost, high tech and high added value, 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 (due for release in mid 2010). Call me for advice on implementing Project Server and automating your project reporting.

ProjectReporter Pricing

The introductory cost for ProjectReporter is $1,500 plus $150 per project manager connecting to ProjectReporter. Costs exclude GST and are in NZ$.

Multi-Project Solutions

Click here for a multi-project solution overview.

Scheduling Made Easy - an effective new course from Rod Gill