The question always arises as to how to best take a snapshot of project data, and use that in timephased reports or change logs.  In fact, by my count, this is the third time I’ve addressed this topic in this blog:

1. Using External Content Types and SharePoint lists.
2. Using VBA to store data in spare baseline fields.
3. Using VBA to store data in a custom SQL table (keep reading).

In this blog post, I’d like to take a different approach than in the past and show how to use VBA to post snapshots of project data to a SQL database.  The value of storing the data in a custom SQL table of course is that the data is available on the enterprise level, subject to enterprise retention policies, and available for use with enterprise reporting solutions.

Now, I could always use automatically generated queries.  In fact, it would be quite simple to set up a timed job that runs within SQL every Friday evening and takes a snapshot of project data.  As I’ve mentioned before, I am not a big fan of such automated queries – generally because I know a lot of project managers who like to catch up on work over the weekend – or who often will submit the numbers only to immediately realize that they forgot to totally update their schedule, make adjustments and then resubmit.  Running a timed snapshot process may not work well for folks like this.

My preference is for the project manager to manually trigger that snapshot in time as part of the routine status reporting cycle.  You could even incorporate some workflow or alerts around the data when it’s submitted as part of the report.

Creating the Table

In this case, I’ll just use some code that Calin, one of our developers created for me to support some EVM reporting.  This query will generate a table within a SQL database.   Generally, you would add this table to a custom database.

CREATE TABLE [dbo].[ProjectStatusSnapshots](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Date] [datetime] NOT NULL,
      [ProjectName] [nvarchar](500) NOT NULL,
      [ProjectUID] [uniqueidentifier] NULL,
      [ProjectStatusDate] [datetime] NULL,
      [ProjectACWP] [money] NULL,
      [ProjectBCWP] [money] NULL,
      [ProjectBCWS] [money] NULL,
      [ProjectEAC] [money] NULL,
      [ProjectCPI] [money] NULL,
      [ProjectSPI] [money] NULL,
      [ProjectTCPI] [money] NULL,
      [ProjectCost] [money] NULL,
    [ProjectActualCost]  [money] NULL,
      [ProjectBaseline0Cost] [money] NULL 
CONSTRAINT [PK_ProjectStatusSnapshots] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Modify the fields to fit your needs.  You might also consider creating two tables, one for project header information, and one for task information.

Confirm that we have a new table in SQL.

Adding the VBA

The VBA then is relatively simple.  We just need to create a bit of code to generate the database connections and then run a SQL update query to insert the right data into the database table.

Note that I am grabbing the ProjectUID as well.  This will facilitate reports that will join the trend analysis data with the default Project Server reporting database.

As I mentioned above, you may want to consider adding code to capture task level metrics for trend analysis.  That would pretty much be the same concept.  Just make sure to grab a unique identifier for the tasks to use in generating reports.  Using that technique and a field to capture the current user, you could effectively implement a change log – especially if you embed the VBA code in the Project OnSave activity.

Sub UpdateSQL()

    'This macro will log specific field data into a customized SQL database.

    'Create connection to SQL table.'

    Dim Conn As New ADODB.Connection

    Dim SQLInstance As String
    Dim SQLDatabase As String

    'Set the connection parameters here

    SQLInstance = "Demo\Demo" 'Enter the name of the SQL instance
    SQLDatabase = "zzz_TrendAnalysis" 'Enter the name of the SQL database

    Conn.ConnectionString = "Provider=sqloledb;" _
    & "Data Source=" & SQLInstance & ";" _
    & "Initial Catalog=" & SQLDatabase & ";" _
    & "Integrated Security=SSPI;"

    If Conn.State = 1 Then 'To avoid errors where the connection was opened and not closed
        Conn.Close
    End If

    'Open the connection and assign the data to the table.

    Conn.Open

    With ActiveProject.ProjectSummaryTask

    Conn.Execute ("INSERT INTO " & SQLDatabase & ".dbo.ProjectStatusSnapshots([date], ProjectUID, ProjectName, " _
    & "ProjectStatusDate, ProjectACWP, ProjectBCWP, ProjectBCWS, ProjectEAC, ProjectCPI, " _
    & "ProjectSPI, ProjectTCPI, ProjectCost, ProjectActualCost, ProjectBaseline0Cost) " _
    & "VALUES(GetDate(), " _
    & Chr(39) & ActiveProject.GetServerProjectGuid & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.Name & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.StatusDate & Chr(39) _
    & Chr(44) & .ACWP _
    & Chr(44) & .BCWP _
    & Chr(44) & .BCWS _
    & Chr(44) & .EAC _
    & Chr(44) & .CPI _
    & Chr(44) & .SPI _
    & Chr(44) & .TCPI _
    & Chr(44) & .Cost _
    & Chr(44) & .ActualCost _
    & Chr(44) & .BaselineCost & ")")

    End With

    Conn.Close

    MsgBox "Project snapshot has been saved.", vbOKOnly, "Confirmation"

End Sub

Run the VBA a couple of times to confirm it’s generating data.

Once you’ve validated the VBA, consider adding it as a button to a customized ribbon toolbar.  This will give your users the ability to trigger the macro directly from the main Microsoft Project interface.

Retrieving the Data

From there, it’s just a simple question of retrieving the data.  As I mentioned above, I fully expect the PM to potentially create multiple snapshots at each status period, I may only want to grab the “final” one that was posted each day.  To do that, we just need to modify a basic SQL query.

Here’s an example:

SELECT dbo.ProjectStatusSnapshots.Id, dbo.ProjectStatusSnapshots.Date, dbo.ProjectStatusSnapshots.ProjectName, dbo.ProjectStatusSnapshots.ProjectUID, dbo.ProjectStatusSnapshots.ProjectStatusDate, dbo.ProjectStatusSnapshots.ProjectACWP, dbo.ProjectStatusSnapshots.ProjectBCWP, dbo.ProjectStatusSnapshots.ProjectBCWS, dbo.ProjectStatusSnapshots.ProjectEAC, dbo.ProjectStatusSnapshots.ProjectCPI, dbo.ProjectStatusSnapshots.ProjectSPI, dbo.ProjectStatusSnapshots.ProjectTCPI, dbo.ProjectStatusSnapshots.ProjectCost, dbo.ProjectStatusSnapshots.ProjectActualCost, dbo.ProjectStatusSnapshots.ProjectBaseline0Cost, DailyRecord.LastRecord FROM dbo.ProjectStatusSnapshots RIGHT OUTER JOIN (SELECT MAX(Id) AS LastRecord FROM dbo.ProjectStatusSnapshots AS ProjectStatusSnapshots_1 GROUP BY ProjectUID, CAST(Date AS Date)) AS DailyRecord ON dbo.ProjectStatusSnapshots.Id = DailyRecord.LastRecord

Run this query to generate a table of the last entry posted per project on any given day.  You could then use the ProjectUID field to join this date to live data in the Project Server reporting database.