ProjectReporter - How it Works

ProjectReporter comes in three parts:

  1. A SQL Server 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 Standard or Professional. This provides a number of macros: exporting to the database, importing central Resources, inter-project links and more.
  3. Excel Workbooks. There is an Excel workbook for each project that shows milestones, current tasks, Risks, Actions, Issues, Decisions and Change requests for a project. All data is saved in the database as well.
    An Excel Spreadsheet for the ProjectReporter Admin person to manage resources and their availability and rates.
    An Excel Spreadsheet for the Who Does What When report.

Click the following headings to read more information on each 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.

A Menu or Ribbon provides access to the following commands:

  • 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 the 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 project and creates a pivot table report in Excel to show Who Does What When. Low cost, low tech, but high added value!

By using SQL Server and VBA macros in Proejct and Excel, ProjectReporter provides a high value, low cost solution that neatly collects easy to deliver business solutions. Call us to find out more.

ProjectReporter ribbon in Project 2010First half of the ProjectReporter ribbon

Second half of the ProjectReporter ribbon

ProjectReporter menu in Project 2007ProjectReporter Menu - Project 2007

There are three 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.
  3. ProjectReporter Who Does What When Report - The Who Does What When report shows the same information as the Resource Usage View in Project, but for all selected projects in the database. The report can also be run from the ProjectReporter ribbon just for the active project.
    All users not familiar with Microsoft Project find this report easy and extremely useful for viewing Resource workloads.

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 we recommend you look at Project Online, Microsoft's hosted Project Server solution. Call me for advice on implementing Project Online and automating your project reporting.