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 For Each TSV_BCWP In TSVS_BCWP 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:
- Deletes the previously saved Baseline10 values.
- 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).
- 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…