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:
- Using External Content Types and SharePoint lists.
- Using VBA to store data in spare baseline fields.
- 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
)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.
'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
'Open the connection and assign the data to the table.
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 & ")")
MsgBox "Project snapshot has been saved.", vbOKOnly, "Confirmation"
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.