Feel free to pass this one up if I’m telling you things you already know. Or read on because even though I might not know too much, I am hilarious.
I’ve been playing around (nobody should be this “into” their job) with Excel Web Access web parts for use by our PMs (among other things) in order to give them some quick “schedule compliance” views that they can use. Our PMs all have quite a few projects, so it’s nice if they can have a place to get an overview of potential problems without having to open and check each project one at a time.
One thing that I really wanted was for the PMs to not have to click on filters and drop downs and otherwise sort through the rest of the universe’s dirty laundry. The current user filter web part in SharePoint can connected to a web part, yadda yadda, this stuff comes right out of the Big Orange Book of Doom that all Project Server admins keep on their desk. (Just to look cool, right?) But what good is that filter if the pivot table you’re trying to use has the name of the project owner listed, and the SharePoint name probably doesn’t match up?
What matches up is the NT account. That should really match up no matter what. (ok, I’ve seen exceptions to this, but I’m going to assume here that you’re using a nice happy AD sync, ok?)
So all you have to do is manipulate the query so that instead of pulling MSP_EpmProject_UserView.ProjectOnwerName it pulls the MSP_EpmResource_UserView.ResourceNTAccount ….but associates that value with the PM, not the resources assigned to tasks, etc.
Easy? You betcha, but it might not be the most obvious of things when you’re a hack like me. So here’s the trick, which can be used for a myriad of other things – like finding tasks with the wrong status manager after a project changes hands, etc…
All you gotta do is put a special little WHERE statement right into the SELECT part of the query:
(SELECT ResourceNTAccount from MSP_EpmResource_UserView where MSP_EpmResource_UserView.ResourceUID = MSP_EpmProject_UserView.ProjectOwnerResourceUID) as [NTAccount],
Now when you add your slicer and publish your workbook, you can connect your Current User Filter to the NT Account and BAM, the PM will only see their own grave errors, not everyone else’s.
We like to keep our reports simple and clean, with as little room for user error as possible, and I find that this makes it all quite clear. There is probably some technical name for the maneuver, but I learned it all on the job, so I have no clue. Hope it helps!