Well, it’s that time of year again, the down time between the solar and lunar new year festivities when we bid farewell to 2011 and welcome to the year of the water dragon. This year, as I attempt to forestall getting back into “serious” work, I figured that I’d kick off the blog with a return to the basics and a look at reporting.
Seeing as I’ll be presenting on Earned Value at the upcoming Project Conference, this next series of posts combines EVMS and reporting into the same concept. The general gist is how to capture timescaled data within the Project Pro client using a spare baseline cost field. This is a technique that I had heard about from other MVPs, but had never tried myself. When a client request popped up, I figured I’d take some time to see if I could figure out how it works.
So first off the challenge: we’re implementing Earned Value techniques in conjunction with Microsoft Project Server at this client, and I’d gotten to the point of developing a couple reports in Excel Services. When it came time to generate a timescaled EVM curve, I realized that Project Server doesn’t seem to actually maintain timescaled EVM data anywhere. Essentially that means it’s almost impossible to generate the report below from the data stored in the enterprise databases.
Just to throw a quick primer on EVM terminology out there, note that Microsoft Project uses the DOD terminology for EV calculations, hence…
- EV = BCWP
- PV = BCWS
- AC = ACWP
…which I’ll be using for the rest of these blog posts…
From an enterprise data perspective, we can generate a similar graph using BCWS = Baseline0Cost, and ACWP = Actual Cost – both of which are stored in the Project Server databases and readily available for reporting purposes.
The challenge here is to copy the timescaled BCWP data from the BCWP row in Project Pro to the Baseline10Cost row – thereby making it available for enterprise reporting purposes.
The same concept could be applied to any number of other challenges, for instance capturing snapshots of milestone finish variance, or snapshots of EAC calculations on each status date. The possibilities are endless.
Enterprise Baseline Mechanics
So I sat down and wrote a macro to copy BCWP to the Baseline10Cost field. Unexpectedly at that point, I ran into a significant snag….it turns out that I didn’t quite understand how enterprise baseline data is saved. Hence, I had to take a quick detour to figure that out before coming back to the issue at hand.
The key to understanding baselines in Project Server is that baselines are stored in two different data sets. The first data set consists of the scalar data. That is the data on the left of the Task Usage view. (I should point out that I really liked the definition of “scalar” that I found on the Web….”Having value, but no direction.” Definitely sounds like some people I know. )
Editing scalar data is pretty straightforward. Simply update the field and publish the project to Project Server.
Run a query against the reporting database within SQL to display the edit.
Timescaled data is a bit more complicated. That’s stored in any of the tables with the designation “ByDay” – i.e. AssignmentsByDay, TasksByDay, etc. The trick here (and thanks to Brian and Adrian for pointing it out) is that simply editing the timescaled cost values will not work…
When I make the manual edits above, and then publish the project, the SQL query against the TasksByDay table results in the following:
Worked just fine, right? Well, unfortunately, things are not as simple as they appear. It turns out that simply entering timescaled data doesn’t trigger Project Server to capture the data in the enterprise databases. And if Project Server doesn’t capture the data, we can’t generate reports against it.
After doing some testing, it seems that Task and Assignment timescaled data are treated differently.
For Task timescaled data to be saved, at least one of the scalar values must be edited. In the example below, I have edited the Baseline10 Cost field in addition to the two timescaled values.
This triggers Project Server to recognize the timescaled data and to store it on publish.
Assignment timescaled data appears to be a bit more complicated. As far as I can tell, Assignment timescaled data requires a value to be entered in both the scalar and the corresponding Baseline Work field.
In this example, I enter data in Baseline10 Cost, Baseline10 Work, and the scalar Baseline10 Cost field.
Note that any non-null value in the Baseline10 Work field will trigger the save.
So, in short, if you wish to edit Baseline timescaled data, you’ll need to remember the correct triggers to force Project Server to retain the data.
|Data||Baseline Save Triggered By|
|Task||Scalar Data Edit|
|Assignment||Scalar Data Edit &
Timescaled Work Edit
You could apply the same approach to a budget resource assignment, i.e. create a budget resource, assign it to the summary task, and then copy the relevant data into the assignment row – in which case you may not have to mess with the baseline mechanics. I didn’t test this scenario against a custom timescaled budget cost field, but if you’re looking to create more custom timescaled fields, it wouldn’t be hard to just create a budget resource called “BCWP” and then use that to store the data.
Next up…the macro.