none
Microsoft Project Server 2010 - Need a query/report to show everyone who did not submit timesheet last week RRS feed

  • Question

  • I need to create a MSPS 2010 query/report to send out to my project managers that show them everyone who did not submit their timesheet for the previous week.  I would like to be able to filter it by project/client but would settle for just a list of all unsubmitted timesheets at this point.  Every link I have found point to old reports for MSPS 2007 and the queries do not work because of schema changes between MSPS 2007 and 2010.  Please help!
    Tuesday, February 25, 2014 6:57 PM

Answers

  • Jason,

    I don't actually have much about time reporting on my page (yet), as you've probably noticed by now.

    I can probably point you in the right direction - this query is a slightly abridged (and reformatted) version of one of the sample reports which gets installed with Project Server 2010.  I removed some of the less helpful items (why those queries all have the GUIDS in the SELECT statement I will never know!) and lined everything up nice and pretty.

    One note about this- if a resource has never created a single timesheet (or hasn't created one in the timeframe you are filtering the data on) then their info won't be passed to the timesheet table and they will not appear on the list at all.  Hope this helps, please let me know.

     SELECT                     
    MSP_TimesheetLine_UserView.PeriodName as [PeriodName],               
    MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],               
    MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],        
    MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],               
    MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],      
    MSP_TimesheetLine_UserView.ProjectName as [ProjectName],        
    MSP_TimesheetLine_UserView.TaskName as [TaskName],                             
    MSP_TimesheetLine_UserView.PlannedWork as [Planned Work],               
    MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [BillableActOvertimeWork],                MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable ActOTWork],               
    MSP_TimesheetLine_UserView.ActualWorkBillable + MSP_TimesheetLine_UserView.ActualWorkNonBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [TimesheetLineActWork],    MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],               
    MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],               
    MSP_TimesheetLine_UserView.[RBS] as [RBS],
    MSP_TimesheetLine_UserView.ResourceName       

    FROM               

    MSP_TimesheetLine_UserView


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/




    Wednesday, February 26, 2014 4:54 AM
  • If you first pull from MSP_EpmResource_UserView and LEFT OUTER JOIN the timesheet table, it should pull all resources. Put a WHERE statement for active users (WHERE MSP_EpmResource_UserView.ResourceIsActive =1) and you will get currently active users. If you wanted to ensure they were active during the time period in question, you could probably do a BETWEEN for earliest and latest available dates, but it would only be as accurate as your resource data. I'll see if I have time to test all that tomorrow and post a full query. This makes me realize how much easier my life could be, if only we used time sheets!

    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Thursday, February 27, 2014 7:42 AM
  • Jason,

    Written and tested!  It does work.  Here you go:

    SELECT                     
    MSP_TimesheetLine_UserView.PeriodName as [PeriodName],               
    MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],               
    MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],        
    MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],               
    MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],      
    MSP_TimesheetLine_UserView.ProjectName as [ProjectName],        
    MSP_TimesheetLine_UserView.TaskName as [TaskName],                             
    MSP_TimesheetLine_UserView.PlannedWork as [Planned Work],               
    MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [BillableActOvertimeWork],               
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable ActOTWork],               
    MSP_TimesheetLine_UserView.ActualWorkBillable +
    MSP_TimesheetLine_UserView.ActualWorkNonBillable +
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable +
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [TimesheetLineActWork],   
    MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],               
    MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],               
    MSP_TimesheetLine_UserView.[RBS] as [RBS],
    MSP_EpmResource_UserView.ResourceName       

    FROM      

    MSP_EpmResource_UserView

    LEFT OUTER JOIN        

    MSP_TimesheetLine_UserView ON
    MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserView.ResourceUID


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Monday, March 3, 2014 4:11 PM

All replies

  • Hi Jason,

    Have you configured OLAP cubes?

    The EPM timesheet cube provides you what you need: timesheet status (submitted, approved...) by periods. You'll also find project dimension, resources, actual work...

    Here are a few links about OLAP cubes:

    Hope this helps.


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, February 25, 2014 7:16 PM
    Moderator
  • Thank you Guillaume.  I must admit I have inherited a bit of a mess of a MSPS 2010 system.  These are not configured it looks.  I have NOT read through the links you sent yet, but definitely will.  Is there already a report/alert that can be configured to do this already OOB?

    Tuesday, February 25, 2014 8:32 PM
  • Once OLAP cubes and Excel Services configured, you'll have out of the box Excel templates plugged on the Timesheet cube that will be a good start for you.

    See in this article how to configure reporting:

    http://technet.microsoft.com/en-us/library/ff426869(v=office.14).aspx

    Hope it will correspond to your need. 


    Guillaume Rouyre - MBA, MCP, MCTS

    Tuesday, February 25, 2014 8:41 PM
    Moderator
  • You might want to check out ElliJ's blog, http://projectserverpants.wordpress.com as I know she's done some work in this area.

    Treb Gatte, Project MVP | @tumbleroad | http://AboutMSProject.com

    Tuesday, February 25, 2014 11:42 PM
    Moderator
  • Jason,

    I don't actually have much about time reporting on my page (yet), as you've probably noticed by now.

    I can probably point you in the right direction - this query is a slightly abridged (and reformatted) version of one of the sample reports which gets installed with Project Server 2010.  I removed some of the less helpful items (why those queries all have the GUIDS in the SELECT statement I will never know!) and lined everything up nice and pretty.

    One note about this- if a resource has never created a single timesheet (or hasn't created one in the timeframe you are filtering the data on) then their info won't be passed to the timesheet table and they will not appear on the list at all.  Hope this helps, please let me know.

     SELECT                     
    MSP_TimesheetLine_UserView.PeriodName as [PeriodName],               
    MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],               
    MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],        
    MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],               
    MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],      
    MSP_TimesheetLine_UserView.ProjectName as [ProjectName],        
    MSP_TimesheetLine_UserView.TaskName as [TaskName],                             
    MSP_TimesheetLine_UserView.PlannedWork as [Planned Work],               
    MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [BillableActOvertimeWork],                MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable ActOTWork],               
    MSP_TimesheetLine_UserView.ActualWorkBillable + MSP_TimesheetLine_UserView.ActualWorkNonBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable + MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [TimesheetLineActWork],    MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],               
    MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],               
    MSP_TimesheetLine_UserView.[RBS] as [RBS],
    MSP_TimesheetLine_UserView.ResourceName       

    FROM               

    MSP_TimesheetLine_UserView


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/




    Wednesday, February 26, 2014 4:54 AM
  • Thanks Elli!  Is there any way to also pull in the active resources that have not created a timesheet in the timeframe I am filtering on as well?

    Here is the query I had came up with.  Looks like I was on the right track...  I did two actually.  First one to give me a roll up list of employees with outstanding time sheets for last week and the hours, then the same data with an explosion by employee by project and also pull in their associated time sheet manager:

    --Everyone with an timesheet for last week where status  is not 'Approved' or 'Submitted'
    SELECT DISTINCT
            CONVERT(varchar(10),b.StartDate,1) + ' - ' + CONVERT(varchar(10),b.EndDate,1) as [Timesheet Date]
          , [ResourceName]
          , [TimesheetStatus]     
          , SUM([ActualWorkBillable]) as [Hours Entered]
      FROM 
        [dbo].[MSP_TimesheetLine_UserView] a
      , [dbo].[MSP_TimesheetPeriod] b
      WHERE
          a.PeriodUID = b.PeriodUID 
      and [TimesheetStatus] NOT IN ('Approved', 'Submitted')
      and DATEADD(dd,-7,GETDATE()) BETWEEN b.[StartDate] AND b.[EndDate]
      GROUP BY 
        b.[StartDate]
      , b.[EndDate]
      , [ResourceName]
      , [TimesheetStatus]
    GO

    --Everyone with an timesheet for last week where status  is not 'Approved' or 'Submitted' w/ Project/Customer explosion
    SELECT

     CONVERT(varchar(10),b.StartDate,1) + ' - ' + CONVERT(varchar(10),b.EndDate,1) as [Timesheet Date]
    , r.ResourceName AS [Resource Name]
    , [TimesheetStatus]     
    , [ProjectName]     
            , ([ActualWorkBillable]) as [Hours Entered]
    , isnull(rm.ResourceName,'<No Timesheet ManagerInformation Available>') AS [Timesheet Manager]
    FROM
          dbo.MSP_EpmResource_UserView r 
     LEFT OUTER JOIN
          dbo.MSP_EpmResource_UserView as rm 
       ON r.ResourceTimesheetManagerUID = rm.ResourceUID
    ,[MSP_TimesheetLine_UserView] as a
    ,[MSP_TimesheetPeriod] as b
    WHERE
          a.PeriodUID = b.PeriodUID 
      AND a.ResourceUID = r.ResourceUID
      AND  [TimesheetStatus] NOT IN ('Approved', 'Submitted')
      AND  DATEADD(dd,-7,GETDATE()) BETWEEN b.[StartDate] AND b.[EndDate]
      AND (r.ResourceIsActive = 1)
      AND (r.ResourceType = 2)
      AND (r.ResourceIsGeneric = 0)
    ORDER BY 
       r.ResourceName
     , ProjectName
    GO




    Wednesday, February 26, 2014 5:39 PM
  • If you first pull from MSP_EpmResource_UserView and LEFT OUTER JOIN the timesheet table, it should pull all resources. Put a WHERE statement for active users (WHERE MSP_EpmResource_UserView.ResourceIsActive =1) and you will get currently active users. If you wanted to ensure they were active during the time period in question, you could probably do a BETWEEN for earliest and latest available dates, but it would only be as accurate as your resource data. I'll see if I have time to test all that tomorrow and post a full query. This makes me realize how much easier my life could be, if only we used time sheets!

    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Thursday, February 27, 2014 7:42 AM
  • Thanks again Elli.  Let me know if you write that query or if you post it up on your blog!
    Monday, March 3, 2014 12:47 AM
  • Jason,

    Written and tested!  It does work.  Here you go:

    SELECT                     
    MSP_TimesheetLine_UserView.PeriodName as [PeriodName],               
    MSP_TimesheetLine_UserView.PeriodStatus as [Period Status],               
    MSP_TimesheetLine_UserView.TimesheetName as [TimesheetName],        
    MSP_TimesheetLine_UserView.TimesheetStatus as [Timesheet Status],               
    MSP_TimesheetLine_UserView.TimesheetLineClass as [Timesheet Line Class],      
    MSP_TimesheetLine_UserView.ProjectName as [ProjectName],        
    MSP_TimesheetLine_UserView.TaskName as [TaskName],                             
    MSP_TimesheetLine_UserView.PlannedWork as [Planned Work],               
    MSP_TimesheetLine_UserView.ActualWorkBillable as [Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualWorkNonBillable as [Non Billable Actual Work],               
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable as [BillableActOvertimeWork],               
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [Non Billable ActOTWork],               
    MSP_TimesheetLine_UserView.ActualWorkBillable +
    MSP_TimesheetLine_UserView.ActualWorkNonBillable +
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable +
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable as [TimesheetLineActWork],   
    MSP_TimesheetLine_UserView.PeriodStartDate as [Period Start Date],               
    MSP_TimesheetLine_UserView.PeriodEndDate as [Period End Date],               
    MSP_TimesheetLine_UserView.[RBS] as [RBS],
    MSP_EpmResource_UserView.ResourceName       

    FROM      

    MSP_EpmResource_UserView

    LEFT OUTER JOIN        

    MSP_TimesheetLine_UserView ON
    MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserView.ResourceUID


    Elli J Project Solutions Specialist Blog: http://projectserverpants.wordpress.com/

    Monday, March 3, 2014 4:11 PM
  • Many thanks!
    Tuesday, March 4, 2014 4:05 PM