3 Simple Compliance Reports (Part 3)

Finding All the Naughty Little Projects Without Baselines!

Ok, so there’s no little boxy checky thingy that says loud and clear, “This Project has been baselined, yo!”.  This can be a bit of a problem, since it’s important for Projects to get baselined, given Project’s unstoppable desire to eat data.  (Time marches on, Work gets replaced by Actual Work, etc)

In our PMO, we have a Project Phase field, and once you get out of planning, you’d better have a baseline in place or you won’t be able to report against any kind of variance.  So I created this little ditty to try to track which projects had baselines and which didn’t.  I have an added “Where” in my statement that sifts out the early phases, but since it’s a custom field, I’ve removed it from this post.

Ok, so what’s the problem, exactly?  Shouldn’t you just be able to SUM all the lovely baselines (pick a field, I used work, but cost could work too depending on how you have things set up) and find the nulls?  The problem with that is that while SQL understands that 1+1 = 2, it seems to think that 1+1+NULL = NULL.  Don’t despair, we are still smarter than the system.  Here’s my workaround for that:

SUM(
IsNull(ProjectBaseline0work, 0) +
IsNull(ProjectBaseline1work, 0) +
IsNull(ProjectBaseline2work, 0) +
IsNull(ProjectBaseline3work, 0) +
IsNull(ProjectBaseline4work, 0) +
IsNull(ProjectBaseline5work, 0) +
IsNull(ProjectBaseline6work, 0) +
IsNull(ProjectBaseline7work, 0) +
IsNull(ProjectBaseline8work, 0) +
IsNull(ProjectBaseline9work, 0)) as [BaselineTotal]

 
If we stick that into a temporary table, then we can query against it in the WHERE statement for the overall query and find only those Projects that have absolutely no data for Baseline Work.

And there you have it.  See the whole thing below.  Next up, I finally get to my little excel trick so that you can create a score card to show all of these Compliance checks for all PMs and Projects…

SELECT
ProjectOwnerName, 
ProjectName,
ProjectFinishDate, 
BaselineTotal

FROM

(SELECT
ProjectOwnerName, 
ProjectName,
ProjectFinishDate, 
SUM(
IsNull(ProjectBaseline0work, 0) +
IsNull(ProjectBaseline1work, 0) +
IsNull(ProjectBaseline2work, 0) +
IsNull(ProjectBaseline3work, 0) +
IsNull(ProjectBaseline4work, 0) +
IsNull(ProjectBaseline5work, 0) +
IsNull(ProjectBaseline6work, 0) +
IsNull(ProjectBaseline7work, 0) +
IsNull(ProjectBaseline8work, 0) +
IsNull(ProjectBaseline9work, 0)) as [BaselineTotal]

FROM

MSP_EpmProject_UserView

WHERE
ProjectFinishDate >current_timestamp
GROUP BY
ProjectOwnerName, 
ProjectName,
ProjectFinishDate) as x

WHERE
BaselineTotal=0

Advertisements

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