Storing Custom Timescaled Data with VBA

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.

The Challenge

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 Macro

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.

The Query

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, 
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

The Report

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.


Storing Custom Timescaled Data with VBA

One thought on “Storing Custom Timescaled Data with VBA

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s