Create a Calendar Pivot Table of Love Straight from the PS Database!

Once again, my success is built on the backs of others.

Namely, this ingenuitive gentleman over here:

http://chandoo.org/wp/2012/09/12/interactive-pivot-calendar/

And even he was inspired by someone else’s work.  So, it goes down the line, right?

I was tasked to put a “calendar view” of a project where certain resources are completely tied up for whole weeks at a time.  We wanted to have better visibility to their availability, so “embed a calendar somewhere” was the criteria for this.

Oh, how I didn’t want to have to have some manual calendar out there which had to be updated regularly…  So between the post above and the magic of sql and excel, I was able to put something together which looks like this:

calendar
And this puppy doesn’t even have an extra table hiding somewhere in Excel or anything!

Let’s start with the basics.

You have to pull all the TimeByDay measures out of the database.  That’s not so bad:

DATENAME (Week, MSP_TimeByDay.TimeByDay) as [Week],
DATENAME (Month, MSP_TimeByDay.TimeByDay) as [Month],
DATENAME (Year, MSP_TimeByDay.TimeByDay) as [Year],
DATENAME (WeekDay, MSP_TimeByDay.TimeByDay) as [weekday],
CONVERT (NUMERIC, DATENAME (day, MSP_TimeByDay.TimeByDay)) as day

(note that if you don’t convert that last one, day will not add up right as a values field)

The problem here is, of course, that you now have to join the timebyday table to the assignmentbyday table, and that starts to look pretty messy when you pull it into the calendar layout, because if no one happens to be assigned to a task on a given day, the date will be blank.  Of course, if there were no splits (lack of work between the start and end date of an assignment) for any tasks in your project, you could use the trick from my post https://projectserverpants.wordpress.com/2012/09/07/fake-time-phase-task-data-for-reals/ and pull only days between starts and ends).

Failing that (this project fails that) you have to somehow jimmy the thing into putting some value in for each weekday, and then hide it.

My solution was to stick this beauty in the select statement:

CASE WHEN MSP_EpmResource_UserView.ResourceName like ‘%unassigned%’ then 1 else MSP_EpmAssignmentByDay_UserView.AssignmentWork end as AssignmentWork

This will only work if there is unassigned work on the project.  You could also use someone who you weren’t trying to track, or a generic resource, etc.

So that’s the first part of the first part – jimmying it into putting a value in for each weekday.  Here’s the second part of the first part:

That first portion goes into a nested select statement, and the outer statement will have this baby:

CASE WHEN assignmentwork >0 then day else NULL End as [Day]

As always, I’ll hit you with the whole query at the end so you won’t have to stand around blinking and wondering what this crazy lady is doing.

Ok, so now for the layout of the pivot table – it’s just like the one that the post I referenced put together:

set

There’s one thing we have to do differently – the SUM for project is going to be set to “average” instead of sum. 

But, now I’ve got this ugly idiotic unassigned resource showing up every stupid time!  Can I just filter him out?

No.  Not unless you want your calendar dates to look like this:

ugly 

See how we’re missing days there?  Ugly.

Ok, so let’s use some conditional formatting on this sucker (that’s the hiding part) and we’ll be golden.

Here’s the basic concept – for the pivot table (you have to expand the whole pivot table when setting the condition or it will go away when you slice it) you want to set all blanks to be the same font color as the fill color.  (borders should be the same, that makes it look nicer)  (Make sure you set this one on the pivot table using “All cells showing “Sum of Day” values for “week and “weekday”)

 Then for the “unassigned” resource, you’ll want to use a formula like this: 

=$B6=”Unassigned Resource”

(Make sure you set this one on the pivot table as “All Cells Showing “Sum of Day” values)

Almost there.  Stay on target!

For column B, conditional formatize (that’s a word) it for specific text “Unassigned Resource” and now you just have one blank line in your table. (drag it to the top so that it doesn’t show up in random spots, or better yet you can use a custom sort list)

Don’t forget a slicer- if this thingy shows more than one month at a time it hurts a little bit.

Is it worth it?

Well I invented this and wowed my boss and wrote this post all in a day, so it is for me!

I’m sure there are tons of applications for this – let me know if you come up with any!   You could certainly just get the data to pull and do one of those delicious interactive calendar things like I liked to at the beginning – but sad panda is sad because macros and SharePoint are not friends, so none for me…

Anyway, here’s the query.  I wouldn’t leave you hanging, don’t worry!
SELECT

CASE WHEN assignmentwork >0 then day else NULL End as [Day],
Month,
Week,
Weekday,
Year,
ResourceName

FROM

(SELECT
MSP_TimeByDay.TimeByday,
MSP_EpmProject_UserView.ProjectName,
Case when MSP_EpmResource_UserView.ResourceName like ‘%unassigned%’ then 1 else MSP_EpmAssignmentByDay_UserView.AssignmentWork end as assignmentwork,
MSP_EpmResource_UserView.ResourceName,
DATENAME (Week, MSP_TimeByDay.TimeByDay) as [Week],
DATENAME (Month, MSP_TimeByDay.TimeByDay) as [Month],
DATENAME (Year, MSP_TimeByDay.TimeByDay) as [Year],
DATENAME (WeekDay, MSP_TimeByDay.TimeByDay) as [weekday],
CONVERT (NUMERIC, DATENAME (day, MSP_TimeByDay.TimeByDay)) as day

FROM
MSP_EpmProject_UserView LEFT OUTER JOIN
MSP_EpmTask_UserView ON
MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID

LEFT OUTER JOIN
MSP_EpmAssignment_UserView ON
MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID
LEFT OUTER JOIN
MSP_EpmResource_UserView ON
MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID

LEFT OUTER JOIN
MSP_EpmAssignmentByDay_UserView ON
MSP_EpmAssignment_UserView.AssignmentUID=MSP_EpmAssignmentByDay_UserView.AssignmentUID

LEFT OUTER JOIN
MSP_TimeByDay ON
MSP_TimeByDay.TimeByDay = MSP_EpmAssignmentByDay_UserView.TimeByDay

WHERE
MSP_EpmProject_UserView.ProjectName like ‘%yourprojectname%’)as x

Advertisements

2 thoughts on “Create a Calendar Pivot Table of Love Straight from the PS Database!

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