Regular readers of this blog may recall a series of posts from January where I discussed how to use VBA to store timescaled BCWP values in the Baseline10Cost field. At the time, I’d mentioned that the same technique could be used to store other custom timescaled data, for instance, taking a snapshot of milestone variance at each status date.
As I had to develop this solution for my presentation with Mike McLean at the recent Project Conference, I figured I’d go ahead and share the solution.
So the challenge I set myself was to figure out a way to manually trigger the storage of key timescaled data. In this example, I wish to store the variance for each of the project milestones as of the status date. The way I envision this would be that the project manager would update the schedule each week, then trigger the macro that takes a snapshot of the data.
The VBA actually proved to be quite simple. While there may be a performance hit, as I am only copying data into a single timescaled cell, it didn’t seem noticeable.
Sub MSVariance() 'This macro will copy timescaled variance data into the Baseline10Cost field. Dim TSVBaselineCost As TimeScaleValue 'Capture the dataset for the Baseline10Cost Dim TSVSBaselineCost As TimeScaleValues Dim T As Task ActiveProject.StatusDate = InputBox("Enter the Status Date.", "Status Date", ActiveProject.StatusDate) Application.OpenUndoTransaction "UpdateVariance" 'Create an undo group to remove the changes For Each T In ActiveProject.Tasks If T.Milestone = True Then If T.PercentComplete < 100 Then Set TSVSBaselineCost = T.TimeScaleData((ActiveProject.StatusDate), ActiveProject.StatusDate, pjTaskTimescaledBaseline10Cost, pjTimescaleDays, 1) For Each TSVBaselineCost In TSVSBaselineCost TSVBaselineCost = T.FinishVariance / 480 Next TSVBaselineCost T.Baseline10Cost = T.Baseline10Cost + 1 End If End If Next T Application.CloseUndoTransaction 'Close the undo group for the changes. MsgBox "Milestone variance has been stored in the Baseline10Cost field.", vbOKOnly, "Confirmation" End Sub
What this does is copy the variance into the Baseline10Cost field. Now that’s a bit of a hack, as we’re copying a number field into a cost field. As there are no timescaled number fields, I figured it would still work – and it does.
To use, simply set the status date, then trigger the macro. Navigate to the Task Usage view and add the Baseline10Cost field to confirm.
Once the project has been published, create an Excel report from an ODC configured with the following SQL query:
SELECT TOP (100) PERCENT dbo.MSP_EpmProject_UserView.ProjectName, dbo.MSP_EpmTask_UserView.TaskName, dbo.MSP_EpmTaskByDay_UserView.TimeByDay, dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTaskByDay_UserView ON dbo.MSP_EpmProject_UserView.ProjectUID = dbo.MSP_EpmTaskByDay_UserView.ProjectUID INNER JOIN dbo.MSP_EpmTask_UserView ON dbo.MSP_EpmTaskByDay_UserView.TaskUID = dbo.MSP_EpmTask_UserView.TaskUID WHERE (dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost IS NOT NULL) AND (dbo.MSP_EpmTaskByDay_UserView.TaskBaseline10Cost <> 0) ORDER BY dbo.MSP_EpmTaskByDay_UserView.TimeByDay
Once the data’s in Excel, it’s a pretty easy report to generate.
Configure the Pivot Table so it appears as follows:
Add a line chart, and you now have a custom timescaled data report.