3 Simple Compliance Reports (Part 1)

If you’re like me, you’re forced to delve into the mundane day-to-day issues of a PMO. No, I actually love this, I’m just trying not to admit it. There are those around here who call me the “time cop” because I’m responsible for running reports that show if people are entering time, etc. It kind of makes me feel powerful, like a wild… uh… I digress.

I have a few little tricks that I’ve come up with, resulting in a couple of neat little packages for compliance reporting that the Project Oversight team can look at whenever they need to. At the end of it all I will include a little Excel trick that allows you to show the totals for all of these checks into one table for easy visibility!

;

First Up:

Who’s Your Status Manager?!

When a project changes hands from one PM to another, all you can really do is change the owner of the project and then close your eyes and hope, right? Wrong! I mean, we usually send an email requesting them to change themselves to the Status Manager, and it has these nice, detailed instructions for how to make sure you expand all the tasks in the project and drag your name down to every cell, but that doesn’t always work well.

So, I present to you a nice little query which will only return tasks that have a different Status Manager than the Project Owner!

The first key here is in the join statement:

MSP_EpmTask_UserView.TaskStatusManagerUID = MSP_EpmResource_UserView.ResourceUID

All you have to do is tell it that the resource you want to know about isn’t the one assigned to the task, but the one who is the status manager. Now you need to tell it to only show you the ones that don’t match by sticking this thingamie in your WHERE statement:
MSP_EpmResource_UserView.ResourceName ; MSP_EpmProject_UserView.ProjectOwnerName

Plink down those two pieces in an otherwise straight forward project/task/resource query and you’re good to go. You can also filter this on active tasks, incomplete tasks, and other criteria, of course. So take it and run with it!

;

SELECT

MSP_EpmProject_UserView.ProjectName,
MSP_EpmProject_UserView.ProjectOwnerName,
MSP_EpmTask_UserView.TaskName,
MSP_EpmResource_UserView.ResourceName

FROM

MSP_EpmProject_Userview

INNER JOIN

MSP_EpmTask_UserView ON

MSP_EpmProject_Userview.ProjectUID = MSP_EpmTask_UserView.ProjectUID

INNER JOIN

MSP_EpmResource_UserView ON

MSP_EpmTask_UserView.TaskStatusManagerUID = MSP_EpmResource_UserView.ResourceUID

WHERE

(MSP_EpmResource_UserView.ResourceName ; MSP_EpmProject_UserView.ProjectOwnerName)

Advertisements

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