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
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.
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
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
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
ProjectReporter ribbon in Project 2010
ProjectReporter menu in Project 2007
There are three Excel Templates:
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.
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
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.