locked
Where is the field ResourceTimesheetManagerUID? RRS feed

  • 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

    • Edited by Richard T Wednesday, February 13, 2013 2:15 PM added version info
    Wednesday, February 13, 2013 2:13 PM

Answers

  • 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

    • Proposed as answer by Ali-Al Wednesday, February 13, 2013 9:30 PM
    • Marked as answer by Richard T Thursday, February 14, 2013 7:12 AM
    Wednesday, February 13, 2013 2:54 PM

All replies

  • 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

    • Proposed as answer by Ali-Al Wednesday, February 13, 2013 9:30 PM
    • Marked as answer by Richard T Thursday, February 14, 2013 7:12 AM
    Wednesday, February 13, 2013 2:54 PM
  • Nifty, thanks Paul. Can you turn off the filter in Excel?
    Wednesday, February 13, 2013 7:06 PM
  • 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
    Wednesday, February 13, 2013 9:33 PM
  • 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

    Friday, February 15, 2013 8:43 AM
  • 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

    Friday, February 15, 2013 9:16 AM
  • Thanks Paul, works like a charm!
    Friday, February 15, 2013 10:12 AM