Where is the field ResourceTimesheetManagerUID?

Question

I have created a report in Excel using a SQL-connection to MSP_EpmResource_UserView. According to the SDK-documentation, the field ResourceTimesheetManagerUID should be found there. However, it doesn't show up in the list of fields. Am I doing something wrong?

My purpose is to create a report that tracks how far along actuals are in the approval process. I can retrieve data on timesheet status and task status but I would like to add the status and timesheet managers' names so I know who to chase.

Can this be done? I cannot create or run SQL-queries, just the out of the box frontend stuff for dummies...

Oops, forgot: we use EPM 2010 and Office 2010 and I am referencing the reporting database

Answer 1

Hi Richard,

This field does exist - Excel will filter out the unique identifier columns. Use the following SQL query in Excel and you will see the GUIDs:

select		ResourceName
		,	Cast(ResourceUID AS Nvarchar (255)) as ResourceUID
		,	Cast(ResourceTimesheetManagerUID  AS Nvarchar (255)) as ResourceTimesheetManagerUID
from		dbo.MSP_EpmResource_UserView

Thanks

Paul

---

Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

Answer 2

Nifty, thanks Paul. Can you turn off the filter in Excel?

Answer 3

If you want the actual name of the Timesheet Manager, you can add this subquery:

select * 
		, (select ResourceName from MSP_EpmResource 
where ResourceUID = MSP_EpmResource_UserView.ResourceTimesheetManagerUID) as TimesheetManager
from	MSP_EpmResource_UserView 

Treb Gatte | Twitter | http://AboutMSProject.com

Answer 4

Hi Treb,

I'm not very good at writing SQL queries. I barely managed to build the query below with MS Query. I use a vertical lookup to add the TimesheetManager and TaskStatusManager's names.

How do I add your subquery to mine? Or am I now pushing it and should I take a SQL-course first...?

SELECT MSP_TimesheetLine_UserViewCF.ResourceName, MSP_EpmResource_UserView.ResourceTimesheetManagerUID, MSP_EpmTask_UserView.TaskStatusManagerUID, (...)

FROM ProjectServer_Reporting.dbo.MSP_EpmProject_UserView MSP_EpmProject_UserView, ProjectServer_Reporting.dbo.MSP_EpmResource_UserView MSP_EpmResource_UserView, ProjectServer_Reporting.dbo.MSP_EpmTask_UserView MSP_EpmTask_UserView, ProjectServer_Reporting.dbo.MSP_TimesheetLine_UserViewCF MSP_TimesheetLine_UserViewCF

WHERE MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserViewCF.ResourceUID

AND MSP_EpmTask_UserView.TaskUID = MSP_TimesheetLine_UserViewCF.TaskUID

AND MSP_EpmProject_UserView.ProjectUID = MSP_TimesheetLine_UserViewCF.ProjectUID

Answer 5

Hi there,

Based on the query you have so far see below:

SELECT		TL.ResourceName
		,	R.ResourceTimesheetManagerUID
		,   (select ResourceName from MSP_EpmResource where ResourceUID = R.ResourceTimesheetManagerUID) AS [Timesheet Manager]
		,	T.TaskStatusManagerUID
		,	(select ResourceName from MSP_EpmResource where ResourceUID = T.TaskStatusManagerUID) AS [Task Status Manager]
FROM		ProjectServer_Reporting.dbo.[MSP_EpmProject_UserView] AS P
		,	ProjectServer_Reporting.dbo.[MSP_EpmResource_UserView] AS R
		,	ProjectServer_Reporting.dbo.[MSP_EpmTask_UserView] AS T
		,	ProjectServer_Reporting.dbo.[MSP_TimesheetLine_UserViewCF] AS TL

WHERE		R.ResourceUID = TL.ResourceUID
 
AND			T.TaskUID = TL.TaskUID 

AND			P.ProjectUID = TL.ProjectUID

I have aliased the table names.

Thanks

Paul

---

Paul Mather | Twitter | http://pwmather.wordpress.com | CPS

Answer 6

Thanks Paul, works like a charm!