Trick Your Reports to Show Certain Summary Tasks as Projects

Around here, we sometimes end up with projects/efforts that are really too small to each have their own project schedule – it would be silly to have 10 lines for a single effort in its own schedule, even if you were using master projects. Because of this, I’ve come up with a method for creating and reporting on what we call “Effort Schedules” in such a way that they can show up on a report alongside the projects which have their own schedules. I find it incredibly useful for everything from tracking projects and efforts together to tracking deployments for projects with single and multiple drops in a way that makes it simple for the PMs to update and yet easy for reporting at a portfolio level.

My goodness, I’m being serious today. I think my test Project Server 2013 environment ate my sense of humor. More on that another day.

So, the basic setup requires the following:

• A project level field to indicate whether the schedule is a collection of efforts or a single project
• A simple case statement in your SQL query

We also have a second project level field for “multiple drops” but once I explain the application you’ll see what I’m getting at.

First define your project field – in our case it’s “Schedule Type” and the options include “Project” and “Effort” etc.

Once your PMs have set that up correctly, you can tell your query to include the Summary Task names by joining the MSP_EpmTask_UserView table in the FROM portion of your query by adding the following:

INNER JOIN
MSP_EpmTask_UserView AS SummaryTask ON
MSP_EpmTask_UserView.TaskParentUID = SummaryTask.TaskUID

Now you can put a case statement into your query which will tell it to pull either the project name or the summary task name, depending on the criteria you set for your custom field:

CASE WHEN MSP_EpmProject_UserView.”Schedule Type” like ‘%Effort%’ THEN SummaryTask.TaskName ELSE MSP_EpmProject_UserView.ProjectName END as [Project]

You can incorporate that into all sorts of status and allocation reports. You can also grab other task level items, like the start/finish dates to slap in there with the project information if you want.

Of course, you can also create a task level field that PMs can set for items they want to show up as “Project” line items in reports and use a similar case statement, but I find that they are generally resistant to setting oodles of task level fields and flags for reporting purposes, so I try to simplify it for them. Another item you could add which might clean up the results a bit is to only pick up summary tasks with a certain outline level by adding this to the WHERE statement:

SummaryTask.TaskOutlineLevel = 1

So yeah, there’s that trick. Now I’m back to beating on my 2013 Test server to see if I can’t get my sense of humor back.

Oh, and full query:

SELECT
MSP_EpmProject_UserView.ProjectOwnerName,
CASE WHEN MSP_EpmProject_UserView.”Schedule Type” like ‘%Effort%’ THEN SummaryTask.TaskName ELSE MSP_EpmProject_UserView.ProjectName END as [Project],
CASE WHEN MSP_EpmProject_UserView.”Schedule Type” like ‘%Effort%’ THEN SummaryTask.TaskFinishDate ELSE MSP_EpmProject_UserView.ProjectFinishDate END as Finish

FROM
MSP_EpmProject_UserView INNER JOIN

MSP_EpmTask_UserView ON
MSP_EpmTask_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID

INNER JOIN
MSP_EpmTask_UserView AS SummaryTask ON
MSP_EpmTask_UserView.TaskParentUID = SummaryTask.TaskUID

WHERE
SummaryTask.TaskOutlineLevel = 1