Capturing Custom Timescaled Data in Project Server (Part II)

First off, let me preface this post with the comment that all customizations should be tested – especially a solution which edits timescaled data.  Solutions such as these are often prone to memory leaks, and as such, it behooves you to follow Lenin’s (paraphrased) advice: “Test, test, and, again, test.”  In fact, my DEV environment started exhibiting all sorts of memory related issues as I worked through these posts.

From a performance perspective, when testing the solution below, I found that it seemed to add almost 10-20 seconds to the publish time in my DEV environment.  That’s hardly a scientific test, but it’s definitely the sort of performance hit that would cause you to take pause if you thought about, perhaps, running this automatically as part of the publish routine.  Personally, I could see a macro that would be triggered manually at each status reporting interval, which would capture all sorts of timescaled reporting data: BCWP over time, snapshots of major milestone variance, etc.

So anyway, here’s the macro.  The usual hat tip goes to Rod Gill for his excellent book on VBA for Microsoft Project.

Sub BCWP_SummaryTaskTransfer()

    'This macro will copy timescaled EVMS data into the Baseline10Cost field.  When used in an enterprise setting, users may report
    'on EVMS metrics as follows: PV = BaselineCost, AC = ActualCost, EV = Baseline10Cost.
    Dim TSV_BCWP As TimeScaleValue 'Capture the dataset for the EV
    Dim TSVBaselineCost As TimeScaleValue 'Capture the dataset for the Baseline10Cost
    Dim TSVS_BCWP As TimeScaleValues
    Dim TSVSBaselineCost As TimeScaleValues
    Dim LastBCWP As String
    Application.OpenUndoTransaction "UpdateBCWP" 'Create an undo group to remove the changes.
    BaselineClear All:=True, From:=20 'Clear the previous baseline
    ActiveProject.ProjectSummaryTask.Baseline10Cost = ActiveProject.ProjectSummaryTask.BCWP 'Populate the scalar data
    'Define the two data sets: BCWP and Baseline10 Cost
    Set TSVSBaselineCost = ActiveProject.ProjectSummaryTask.TimeScaleData(ActiveProject.ProjectStart, ActiveProject.StatusDate, pjTaskTimescaledBaseline10Cost, pjTimescaleWeeks, 1) 'Set the data set for the Baseline10Cost
    Set TSVS_BCWP = ActiveProject.ProjectSummaryTask.TimeScaleData(ActiveProject.ProjectStart, ActiveProject.StatusDate, pjTaskTimescaledBCWP, pjTimescaleWeeks, 1) 'Set the data set for the Cumulative Percent Complete

    'Transfer data from BCWP to Baseline10 Cost

        TSVSBaselineCost(TSV_BCWP.Index).Value = (Val(TSV_BCWP.Value) - Val(LastBCWP))
        LastBCWP = Val(TSV_BCWP.Value) 'Capture the last period's value to calculate the delta
    Next TSV_BCWP
    LastBCWP = 0 'Remove any value lingering in the BCWP field
    Application.CloseUndoTransaction  'Close the undo group for the changes.
End Sub

Walking through the macro, it performs the following steps:

  1. Deletes the previously saved Baseline10 values.
  2. Copies the BCWP values at the Project Summary task level into Baseline10 Cost (at a granularity of 1 week – as opposed to daily, to avoid potential performance issues).
  3. Drops the Project BCWP into the scalar Baseline10 Cost field to trigger the baseline save event.  See the last post for more information on that.

Note that instead of simply copying the BCWP value to the Baseline10 Cost line, I am actually subtracting the last period’s BCWP from the current period to generate the delta from one period to another.  That makes the reporting a bit easier, as it matches how AC and BCWS are both stored within the reporting database.

For what it’s worth, Rev1 of this macro operated at the assignment level.  On a fair sized project, it took almost a minute or so to run.  Hence, I decided to keep it at the Project Summary task level.  It makes reporting a bit of a hassle as you’ll see in the next post, but it’s still doable.  On the other hand, throwing the data into the assignment level will make it available in OLAP cubes – which is also pretty slick – but maybe not worth the performance hit, depending on how forgiving your end users are.

Another hat tip to Ukrainian MVP, Vadim Gerya, for graciously sharing his solution on how to convert scalar cost values to timescaled data by allocating it across a defined time period…  The list of beers and/or vodka shots I owe to folks at the Project Conference gets longer by the day.

Next up….the query…

Capturing Custom Timescaled Data in Project Server (Part II)

4 thoughts on “Capturing Custom Timescaled Data in Project Server (Part II)

  1. Jon says:

    Andrew, great stuff. I was wondering if I could ask you some questions. I tried putting this macro in a project file (Project Pro 2010) and set it on the Calculate and then Open event. It didn’t transfer the data to Baseline 10. Is there something I am missing?

    1. Hmmm….I can’t remember if I have that set to use the status date or the current date. 1) Is it possible you don’t have a status date set? 2) Are you sure it’s running when you open the project?

  2. Jon says:

    Hi Andrew – I am positive it is running, as I put a “watch” event in there to pause it initially to see if it would run on event. I even ran the macro manually to be sure. I set the status date, but also tested this with ActiveProject.CurrentDate. It runs, doesn’t error out, but the timephases Baseline10Cost field does not get the BCWP data. The timephased data does not copy and the scalar data, in the Gantt view does not update as well. I’m hoping I can figure this out with your assistance, as I am managing an EPM implementation and am very disappointed with the lack of timescaled reporting you get out of the box. I appreciate any assistance or information you can provide.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s