none
Different Timesheet statuses in PWA and Database RRS feed

  • Question

  • Hi,

    Can anyone can explain me, why there is a difference when I am checking statuse of my timesheets from Manage Timesheets PWA page (/PWA/Timesheet/MyTSSummary.aspx) and when I tried to do the same directly from Database.

    Below SQL query I use to generate Timesheet statuses report:

    SELECT 
      T.TimesheetName, 
      T.Comment, 
      TR.ResourceName, 
      TS.Description, 
      TP.PeriodName, 
      TP.StartDate, 
      TP.EndDate 
    FROM MSP_Timesheet_OlapView T
    LEFT JOIN MSP_TimesheetResource TR ON T.OwnerResourceNameUID = TR.ResourceNameUID
    LEFT JOIN MSP_TimesheetStatus TS ON T.TimesheetStatusID = TS.TimesheetStatusID
    LEFT JOIN MSP_TimesheetPeriod TP ON T.PeriodUID = TP.PeriodUID
    WHERE TR.ResourceName LIKE 'My_User_Name'
    ORDER BY TP.StartDate DESC

    For instance, statuses marked as 'Submitted' in PWA has status 'In progress' in DB results.

    My goal is to check who do not submit his timesheet for given period (and by that I mean, who do not turn in final timesheet from PWA). Any Way to do that?

    Wednesday, October 23, 2013 10:54 AM

Answers

  • Hi,

    If someone is having same issues, please find below my findings.

    I still do not know why reporting DB do not store proper Timesheet statuses but I know that Publish DB has it.

    Below sample SQL that will retrieve Timesheet statuses same way they are displayed in PWA:

    SELECT
    	TS.Description AS TimesheetStatus, 
    	TP.StartDate AS PeriodStartDate, 
    	TP.EndDate AS PeriodEndDate, 
    	TP.PeriodName, 
    	TR.ResourceName
    FROM
    	MSP_EpmResourceType INNER JOIN
    	MSP_EpmResource_UserView AS TR ON MSP_EpmResourceType.ResourceType = TR.ResourceType LEFT OUTER JOIN
    	pub.MSP_TIMESHEETS AS T LEFT OUTER JOIN
    	MSP_TimesheetStatus AS TS ON T.TS_STATUS_ENUM = TS.TimesheetStatusID RIGHT OUTER JOIN
    	MSP_TimesheetPeriod AS TP ON T.WPRD_UID = TP.PeriodUID ON TR.ResourceUID = T.RES_UID

    Hope that helps.

    Best regards,

    Mariusz

    Monday, July 28, 2014 2:18 PM