Timesheet Compliance RRS feed

  • Question

  • Hi,

    How can I ensure Timesheet complaince in PS 2013?

    What are the best practices to track how many resource are filling Timesheet or not.


    Noman Sohail

    Wednesday, March 22, 2017 10:49 AM

All replies

  • Hi,

    you could check for timesheet existance with following approach:

    SELECT        TRO.ResourceName
    			, TPO.StartDate
    			, TPO.EndDate
    			, 'TimesheetExistsWithoutActualWork' AS Status
    FROM            dbo.MSP_Timesheet_OlapView AS TSO 
    INNER JOIN dbo.MSP_TimesheetResource_OlapView AS TRO 
    	ON TSO.OwnerResourceNameUID = TRO.ResourceNameUID 
    INNER JOIN  dbo.MSP_TimesheetLine_UserView AS TLU 
    	ON TSO.TimesheetUID = TLU.TimesheetUID 
    INNER JOIN dbo.MSP_TimesheetPeriod_OlapView AS TPO 
    	ON TSO.PeriodUID = TPO.PeriodUID
    GROUP BY TPO.StartDate
    	   , TPO.EndDate
    	   , TRO.ResourceName
    HAVING		(TPO.StartDate >= DATEADD(ww, - 2, GETDATE())) 
    			(TPO.EndDate <= DATEADD(ww, 1, GETDATE())) 
    			(SUM(ISNULL(TLU.ActualWorkBillable, 0)) = 0)
    SELECT	R.ResourceName
    	  , TPO.StartDate
    	  , TPO.EndDate
    	  , 'NoTimesheet' AS Status
    FROM  dbo.MSP_EpmResource_UserView AS R 
    CROSS JOIN dbo.MSP_TimesheetPeriod_OlapView AS TPO
    WHERE	(R.ResourceIsActive = 1) 
    		(TPO.StartDate >= DATEADD(ww, - 2, GETDATE())) 
    		(TPO.EndDate <= DATEADD(ww, 1, GETDATE()))
    ORDER BY ResourceName
    	   , StartDate


    Thursday, March 23, 2017 7:47 AM
  • Thanks Barbara.

    Can you please tell me how organizations can ensure Timesheet compliance?

    What are the best practices to know Planned VS Actual other than SQL query?


    Noman Sohail

    Monday, March 27, 2017 4:06 AM
  • Hi NomanSohail,

    I would like to suggest two approaches.

    1) You can use SSRS/PowerBI report to list all the users who are defaulters.

    2) An automated approach to send the notification to users who are not submitting the Timesheets.

    Monday, March 27, 2017 5:52 AM
  • Thanks Murali,

    I have used SQL queries to identify Planned VS Actual Timephased/period wise reported by resources but couldn't get the correct result.

    In your approach 1: I will also need to use any SQ table to get records. Can you specify the table fr where I can get the details. I have used Assignment table where Planned and Actual hours are recorded but the issue I had faced that Planned hours don't show until any Actual hours reported.

    In your approach 2: This will show only who has submitted Timesheet or not to Timesheet Manager, which I have already done it.


    Noman Sohail

    Monday, March 27, 2017 8:02 AM
  • Noman,

    What exactly is the requirement here?

    1) if you are looking to have a count of how many users filled timesheet or not, then the queries below should give you the answer

    2) If you want to track Planned vs Actual value, you will have to use the hours logged in the timesheet AND the "planned" hours for the same timesheet Table. This is because if you try to compare with the "Assignment" tables, then you are looking at the "task Update" hours, which are a result of timesheet hours (assuming you are in Single Entry Mode). You are also assuming that the Project managers have accepted the hours and incorpiorated them into the project schedule.

    The best practice process is as follows:

    1) Set your Timesheets in Single Entry Mode

    2) train your team members to submit timesheets every week.

    3) Train your Project Managers to accept the resulting task updates and update/publish the project plan. At the very least, they should set up ayutomatic rules to take care of this.

    4) Now you can run a planned vs Actual report from the Project Schedule and see the discrepancy.

    5) You should also run a Timesheet vs Task Discrepancy Report and see the difference in hours.


    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Monday, March 27, 2017 9:23 PM
  • Thanks Prasanna for detailed response.

    Yes we use SEM. Resources submit Timesheet regularly. PMs have set auto approval and publish.

    Your 2nd point is my requirement. Let me develop a case:

    Resource A is assigned a task of duration 2 days (8 hours each)
    Resource B is assigned a task of duration 3 days (8 hours each)
    Resource C is assigned a task of duration 10 days (8 hours each)

    Resource A has not entered efforts (remaining 16 hours of this task)
    Resource B has entered 8 hours in day 1 (remaining 16 hours of this task)
    Resource C has entered 8 hours each in 3 days (remaining 56 hours of this task)

    In report I want period wise (of this week):

    Filter:	Current week	
    Resource Name	Planned	Actual
    Resource A	16	0
    Resource B	24	8
    Resource c	40	24

    Let me know how to get this done through SQL query or any view or anything else?


    Noman Sohail

    • Edited by NomanSohail Tuesday, March 28, 2017 9:28 AM Correction update
    Tuesday, March 28, 2017 5:56 AM
  • The below query will give you 

    Resource ID, Date, Planned, Actual. 

    	select o1.ResourceUID as 'ResourceUID',o1.TimeByDay as 'TimeByDay'
    	,sum(o1.AssignmentWork) as 'WORK', sum(o1.AssignmentActualWork) as 'ActualWork'
    		(select a.AssignmentUID,a.ProjectUID,a.ResourceUID,a.TaskUID,ad.TimeByDay, ad.AssignmentWork as 'AssignmentWork',
    		ad.AssignmentActualWork as 'AssignmentActualWork'
    		from dbo.MSP_EpmAssignment  a
    		inner join MSP_EpmAssignmentByDay ad on ad.AssignmentUID = a.AssignmentUID
    		) as o1
    	group by o1.ResourceUID, o1.TimeByDay
    ) o2 
    where o2.TimeByDay between '2017-02-15 00:00:00.000' and  '2017-02-16 00:00:00.000'

    Thursday, March 30, 2017 7:48 AM
  • Thanks Murali,

    Can you please add/update your query with these details too:

    • [ResourceName]
    • [ProjectName]
    • [TaskName]
    • [PeriodStartDate]
    • [PeriodEndDate]
    • [Resource Departments]
    • [Team Name]
    • [ProjectStartDate]
    • [ProjectFinishDate]


    Noman Sohail

    Thursday, March 30, 2017 9:15 AM
  • Noman,

    When you add Project Name and Task Name. You cannot achieve overall data like you requested previously.

    Filter:	Current week	
    Resource Name	Planned	Actual
    Resource A	16	0
    Resource B	24	8
    Resource c	40	24

    You will something like below


    Project Name Task Name Resource Name Planned Actual
    ABC T1 Resource A 8 0
    ABC T2 Resource B 16 0
    CDE T1 Resource B 16 8
    CDE T2 Resource C 16 8
    DEF T3 Resource C 40 24
    DEF T1 Resource A 20 24

    Are you looking for this kind of result?

    Tuesday, April 4, 2017 4:01 AM