“Ninja-ing” Predecessor and Successor Info into the Reporting Database

I don’t know about the rest of you, but I’ve had a nightmare of a time getting our PMs to quit it with the whole linking stuff to Summary Tasks thing.  I can name about 5 different reasons that it’s a bad idea, and it’s on our compliance checklist regarding Project Schedules, but those tricky little predecessors just don’t show up in the reporting database, so the only way to see them is to open project schedules…right?

I have outdone myself this time (or was that last time?) and found a way around this which makes a tidy little report that can go with your other NO SOUP FOR YOU data.

The first thing you have to do is create two custom enterprise fields.  I know, they will show up in projects and make the list of insert-able columns that much longer, but this is so handy!  Just bear with me.  I called mine TaskPred and TaskSucc (tasks suck?) and set them up like so…

pred

NOTE: Make sure that the roll up for summary tasks is set to “formula” instead of “none”.

Only the succ one calls for the field [Successors], obviously.

The only trick to this is that the PMs have to publish their schedules after you make the change, so it might take a while for the data to start showing up.

It’s really quite simple after that, you just pull the data that you want (I would totally add more information to this query, like the project status so I can filter out closed projects, etc) and there you have it.  I can see some other handy applications for this data, and for the concept in general.  You’ll find the query below.  I hope this helps someone, but it certainly has made my life easier!

EDIT – You can also do this with the task notes column, as well as many others!

SELECT
MSP_EpmProject_UserView.ProjectOwnerName,
MSP_EpmProject_UserView.ProjectName,
MSP_EpmTask_UserView.TaskName,
MSP_EpmTask_UserView.TaskIsActive,
MSP_EpmTask_UserView.TaskIsSummary,
MSP_EpmTask_UserView.taskpred,
MSP_EpmTask_UserView.tasksucc,
MSP_EpmTask_UserView.TaskIndex

FROM
MSP_EpmProject_UserView
INNER JOIN
MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

WHERE
MSP_EpmTask_UserView.TaskIsActive =1 AND
MSP_EpmTask_UserView.TaskIsSummary =1 AND
(MSP_EpmTask_UserView.taskpred IS NOT NULL OR
MSP_EpmTask_UserView.Tasksucc IS NOT NULL)

Advertisements

2 thoughts on ““Ninja-ing” Predecessor and Successor Info into the Reporting Database

  1. why not just tell people not to dio it, and have some sort of QC checklist which gets applied before the plan gets published?

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