none
How to account our staff's actual working hours ,Project 2013 RRS feed

  • Question

  • Hi there,

    I let our staffs record work hours in PWA during project, now I want to account our staff's working hours by month, what shall I do? eg, In May, totally working hours used, Staff A/B/C's actual working hours.

    Can someone help me ?

     


    Jou Chen

    Thursday, June 4, 2015 8:36 AM

Answers

  • Hi Jou Chen,

    You have several ways to achieve this objectives, more or less dynamic, more or less immediate, more or less easy to put in place.

    • From the resources center, select your resources and click on "resource assignments". This is a immediate view which provides you the actual and planned work per resources, projects and per day. You cannot change the timescale but you can switch to the resource availability which provides you a graph with a grid below.

    • From Project Pro, you can have a file with your resources and use the resource usage together with the resource graph (spliut the screen). There you can display in a timephased view the work and the actual work, select an appropriate timescale (view tab) and even detect overallocations.

    • You can use one of the default excel report/template in the BI center, either using the OLAP DB, or the reporting DB.
    • You can create a custom SSRS report.

    If you do need more details about one of those solutions, feel free to ask.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |



    Thursday, June 4, 2015 9:12 AM
    Moderator
  • Are your resources submitting time sheets? If so, you could run a report to retrieve this information.

    The following SQL query assumes that the fiscal periods are named in the format of FY-2015 and Week1. Also, that OLAP is building correctly and that you modify the name of the [dbo].[MSP_TimesheetActual_OlapView_3ADF07A3-55B5-E411-9468-001DD8E8004E] view in the query; adjust as needed to suit your configuration.

    DECLARE @FiscalYear NVarChar(6)
    DECLARE @Month INT
    SET @FiscalYear = '2015'
    SET @Month = 2

    SELECT t.[PeriodName]
          ,SUBSTRING(t.[ResourceName], 1, CHARINDEX('(', t.[ResourceName]) - 1) AS Resource
          ,t.[ProjectName]
          ,o.[ActualWorkBillable]
     ,t.[PeriodStartDate]
     ,o.TimeByDay
          ,r.ResourceStandardRate
          ,ROUND(SUM(t.[ActualWorkBillable] * r.ResourceStandardRate),2) AS RPCost
          ,SUM(t.[ActualWorkBillable] * r.ResourceStandardRate) AS PCost

          
      FROM [dbo].[MSP_TimesheetLine_UserView] t
      JOIN [dbo].[MSP_TimesheetActual_OlapView_3ADF07A3-55B5-E411-9468-001DD8E8004E] o on o.timesheetlineuid = t.timesheetlineuid
      JOIN [dbo].[MSP_EpmResource_UserView] r ON r.ResourceUID = t.ResourceUID
      WHERE t.PeriodName LIKE '%FY-' + @FiscalYear AND MONTH(o.TimeByDay) = @Month AND r.ResourceIsGeneric = 0 AND r.ResourceIsActive = 1 AND t.[PeriodName] LIKE 'Week%' AND t.[ActualWorkBillable] <> 0 
      Group BY [PeriodName],t.[ResourceName], [ProjectName], o.[ActualWorkBillable],[PeriodStartDate],o.TimeByDay,r.ResourceStandardRate
      

    


    Mark M Webster

    • Marked as answer by Jou Chen Friday, June 5, 2015 9:13 AM
    Thursday, June 4, 2015 2:18 PM

All replies

  • Hi Jou Chen,

    You have several ways to achieve this objectives, more or less dynamic, more or less immediate, more or less easy to put in place.

    • From the resources center, select your resources and click on "resource assignments". This is a immediate view which provides you the actual and planned work per resources, projects and per day. You cannot change the timescale but you can switch to the resource availability which provides you a graph with a grid below.

    • From Project Pro, you can have a file with your resources and use the resource usage together with the resource graph (spliut the screen). There you can display in a timephased view the work and the actual work, select an appropriate timescale (view tab) and even detect overallocations.

    • You can use one of the default excel report/template in the BI center, either using the OLAP DB, or the reporting DB.
    • You can create a custom SSRS report.

    If you do need more details about one of those solutions, feel free to ask.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |



    Thursday, June 4, 2015 9:12 AM
    Moderator
  • Are your resources submitting time sheets? If so, you could run a report to retrieve this information.

    The following SQL query assumes that the fiscal periods are named in the format of FY-2015 and Week1. Also, that OLAP is building correctly and that you modify the name of the [dbo].[MSP_TimesheetActual_OlapView_3ADF07A3-55B5-E411-9468-001DD8E8004E] view in the query; adjust as needed to suit your configuration.

    DECLARE @FiscalYear NVarChar(6)
    DECLARE @Month INT
    SET @FiscalYear = '2015'
    SET @Month = 2

    SELECT t.[PeriodName]
          ,SUBSTRING(t.[ResourceName], 1, CHARINDEX('(', t.[ResourceName]) - 1) AS Resource
          ,t.[ProjectName]
          ,o.[ActualWorkBillable]
     ,t.[PeriodStartDate]
     ,o.TimeByDay
          ,r.ResourceStandardRate
          ,ROUND(SUM(t.[ActualWorkBillable] * r.ResourceStandardRate),2) AS RPCost
          ,SUM(t.[ActualWorkBillable] * r.ResourceStandardRate) AS PCost

          
      FROM [dbo].[MSP_TimesheetLine_UserView] t
      JOIN [dbo].[MSP_TimesheetActual_OlapView_3ADF07A3-55B5-E411-9468-001DD8E8004E] o on o.timesheetlineuid = t.timesheetlineuid
      JOIN [dbo].[MSP_EpmResource_UserView] r ON r.ResourceUID = t.ResourceUID
      WHERE t.PeriodName LIKE '%FY-' + @FiscalYear AND MONTH(o.TimeByDay) = @Month AND r.ResourceIsGeneric = 0 AND r.ResourceIsActive = 1 AND t.[PeriodName] LIKE 'Week%' AND t.[ActualWorkBillable] <> 0 
      Group BY [PeriodName],t.[ResourceName], [ProjectName], o.[ActualWorkBillable],[PeriodStartDate],o.TimeByDay,r.ResourceStandardRate
      

    


    Mark M Webster

    • Marked as answer by Jou Chen Friday, June 5, 2015 9:13 AM
    Thursday, June 4, 2015 2:18 PM
  • Hi Guillaume,

    The way No.1 No.2 all worked, ^_^

    You mentioned 'You can use one of the default excel report/template in the BI center, either using the OLAP DB, or the reporting DB.'

    I tried to use 'Resource overview'  in BI center, excel report, to get the data, but failed to find out the way to add Timescales, is there any advise? 


    Jou Chen

    Friday, June 5, 2015 7:08 AM
  • This report is plugged on the reporting DB, which is not timephased. Thus you cannot add the time dimension unless you develop your own SQL query. Only the OLAP DB integrates by default the time dimension (only available on premise, not for Project Online).

    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Friday, June 5, 2015 7:46 AM
    Moderator
  • Oh, I know, thanks again.

    Jou Chen

    Friday, June 5, 2015 9:13 AM