Project Server saves certain data in a “time phased” fashion, which is a pretty neat trick. In fact, it can be kind of addicting. Everything should be time phased! But, sadly, everything is NOT time phased. Assignment Work and Actual Work are, as are Baseline work and Capacity. But, sadly, task work is not. I’m not saying you can make task work look time phased – I might be clever, but my Sonic Screwdriver is sadly just a prop. But you can make tasks seem time phased – enough to make a gantt chart looking thing in an Excel pivot table, anyway.
Why would you want to do this?
Well, in our particular case, we have to track certain tasks across all the projects (we have about 30 active projects at any given time) because of a vital system constraint that I can’t really explain in this post. Just believe me that it’s important to know when certain things are happening in all projects, and that time frame really isn’t work being “done”, so there is no assignment.
We thought about assigning generic resources to those tasks, but I came up with a better plan, one that wouldn’t require us to retro-actively change a whole lot of existing projects, and wouldn’t require our PMs to learn yet another new ‘workaround’ for Project Server.
I managed to trick SQL into giving values to a stretch of time based on the task start and end dates, and then used conditional formatting to make those numbers into a bar.
This is the stuff that dreams are made of! …right? Or maybe I’m just a huge nerd. I’m going with huge nerd. Here’s the trick, use it as you will:
CASE WHEN MSP_EpmTaskByDay_UserView.TimeByDay BETWEEN MSP_EpmTask_UserView.TaskStartDate AND MSP_EpmTask_UserView.TaskFinishDate then 1 else null end as [TaskDuration]
That little puppy will populate a number which will fall into the correct time by day column when you stick it in the values area. It will line up with the task names and then you can make a pretty little fake gantt chart across all your projects. Just use conditional formatting for “all cells showing count of “TaskDuration”=> format only cells that contain=> no blanks, then chose the same color for the text and the background.
So admit it – CASE is your friend, and so am I.