none
Timesheet Tracking RRS feed

  • Question

  • Hi,

    I want to track the resources who are not filling Timesheet (Planned VS Actual hours).

    I have created a SQL query but couldn't get tasks where NO Actual hours reported. The task appears when Actual hours entered in Timesheet (SQL query is added below)

    Or any report that can help in this regards.

    SELECT
    	b.[ResourceName]
    	,d.[ProjectName]
    	,b.[TaskName]
    	,a.[PlannedWork]
    	,a.[ActualWorkBillable]
    	,b.[PeriodStartDate]
    	,b.[PeriodEndDate]
    	,c.[Resource Departments]
    	,c.[Team Name]
    FROM
    	[ProjectWebApp].[dbo].[MSP_TimesheetActual_OlapView] a
    	,[ProjectWebApp].[dbo].[MSP_TimesheetLine_UserView] b
    	,[ProjectWebApp].[dbo].[MSP_EpmResource_UserView] c
    	,[ProjectWebApp].[dbo].[MSP_EpmProject_UserView] d
    WHERE
    	a.[TimesheetLineUID] = b.[TimesheetLineUID]
    	and b.[ResourceUID] = c.[ResourceUID]
    	and b.[ProjectUID] = d.[ProjectUID]
    

    Thanks,


    Noman Sohail


    • Edited by NomanSohail Tuesday, March 21, 2017 1:06 PM Update
    Tuesday, March 21, 2017 9:44 AM

Answers

  • Hi,

    sorry, my mistake. Does this work for you?

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, ABD.AssignmentWork AS PlannedWork
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    INNER JOIN dbo.MSP_TimesheetLine_OlapView AS TSO 
    	ON TSL.TimesheetLineUID = TSO.TimesheetLineUID 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    	ON TSO.AssignmentUID = A.AssignmentUID 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    FULL OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON	ABD.TimeByDay = TSA.TimeByDay
    		AND 
    		TSL.TimesheetLineUID = TSA.TimesheetLineUID
    WHERE	( 
    			(ISNULL(ABD.AssignmentWork, 0) <> 0)
    			 OR
                (ISNULL(TSA.ActualWorkBillable, 0) <> 0)
    		)
    		AND
    		NOT TSL.TimesheetLineClass IS NULL
    		AND
    		ABD.TimeByDay BETWEEN TSL.PeriodStartDate AND TSL.PeriodEndDate

    Regards
    Barbara

    • Marked as answer by NomanSohail Wednesday, March 22, 2017 9:34 AM
    Wednesday, March 22, 2017 9:13 AM
    Moderator

All replies

  • Hi Noman,

    the issue in your query: you are not explicitely defining JOIN types, therefore an INNER JOIN ist implicitely used. But there are only recordes in MSP_TimesheetActual_OlapView, if there are Actuals. There is no timesheet table displaying Planned Work By Day if there is no actual work. To do so, you would need to gather Planned Work from AssignmentByDay table.

    The following query is just a modification of your query, showing PlannedWork from TimsheetLines for period:

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.PlannedWork
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, TSL.PlannedWork
    
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    LEFT OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON TSL.TimesheetLineUID = TSA.TimesheetLineUID

    The next one returns also PlannedWork from assignments.
    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, ABD.AssignmentWork AS PlannedWork
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    INNER JOIN dbo.MSP_TimesheetLine_OlapView AS TSO 
    	ON TSL.TimesheetLineUID = TSO.TimesheetLineUID 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    	ON TSO.AssignmentUID = A.AssignmentUID 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    FULL OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON	ABD.TimeByDay = TSA.TimeByDay
    		AND 
    		TSL.TimesheetLineUID = TSA.TimesheetLineUID
    WHERE	( 
    			(ISNULL(ABD.AssignmentWork, 0) <> 0)
    			 OR
                (ISNULL(TSA.ActualWorkBillable, 0) <> 0)
    		)
    		AND
    		NOT TSL.TimesheetLineClass IS NULL

    Does this give a starting point?
    Regards
    Barbara

    Tuesday, March 21, 2017 3:33 PM
    Moderator
  • Thanks Barbara,

    Your query (copied below) worked but I face a little challenge, that shows duplication of records.

    Example:

    The new task (8 hours) created for previous week and it still appears in current week's timesheet because of the task is not completed. In Timesheet view 8 hours "Planned" shown in previous week and NO "Planned" hours in current week.

    But this "query results" 8 hours Planned in previous week and 8 hours Planned in current week.

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, ABD.AssignmentWork AS PlannedWork
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    INNER JOIN dbo.MSP_TimesheetLine_OlapView AS TSO 
    	ON TSL.TimesheetLineUID = TSO.TimesheetLineUID 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    	ON TSO.AssignmentUID = A.AssignmentUID 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    FULL OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON	ABD.TimeByDay = TSA.TimeByDay
    		AND 
    		TSL.TimesheetLineUID = TSA.TimesheetLineUID
    WHERE	( 
    			(ISNULL(ABD.AssignmentWork, 0) <> 0)
    			 OR
                (ISNULL(TSA.ActualWorkBillable, 0) <> 0)
    		)
    		AND
    		NOT TSL.TimesheetLineClass IS NULL


    Noman Sohail


    • Edited by NomanSohail Wednesday, March 22, 2017 7:01 AM Update
    Wednesday, March 22, 2017 6:49 AM
  • Hi,

    sorry, my mistake. Does this work for you?

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, ABD.AssignmentWork AS PlannedWork
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    INNER JOIN dbo.MSP_TimesheetLine_OlapView AS TSO 
    	ON TSL.TimesheetLineUID = TSO.TimesheetLineUID 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    	ON TSO.AssignmentUID = A.AssignmentUID 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    FULL OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON	ABD.TimeByDay = TSA.TimeByDay
    		AND 
    		TSL.TimesheetLineUID = TSA.TimesheetLineUID
    WHERE	( 
    			(ISNULL(ABD.AssignmentWork, 0) <> 0)
    			 OR
                (ISNULL(TSA.ActualWorkBillable, 0) <> 0)
    		)
    		AND
    		NOT TSL.TimesheetLineClass IS NULL
    		AND
    		ABD.TimeByDay BETWEEN TSL.PeriodStartDate AND TSL.PeriodEndDate

    Regards
    Barbara

    • Marked as answer by NomanSohail Wednesday, March 22, 2017 9:34 AM
    Wednesday, March 22, 2017 9:13 AM
    Moderator
  • Hi,

    just an idea: perhaps you should include the day:

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, ABD.AssignmentWork AS PlannedWork
    			, ISNULL(TSA.TimeByDay,ABD.TimeByDay) AS [Date]
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    INNER JOIN dbo.MSP_TimesheetLine_OlapView AS TSO 
    	ON TSL.TimesheetLineUID = TSO.TimesheetLineUID 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    	ON TSO.AssignmentUID = A.AssignmentUID 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    FULL OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON	ABD.TimeByDay = TSA.TimeByDay
    		AND 
    		TSL.TimesheetLineUID = TSA.TimesheetLineUID
    WHERE	( 
    			(ISNULL(ABD.AssignmentWork, 0) <> 0)
    			 OR
                (ISNULL(TSA.ActualWorkBillable, 0) <> 0)
    		)
    		AND
    		NOT TSL.TimesheetLineClass IS NULL
    		AND
    		ABD.TimeByDay BETWEEN TSL.PeriodStartDate AND TSL.PeriodEndDate

    Regards
    Barbara

    Wednesday, March 22, 2017 9:17 AM
    Moderator
  • Great!

    Thanks Barbara it worked.

    But the query is taking too much time to load data in Excel Report (RUNNING BACKGROUND QUERY), I have made PivotTable to present on that data. Any clue for quickly or lesser load?

    Thanks,


    Noman Sohail


    • Edited by NomanSohail Wednesday, March 22, 2017 9:34 AM Reference Update
    Wednesday, March 22, 2017 9:32 AM
  • Hi,

    you could shorten it by just looking at some periods.

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSA.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    			, ABD.AssignmentWork AS PlannedWork
    			, ISNULL(TSA.TimeByDay,ABD.TimeByDay) AS [Date]
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_TimesheetPeriod_OlapView AS Period
    	ON Period.PeriodUID = TSL.PeriodUID
    	-- only last two weeks, adjust as required
    	AND Period.EndDate >= DATEADD(ww,-2,GETDATE())
    	-- only next week, adjust as required
    	AND Period.StartDate <= DATEADD(ww,1,GETDATE())
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    INNER JOIN dbo.MSP_TimesheetLine_OlapView AS TSO 
    	ON TSL.TimesheetLineUID = TSO.TimesheetLineUID 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    	ON TSO.AssignmentUID = A.AssignmentUID 
    INNER JOIN dbo.MSP_EpmAssignmentByDay_UserView AS ABD 
    	ON A.AssignmentUID = ABD.AssignmentUID 
    	AND ABD.TimeByDay >= Period.StartDate
    	AND ABD.TimeByDay <= Period.EndDate
    FULL OUTER JOIN dbo.MSP_TimesheetActual_OlapView AS TSA 
    	ON	ABD.TimeByDay = TSA.TimeByDay
    		AND 
    		TSL.TimesheetLineUID = TSA.TimesheetLineUID
    WHERE	( 
    			(ISNULL(ABD.AssignmentWork, 0) <> 0)
    			 OR
                (ISNULL(TSA.ActualWorkBillable, 0) <> 0)
    		)
    		AND
    		NOT TSL.TimesheetLineClass IS NULL
    		AND
    		ABD.TimeByDay BETWEEN TSL.PeriodStartDate AND TSL.PeriodEndDate
    
    

    Regards
    Barbara

    Wednesday, March 22, 2017 9:55 AM
    Moderator
  • I see a gap again:

    A new task is added on the last day of previous week of 8 hours. Entered 4 hours actual on the same day and remaining 4 shifted automatically on the first day of current week. But the query does not results that remaining 4 hours in current period.

    Need any further change?

    ======

    I have already given Date range in WHERE Clause, see my whole query below: (it brings current & previous month data only)

    But it still take too much time to lead, even in SQL Server Management Studio too.

    SELECT
    	R.[ResourceName]
    	,P.[ProjectName]
    	,TSL.[TaskName]
    	,ABD.[AssignmentWork] AS PlannedWork
    	,TSA.[ActualWorkBillable]
    	,TSA.[TimesheetLineUID]
    	,TSL.[PeriodStartDate]
    	,TSL.[PeriodEndDate]
    	,R.[Resource Departments]
    	,R.[Team Name]
    	,P.[Project Status]
    	,P.[Revenue Category]
    FROM
    	[ProjectWebApp].[dbo].[MSP_EpmResource_UserView] AS R 
    	INNER JOIN [ProjectWebApp].[dbo].[MSP_TimesheetLine_UserView] AS TSL
    	ON R.[ResourceUID] = TSL.[ResourceUID]
    	INNER JOIN [ProjectWebApp].[dbo].[MSP_EpmProject_UserView] AS P
    	ON TSL.[ProjectUID] = P.[ProjectUID]
    	INNER JOIN [ProjectWebApp].[dbo].[MSP_TimesheetLine_OlapView] AS TSO
    	ON TSL.[TimesheetLineUID] = TSO.[TimesheetLineUID]
    	INNER JOIN [ProjectWebApp].[dbo].[MSP_EpmAssignment_UserView] AS A
    	ON TSO.[AssignmentUID] = A.[AssignmentUID]
    	INNER JOIN [ProjectWebApp].[dbo].[MSP_EpmAssignmentByDay_UserView] AS ABD
    	ON A.[AssignmentUID] = ABD.[AssignmentUID]
    	FULL OUTER JOIN [ProjectWebApp].[dbo].[MSP_TimesheetActual_OlapView] AS TSA
    	ON ABD.[TimeByDay] = TSA.[TimeByDay]
    		AND 
    		TSL.[TimesheetLineUID] = TSA.[TimesheetLineUID]
    WHERE
    	(
    		(ISNULL(ABD.[AssignmentWork], 0) <> 0)
    		OR
    		(ISNULL(TSA.[ActualWorkBillable], 0) <> 0)
    	)
    	AND NOT TSL.[TimesheetLineClass] IS NULL
    	AND ABD.[TimeByDay] BETWEEN TSL.[PeriodStartDate] AND TSL.[PeriodEndDate]
    	and R.[ResourceName] NOT IN ('Generic Local Resource')
    	and R.[Resource Departments] IS NOT NULL
    	and R.[ResourceIsActive] = 1
    	and TSL.[PeriodEndDate] >= DATEADD(MONTH, -1, GETDATE()) AND TSL.[PeriodEndDate] < DATEADD(MONTH, 0, GETDATE())

    Regards,


    Noman Sohail



    • Edited by NomanSohail Wednesday, March 22, 2017 10:24 AM Another issue found
    Wednesday, March 22, 2017 10:13 AM
  • Hi,

    with your WHERE clause, you are only filtering timesheets, but not AssignmentByDay view. With my last reply, I am using less data frrom this view. If this is still too much data, you need to use my first suggestion in my first reply, not using AssignmentByDay at all, but rely on Planned work from TimesheetLine on PeriodLevel.

    Sorry
    Barbara

    Wednesday, March 22, 2017 10:25 AM
    Moderator
  • I have used both of your suggestions:

    1. Used "AssignmentByDay", it looks a bit quicker but still it does not show current week 4 hours which was derived from previous week.
    2. Used Planned work from TimesheetLine, it does not show Planned hours until enter Actual hours.

    Also can you please suggest & respond on my another thread about Timesheet Compliance:

    https://social.technet.microsoft.com/Forums/projectserver/en-US/24d2f171-6e32-4b5f-af55-3a9157ddaa0e/timesheet-compliance?forum=projectserver2010general

    Thanks,


    Noman Sohail

    Wednesday, March 22, 2017 10:50 AM
  • Hi,

    you could just ignore TimeBayDay information and just compare PlannedWork in Timesheets with ActualWorkBillable.

    SELECT        R.ResourceName
    			, P.ProjectName
    			, TSL.TaskName
    			, TSL.PlannedWork
    			, TSL.ActualWorkBillable
    			, TSL.PeriodStartDate
    			, TSL.PeriodEndDate
    			, R.[Resource Departments]
    			, R.[Team Name]
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_TimesheetLine_UserView AS TSL 
    	ON R.ResourceUID = TSL.ResourceUID 
    INNER JOIN dbo.MSP_EpmProject_UserView AS P 
    	ON TSL.ProjectUID = P.ProjectUID 
    WHERE TSL.PlannedWork > TSL.ActualWorkBillable

    Regards
    Barbara

    Wednesday, March 22, 2017 11:59 AM
    Moderator
  • Thanks Barbara,

    You above suggestion will help for costing purpose and does not fulfill my requirement.

    I want to ensure resources to fill Timesheet, that's why I would need period wise Planned VS Actual hours.

    Can you tell me the best practice on Timesheet compliance that if organization wants to know who s filling Timesheet or not. What are your recommendations?

    Or you can response on my another thread:

    https://social.technet.microsoft.com/Forums/projectserver/en-US/24d2f171-6e32-4b5f-af55-3a9157ddaa0e/timesheet-compliance?forum=projectserver2010general#24d2f171-6e32-4b5f-af55-3a9157ddaa0e

    Thanks,


    Noman Sohail


    • Edited by NomanSohail Wednesday, March 22, 2017 12:18 PM Update
    Wednesday, March 22, 2017 12:17 PM
  • Hi,

    for timesheet existence have a look at your other post. If you want periodwise, you will need to use AssignmentTimeByDay - and this IS slow.

    Last suggestion: check if ther is actual work for assignments not starting in future without ActualWork in last and current timesheet.

    SELECT        R.ResourceName
    			, T.TaskName
    			, P.ProjectName
    			, A.AssignmentStartDate
    			, A.AssignmentFinishDate
    			, A.AssignmentRemainingWork
    FROM            dbo.MSP_EpmResource_UserView AS R 
    INNER JOIN dbo.MSP_EpmAssignment_UserView AS A 
    ON R.ResourceUID = A.ResourceUID 
    INNER JOIN dbo.MSP_EpmTask_UserView AS T 
    ON A.TaskUID = T.TaskUID 
    INNER JOIN  dbo.MSP_EpmProject_UserView AS P 
    ON T.ProjectUID = P.ProjectUID
    WHERE   
    		-- should have been started
    		(A.AssignmentStartDate <= DATEADD(ww, - 2, GETDATE())) 
    	AND 
    		-- not yet completed
    		(A.AssignmentRemainingWork > 0) 
    	AND 
    		-- no actual work in previous or actual period
    		(A.AssignmentUID NOT IN
                                 (
    							  SELECT        TLO.AssignmentUID
                                   FROM            dbo.MSP_TimesheetLine_OlapView AS TLO 
    							   INNER JOIN dbo.MSP_TimesheetLine_UserView AS TLU 
    								ON TLO.TimesheetLineUID = TLU.TimesheetLineUID
                                   WHERE        
    										-- previous and actual period
    											(TLU.PeriodStartDate >= DATEADD(ww, - 2, GETDATE())) 
    										AND 
    											(TLU.PeriodEndDate <= DATEADD(ww, 1, GETDATE()))
    										AND 
    										-- without actual work
    											(ISNULL(TLU.ActualWorkBillable, 0) = 0) 
    										
    							 )
    		)

    Regards
    Barbara

    Thursday, March 23, 2017 7:47 AM
    Moderator