Baseline Variance Reporting Across All Projects

I may not be holding a thermal detonator, but I am fearless and inventive!

I’ve touched on Baselines before, but that was more from a high level reporting “Has a Project Been Baselined or Not?” kind of standpoint.  Today one of our analysts wanted a report that they could use to see variance against baselines at a programmatic level across projects.

This has always been hideously painful to look at because the Baseline work, while present in the reporting database (and even timephased!), the information is scattered across 10 different baseline columns.  I tried a few different solutions to this and eventually came up with something.

The one caveat I will give is that this query will automatically pick the baseline with the highest number of hours.  This might not always be the best baseline, but it is probably the most recent.  You could change MAX to MIN if you wanted to find the oldest one, depending on what you’re looking for.

In any case, I found a great thread here (see the second answer listed) that gave me a nice starting point for  my query and then applied it to Project Server, like so:

(SELECT MAX(v)
FROM (VALUES (MSP_EpmAssignmentByDay_UserView.AssignmentBaseline1Work), (MSP_EpmAssignmentByDay_UserView.AssignmentBaseline2Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline3Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline4Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline5Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline6Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline7Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline8Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline9Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline0Work)) AS Value(v)) as [Max]

So totally handy, right?

See the full query below.  This is absolutely my best all-time find on the whole of today!

SELECT

MSP_EpmAssignmentByDay_UserView.AssignmentActualWork,
MSP_EpmAssignmentByDay_UserView.AssignmentWork,
MSP_EpmTask_UserView.TaskName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmResource_UserView.RBS,
MSP_EpmProject_UserView.ProjectName,
MSP_EpmAssignmentByDay_UserView.TimeByDay,
(SELECT MAX(v)
FROM (VALUES (MSP_EpmAssignmentByDay_UserView.AssignmentBaseline1Work), (MSP_EpmAssignmentByDay_UserView.AssignmentBaseline2Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline3Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline4Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline5Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline6Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline7Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline8Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline9Work),
(MSP_EpmAssignmentByDay_UserView.AssignmentBaseline0Work)) AS Value(v)) as [Baseline]

FROM

MSP_EpmResource_UserView

INNER JOIN

MSP_EpmAssignment_UserView ON
MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

INNER JOIN
MSP_EpmProject_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID

INNER JOIN

MSP_EpmTask_UserView ON
MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID

INNER JOIN

MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignmentByDay_UserView.AssignmentUID = MSP_EpmAssignment_UserView.AssignmentUID

WHERE
(MSP_EpmAssignmentByDay_UserView.TimeByDay <Current_Timestamp) AND
(MSP_EpmResource_UserView.RBS like ‘%staff%’)

Advertisements

2 thoughts on “Baseline Variance Reporting Across All Projects

  1. This seems a little odd to me – I would personally have used the MSP_EpmAssignmentBaseline table in a CTE – that way you could use grouping and window expressions to do this, and order them by any number of different criteria…

    • That would probably work better, but you can’t use a CTE in the sql query in the excel data connection. It might work if you use PowerPivot, but I haven’t tested that. Thanks for the tip, I might just try it out sometime.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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