Easy-Peasy Cross-Project Links Report

Ok, dear readers, it really has been a LONG time. I have a huge queue of posts to write but I got behind and, well, there you have it. But I’m back now! I’m going to start with this easy little gem – a query to show what projects are using other project tasks as predecessors.

This came about for us because we’re trading schedules with a group who is using Project Server 2007, and therefore can’t use deliverables. It took me a surprisingly long time to figure out the trick to this sucker, but it turned out to be deceptively easy.

All you have to do is link the task names of the linked tasks to external task names in the dependent project schedules. Basically, you build a nice table pulling the data you want from the linked *to* project, such as the project name, task name, and other task details, and then link that table to a subset of tables, joining them on the task name where the task is external.

Seriously, that’s all there is to it. I provide the query for you now, and we’re done!

SELECT
linktoproj.ProjectName,
linktoproj.ProjectOwnerName,
linktoproj.TaskName,
linktoproj.TaskStartDate,
linktoproj.TaskFinishDate,
linktoproj.TaskPercentCompleted,
tproj.ProjectName as LinkedProj,
linktoproj.TaskPercentCompleted

FROM

(SELECT
MSP_EpmProject_UserView.ProjectName,
MSP_EpmProject_UserView.ProjectOwnerName,
MSP_EpmTask_UserView.TaskName,
MSP_EpmResource_UserView.ResourceName,
MSP_EpmTask_UserView.TaskUID,
MSP_EpmTask_UserView.ProjectUID,
MSP_EpmTask_UserView.TaskStartDate,
MSP_EpmTask_UserView.TaskFinishDate,
MSP_EpmTask_UserView.TaskPercentCompleted

FROM
MSP_EpmProject_UserView LEFT OUTER JOIN

MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

LEFT OUTER JOIN
MSP_EpmAssignment ON
MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment.TaskUID

LEFT OUTER JOIN
MSP_EpmResource_UserView ON
MSP_EpmAssignment.ResourceUID = MSP_EpmResource_UserView.ResourceUID

WHERE
(MSP_EpmTask_UserView.TaskFinishDate >current_timestamp-30 OR
MSP_EpmTask_UserView.TaskPercentCompleted <100)
) as linktoproj

LEFT OUTER JOIN
MSP_EpmTask_UserView as projtask ON
linktoproj.TaskName = projtask.TaskName AND linktoproj.ProjectUID projtask.ProjectUID

LEFT OUTER JOIN
MSP_EpmProject_UserView as tproj ON
projtask.ProjectUID = tproj.ProjectUID

WHERE
projtask.TaskIsExternal=1

Advertisements

4 thoughts on “Easy-Peasy Cross-Project Links Report

  1. Or you use the (extremely undocumented functionality) of Dependencies and Deliverables in Project Pro 2013 and PWA. There is a Deliverables report that comes with it … if you can find it. We will be moving away from linking soon due to volume of projects and permissions. In 2013 you cannot insert a ‘ghost task’ into a project plan that is not your or if you are not on the project. Dependencies/Deliverables solves the issue … the trick is understanding the interplay between the Deliverables sharepoint list and Project Pro .. so you can do things the hard way or the easy way and get the same results. Meet with Microsoft just a month ago and railed on the MS Project Program Manager for the lack of documentation … said they would be working on it and they don’t document every feature in their applications…. link here explains what it is and why it is underutilized… mainly because it is not documented http://psbehindthescene.blogspot.com/2015/07/why-deliverabledependency-feature-is.html

    • In this case, the vendor with whom we are sharing schedules does not use project sites, which would make it impossible for them to use Deliverables, even if it were well documented. Additionally, since we are only receiving schedules from them and not sending them back, there would be no cycle to tie their deliverables to our sites and then keep them in place on their end.

      I’m aware this isn’t an elegant solution, but it’s the only way which works in these circumstances. I’m working on a better write-up of a less crude dependency report which uses the unique ID of the successor to identify the link, and between these reports and our mechanism, this is working for now. Thanks!

    • Also, in Project 2013 you can insert a “ghost task” without opening the project. It’s only in 2007 that there is a limitation on that.

      Thanks for reading and commenting, I appreciate the feedback!

  2. Or … use the (extremely undocumented) Dependencies and Deliverables in Project Pro 2013 and PWA. We will be moving away from linking soon due to volume of projects and permissions. In 2013 you cannot insert a ‘ghost task’ into a project plan that is not your or if you are not on the project. Dependencies/Deliverables solves the issue … the trick is understanding the interplay between the Deliverables sharepoint list and Project Pro .. so you can do things the hard way or the easy way and get the same results. Met with Microsoft just a month ago and railed on the MS Project Program Manager for the lack of documentation … said they would be working on it and they don’t document every feature in their applications…. link here explains what it is and why it is underutilized… mainly because it is not documented or understood.

    http://psbehindthescene.blogspot.com/2015/07/why-deliverabledependency-feature-is.html

    The link below takes you to the old MS ‘Gives and Gets’ report. We tweaked (recreated our own) to make a Deliverables report. Showing producers and consumers – synonymous with pred/succ) … we only use it for milestones… so that we can see when a project (consumer) MTP date is in jeopardy based on the MTP dates of the dependent projects (producers) …https://msdn.microsoft.com/en-us/library/bb428839(v=office.12).aspx

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