none
Report on all types of work RRS feed

  • Question


  • I need to build a report that shows all types of work (saved but not submitted, submitted and pending of approval, approved and rejected) by day for each assignment and for a whole month. Users enter their work using Tasks page.

    I have found that in MSP_ASSIGNMENTS_SAVED table there is information of the current status of the assignment and the total work but I haven't been able to find all types of work by day (only approved and published work)...

    Any help will be really appreciated!!


    • Edited by E_FG Wednesday, September 18, 2013 6:03 PM Spelling mistake
    Wednesday, September 18, 2013 6:02 PM

Answers

  • E_FG,

    I went back over that thread and I think I have a query which will be of more use to you (providing you switch to SEM) for time reporting.  It breaks out the timesheet work by 'submitted' and 'unsubmitted'.  There are a few things to keep in mind - one is that sometimes the submitted work will show up as 0 - this means that the resource submitted something besides work (like a date or remaining work change).  Another is that if someone puts their time for different weeks out of order (submits time for last week, then the week before), the status will carry over from the subsequent week and this report might not be acruate.  We minimize this by running the report weekly and making sure that all resources put their time into the system in a timely manner.

    Lastly, if someone hasn't created a timesheet for any of the previous weeks, their name will not show in this report at all.  We use a spreadsheet that compares names against anyone who was assigned work, but frankly it's pretty complicated and I haven't yet found a suitable way to share the methods with the masses.

    I hope this helps!

    SELECT
    CASE WHEN
    Timesheetlinestatus like '%Pending Approval%' OR comment like '%a%' then 'Submitted'
    WHEN comment not like '%a%' then 'Unsubmitted' else TimesheetLineStatus end as Status,
    PlannedWork,
    ResourceName,
    timesheetlinestatus,
    TaskName,
    Work,
    Comment,
    StartDate

    FROM

    (SELECT
    MSP_EpmResource_UserView.ResourceName,
    MSP_TimesheetLine_UserView.ProjectName,
    MSP_TimesheetLine_UserView.PeriodName,
    MSP_TimesheetLine_UserView.TaskName,
    MSP_TimesheetLine_UserView.PlannedWork,
    SUM(MSP_TimesheetLine_UserView.ActualWorkBillable+
    MSP_TimesheetLine_UserView.ActualWorkNonBillable+
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable+
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable) as work,
    MSP_TimesheetLine_UserView.TimesheetLineStatus,
    MSP_TimesheetPeriod.EndDate,
    MSP_TimesheetPeriod.StartDate,
    MSP_TimeSheetLine.Comment

    FROM
    MSP_TimesheetLine_UserView

    LEFT OUTER JOIN

    MSP_TimeSheetLine ON
    MSP_TimeSheetLine.TimesheetlineUID = MSP_TimeSheetLine_UserView.TimesheetlineUID

    LEFT OUTER JOIN

    MSP_TimesheetPeriod ON
    MSP_TimesheetPeriod.PeriodUID = MSP_TimesheetLine_UserView.PeriodUID

    LEFT OUTER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserView.ResourceUID

    WHERE
    (MSP_TimesheetPeriod.StartDate <current_timestamp-7)

    GROUP BY
    MSP_EpmResource_UserView.ResourceName,
    MSP_TimesheetLine_UserView.ProjectName,
    MSP_TimesheetLine_UserView.PeriodName,
    MSP_TimesheetLine_UserView.TaskName,
    MSP_TimesheetLine_UserView.PlannedWork,
    MSP_TimesheetLine_UserView.TimesheetLineStatus,
    MSP_TimesheetPeriod.EndDate,
    MSP_TimesheetPeriod.StartDate,
    MSP_TimeSheetLine.Comment) as x


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

    Wednesday, February 12, 2014 6:15 PM

All replies

  • Unfortunately, if you want to pull data for time reporting in Project Server, updating using the tasks function isn't going to do that for you. If you switch to Single Entry Mode, your team members can enter their time by task through the time sheeet and the data will be pushed to the reporting database. There are a lot of queries and information available out there about time reporting, though you have to use a few tricks to get the individual timesheet line status to report properly, but there are a couple of examples here:

    http://social.technet.microsoft.com/Forums/projectserver/en-US/a94a5764-8ae9-4a9c-b7fe-19b3cdd275af/timesheet-line-status-is-it-all-a-lie?forum=projectserver2010general

    Hope this helps.

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


    • Edited by ElliJ Wednesday, February 12, 2014 6:35 AM
    Wednesday, February 12, 2014 6:32 AM
  • E_FG,

    I went back over that thread and I think I have a query which will be of more use to you (providing you switch to SEM) for time reporting.  It breaks out the timesheet work by 'submitted' and 'unsubmitted'.  There are a few things to keep in mind - one is that sometimes the submitted work will show up as 0 - this means that the resource submitted something besides work (like a date or remaining work change).  Another is that if someone puts their time for different weeks out of order (submits time for last week, then the week before), the status will carry over from the subsequent week and this report might not be acruate.  We minimize this by running the report weekly and making sure that all resources put their time into the system in a timely manner.

    Lastly, if someone hasn't created a timesheet for any of the previous weeks, their name will not show in this report at all.  We use a spreadsheet that compares names against anyone who was assigned work, but frankly it's pretty complicated and I haven't yet found a suitable way to share the methods with the masses.

    I hope this helps!

    SELECT
    CASE WHEN
    Timesheetlinestatus like '%Pending Approval%' OR comment like '%a%' then 'Submitted'
    WHEN comment not like '%a%' then 'Unsubmitted' else TimesheetLineStatus end as Status,
    PlannedWork,
    ResourceName,
    timesheetlinestatus,
    TaskName,
    Work,
    Comment,
    StartDate

    FROM

    (SELECT
    MSP_EpmResource_UserView.ResourceName,
    MSP_TimesheetLine_UserView.ProjectName,
    MSP_TimesheetLine_UserView.PeriodName,
    MSP_TimesheetLine_UserView.TaskName,
    MSP_TimesheetLine_UserView.PlannedWork,
    SUM(MSP_TimesheetLine_UserView.ActualWorkBillable+
    MSP_TimesheetLine_UserView.ActualWorkNonBillable+
    MSP_TimesheetLine_UserView.ActualOvertimeWorkBillable+
    MSP_TimesheetLine_UserView.ActualOvertimeWorkNonBillable) as work,
    MSP_TimesheetLine_UserView.TimesheetLineStatus,
    MSP_TimesheetPeriod.EndDate,
    MSP_TimesheetPeriod.StartDate,
    MSP_TimeSheetLine.Comment

    FROM
    MSP_TimesheetLine_UserView

    LEFT OUTER JOIN

    MSP_TimeSheetLine ON
    MSP_TimeSheetLine.TimesheetlineUID = MSP_TimeSheetLine_UserView.TimesheetlineUID

    LEFT OUTER JOIN

    MSP_TimesheetPeriod ON
    MSP_TimesheetPeriod.PeriodUID = MSP_TimesheetLine_UserView.PeriodUID

    LEFT OUTER JOIN
    MSP_EpmResource_UserView ON
    MSP_EpmResource_UserView.ResourceUID = MSP_TimesheetLine_UserView.ResourceUID

    WHERE
    (MSP_TimesheetPeriod.StartDate <current_timestamp-7)

    GROUP BY
    MSP_EpmResource_UserView.ResourceName,
    MSP_TimesheetLine_UserView.ProjectName,
    MSP_TimesheetLine_UserView.PeriodName,
    MSP_TimesheetLine_UserView.TaskName,
    MSP_TimesheetLine_UserView.PlannedWork,
    MSP_TimesheetLine_UserView.TimesheetLineStatus,
    MSP_TimesheetPeriod.EndDate,
    MSP_TimesheetPeriod.StartDate,
    MSP_TimeSheetLine.Comment) as x


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

    Wednesday, February 12, 2014 6:15 PM