Hours to FTE Conversions

All Replies

• Tuesday, May 15, 2012 11:09 AM

   

   
  

  1

  Hi Reg--

  I remember that with the similar kind of requirement in past, I have used the Resource Capacity Vs Denmand report of Project server 2007 repot pack 2 (under Governance Report).
  http://blogs.msdn.com/b/project_programmability/archive/2009/08/20/project-server-2007-report-pack-ii-the-top-reports.aspx
  You can get the data easily but displaying may be a little tricky.

  I used the same for PS2010. Here is the query which gets the data in 2-3 seconds from more than 1000 resources & 100s of projects.
  You may see that you can get the data of Resource capacity data (palnned work across projects) for the selected weeks, Months & years etc.
  

  if object_id('tempdb..#temp_table1') is not null
  drop table #temp_table1;
  select isnull( assignment_table.TimebyDay , capacity_table.timebyday) as [Time of Day],
  assignment_table.AssignmentWork as [Planned Work],
  capacity_table.capacity as [Capacity],
  isnull(assignment_table.resourcename,capacity_table.resourcename) as [Resource Name],
  isnull(assignment_table.rbs,capacity_table.rbs) as [RBS],
  --isnull(assignment_table.[Position Role],capacity_table.[Position Role]) as [Position Role],
  isnull(assignment_table.[Project Name],'<capacity  with no project association>') as [Project Name],
  isnull(capacity_table.[Year],assignment_table.[Year]) as [Year],
  isnull(capacity_table.[Quarter],assignment_table.[Quarter] ) as [Quarter],
  isnull(capacity_table.CalendarMemberKeyQuarter,assignment_table.CalendarMemberKeyQuarter )  as [Quarter Key],
  isnull(capacity_table.[Month],assignment_table.[Month] )  as [Month],
  isnull(capacity_table.CalendarMemberKeyMonth,assignment_table.CalendarMemberKeyMonth) as [Month Key],
  isnull(capacity_table.[Week],assignment_table.[Week] )  as [Week],
  isnull(capacity_table.CalendarMemberKeyWeek,assignment_table.CalendarMemberKeyWeek ) as [Week Key]
  into #temp_table1 
  from 
  ( select ADUV.TimebyDay ,
  ADUV.AssignmentWork,
  rt.resourceuid,
  RT.ResourceName,
  RT.RBS,
  --RT.[Position Role],
  PT.ProjectName as [Project Name],
  TDT.CalendarMemberNameYear as [Year],
  TDT.CalendarMemberNameQuarter as [Quarter],
  TDT.CalendarMemberKeyQuarter,
  TDT.CalendarMemberNameMonth as [Month],
  TDT.CalendarMemberKeyMonth,
  TDT.CalendarMemberNameWeek as [Week],
  TDT.CalendarMemberKeyWeek
  from MSP_EPMAssignmentByDay ADUV
  left outer join
  MSP_EPMAssignment AUV
  on ADUV.AssignmentUID = AUV.AssignmentUID
  left outer join
  MSP_TimeByDay_OlapView TDT
  on ADUV.TimeByDay = TDT.TimeByDay
  left outer join
  MSP_EPMProject_UserView PT
  on AUV.ProjectUID = PT.ProjectUID
  left outer join
  MSP_EPMResource_UserView RT
  on AUV.ResourceUID = RT.ResourceUID
  where
  rt.resourceisgeneric = 0
  and
  TDT.CalendarMemberKeyYear = 2011 and
  TDT.CalendarMemberKeyQuarter = 2 and
  TDT.CalendarMemberKeyMonth IN (4, 5) and
  TDT.CalendarMemberKeyWeek IN (14,15) 
  --and RT.RBS in (@RBS)
  ) assignment_table
  full outer join 
  (
  select 
  RDT.Capacity,
  rdt.timebyday,
  RT.ResourceName,
  rt.resourceuid,
  RT.RBS,
  --RT.[Position Role],
  TDT.CalendarMemberNameYear as [Year],
  TDT.CalendarMemberNameQuarter as [Quarter],
  TDT.CalendarMemberKeyQuarter,
  TDT.CalendarMemberNameMonth as [Month],
  TDT.CalendarMemberKeyMonth,
  TDT.CalendarMemberNameWeek as [Week],
  TDT.CalendarMemberKeyWeek
  from 
  MSP_EPMResourceByDay_UserView RDT
  left outer join
  MSP_TimeByDay_OlapView TDT
  on RDT.TimeByDay = TDT.TimeByDay
  left outer join
  MSP_EPMResource_UserView RT
  on rdt.ResourceUID = RT.ResourceUID
  where
  rt.resourceisgeneric = 0
  and
  TDT.CalendarMemberKeyYear = 2011 and
  TDT.CalendarMemberKeyQuarter = 2 and
  TDT.CalendarMemberKeyMonth IN (4, 5) and
  TDT.CalendarMemberKeyWeek IN (14, 15) 
  --and RT.RBS in (@RBS)
  )  capacity_table
  on capacity_table.resourceuid = assignment_table.resourceuid
  and capacity_table.timebyday = assignment_table.timebyday
  select
  (select 
  [Capacity]/count(*)
  from #temp_table1 ITT
  where ITT.[Time of Day] = OTT.[Time of Day]
  and ITT.[Resource Name] = OTT.[Resource Name]
  group by [Capacity]
  )	as [SRS Capacity Calculated Contribution]
  ,*
  from #temp_table1 OTT

  Hope that helps.
  

  ---

  If you found this post helpful, please "Vote as Helpful";. If it answered your question, Please "Mark as Answer" .
  
  Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

  
  
  
  

  • Edited by Amit Khare - Project Management Consultant Wednesday, May 16, 2012 10:06 AM
  •  
  • Reply
  • Quote

   
• Tuesday, May 15, 2012 3:11 PM

   

   
  

  0

  Hi Amit--

  Thanks for helping out, but this actually does not solve my problem.  First, I want to look into the resource plans, not into actual planned work.  That's fine, I just end up substituting 'AssignmentResourcePlanWork' where you have 'AssignmentWork'.  However, this still gives me a row for each day and the work is expressed in hours.  I need to view a list of resources, by project, organized by month and in the FTE unit as opposed to hours.  Again, the challenge is that each month has a different number of work hours so the FTE divisor is variable.

  Here is the full query, so you'll be able to run it:

  SELECT        MSP_EpmAssignmentByDay_UserView.ProjectUID, MSP_EpmAssignmentByDay_UserView.AssignmentUID, r1.ResourceName,
                            MSP_EpmResource_UserView_1.ResourceName AS ProjectManager,
  /* Jan, 2012 */
   (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                                 FROM            MSP_EpmResource_UserView INNER JOIN
                                                           MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                           MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                           MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                                 WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID) AND (uv2.TimeByDay between '1/1/2012' and '1/31/2012') AND AssignmentType = 101)/160 AS [January 2012 FTE] ,
                                 
  /* Feb, 2012 */
   (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                                 FROM            MSP_EpmResource_UserView INNER JOIN
                                                           MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                           MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                           MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                                 WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID)AND (uv2.TimeByDay between '2/1/2012' and '2/29/2012') AND AssignmentType = 101)/160 AS [Febuary 2012 FTE] ,
  /* March, 2012 */
   (SELECT        SUM(uv2.AssignmentResourcePlanWork) AS Expr1
                                 FROM            MSP_EpmResource_UserView INNER JOIN
                                                           MSP_EpmAssignment_UserView ON MSP_EpmResource_UserView.ResourceUID = MSP_EpmAssignment_UserView.ResourceUID INNER JOIN
                                                           MSP_EpmProject_UserView ON MSP_EpmAssignment_UserView.ProjectUID = MSP_EpmProject_UserView.ProjectUID INNER JOIN
                                                           MSP_EpmAssignmentByDay_UserView AS uv2 ON MSP_EpmAssignment_UserView.AssignmentUID = uv2.AssignmentUID
                                 WHERE        (uv2.ProjectUID = uv1.ProjectUID) AND (r1.ResourceUID = MSP_EpmResource_UserView.ResourceUID)AND (uv2.TimeByDay between '3/1/2012' and '3/31/2012' ) AND AssignmentType = 101)/176 AS [March 2012 FTE] 
                                 
                                 /*
  Continue to do the same for each month, using the correct divisor--code removed temporarily to demonstrate concept */                  
  FROM            MSP_EpmResource_UserView AS r1 INNER JOIN
                           MSP_EpmAssignment_UserView AS MSP_EpmAssignment_UserView_1 ON r1.ResourceUID = MSP_EpmAssignment_UserView_1.ResourceUID INNER JOIN
                           MSP_EpmProject_UserView AS uv1 ON MSP_EpmAssignment_UserView_1.ProjectUID = uv1.ProjectUID INNER JOIN
                           MSP_EpmAssignmentByDay_UserView ON MSP_EpmAssignment_UserView_1.AssignmentUID = MSP_EpmAssignmentByDay_UserView.AssignmentUID INNER JOIN
                           MSP_EpmResource_UserView AS MSP_EpmResource_UserView_1 ON uv1.ProjectOwnerResourceUID = MSP_EpmResource_UserView_1.ResourceUID
  WHERE        (MSP_EpmAssignment_UserView_1.AssignmentType = 101) AND (MSP_EpmAssignment_UserView_1.AssignmentResourceType = 2) AND (r1.ResourceIsActive = 1)
  GROUP BY MSP_EpmAssignmentByDay_UserView.ProjectUID, MSP_EpmAssignmentByDay_UserView.AssignmentUID, r1.ResourceName, 
                           MSP_EpmResource_UserView_1.ResourceName, uv1.ProjectUID, r1.ResourceUID

  Thanks-o-lot--

  Reg

  • Reply
  • Quote

   
• Tuesday, May 15, 2012 9:57 PM

  Moderator

   

   
  

  1

  I'm wondering if you're not making this too complicated. The accuracy level you're trying to achieve, downward dog moves and all ;-) may be unnecessary. For example construction workers might have a 9h day and software developers a 6h day (allowing for support work etc). Therefore I suspect a lookup table of conversion rates for each department or skill is needed or use the max units for each resource.

  Using the dates table and the Time_by_day View monthly work divided by the conversion rate is all you need. So 100h of work for a resource with a conversion factor of 5h/d is 200 FTE. Then of course FTE can be defined differently by different orgs. In some 1 FTE is 8h/day regardless of leave, BAU etc.

  The EPM_TimeByDay table combined with MSP_EpmAssignmentByDay allows calc of assignment work per month. Divide by 8 (or whatever) then maybe divide by max units and there's a workable answer?
  

  ---

  Rod Gill

  The one and only Project VBA Book

  Rod Gill Project Management

  • Reply
  • Quote

   
• Wednesday, May 16, 2012 11:19 PM

   

   
  

  0

  Amit and Rod--

  The combination of both of your answers pushed me in the right direction.  

  Trying to convert to FTE using the capacity query was problematic due to the way the data was returning and I was 'relaxed' enough from my Reverse Butterfly Possum-Stance to figure out how to customize it.  If we had major variations in capacity within our pool then I'd have to.  Instead I took Rod's advice and just created a look-up table with hard coded values that told me the number of working hours in each month, which is exactly the number I needed.   Then I wrote this query--again, borrowing advice from each of you:

  if object_id('tempdb..#temp_table1') is not null
  drop table #temp_table1;
  SELECT        SUM(ABDO.AssignmentResourcePlanWork) / StandardHoursByMonth.StandardHours AS FTE, RO.ResourceName, PO.ProjectName, TBDO.CalendarMemberNameMonth, TBDO.CalendarMemberNameYear
  INTO              [#temp_table1]
  FROM            MSP_EpmAssignment_UserView AS AO INNER JOIN
                           MSP_EpmProject_UserView AS PO ON AO.ProjectUID = PO.ProjectUID INNER JOIN
                           MSP_EpmAssignmentByDay_UserView AS ABDO ON AO.AssignmentUID = ABDO.AssignmentUID INNER JOIN
                           MSP_TimeByDay_OlapView AS TBDO ON ABDO.TimeByDay = TBDO.TimeByDay INNER JOIN
                           MSP_EpmResource_UserView AS RO ON AO.ResourceUID = RO.ResourceUID INNER JOIN
                           StandardHoursByMonth ON TBDO.CalendarMemberKeyMonth = StandardHoursByMonth.TimeMonthOfTheYear AND TBDO.CalendarMemberNameYear = StandardHoursByMonth.TimeYear INNER JOIN
                           MSP_EpmResource_UserView AS ROPM ON PO.ProjectOwnerResourceUID = ROPM.ResourceUID
  WHERE        (TBDO.CalendarMemberKeyMonth IN (4, 5, 6, 7, 8)) AND (TBDO.CalendarMemberKeyYear = 2012) And RO.ResourceIsActive = 1
  GROUP BY RO.ResourceName, PO.ProjectName, TBDO.CalendarMemberNameMonth, TBDO.CalendarMemberNameYear, StandardHoursByMonth.StandardHours
  SELECT        Results.ResourceName, Results.ProjectName,
                               (SELECT        FTE
                                 FROM            [#temp_table1] AS ResultsI
                                 WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                           (ResultsI.CalendarMemberNameMonth = 'April')) AS [April FTE],
                               (SELECT        FTE
                                 FROM            [#temp_table1] AS ResultsI
                                 WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                           (ResultsI.CalendarMemberNameMonth = 'May')) AS [May FTE],
                               (SELECT        FTE
                                 FROM            [#temp_table1] AS ResultsI
                                 WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                           (ResultsI.CalendarMemberNameMonth = 'June')) AS [June FTE],
                               (SELECT        FTE
                                 FROM            [#temp_table1] AS ResultsI
                                 WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                           (ResultsI.CalendarMemberNameMonth = 'July')) AS [July FTE],
  							 (SELECT        FTE
                                 FROM            [#temp_table1] AS ResultsI
                                 WHERE        (ResultsI.ResourceName = Results.ResourceName) AND (ResultsI.ProjectName = Results.ProjectName) AND (ResultsI.CalendarMemberNameYear = 2012) AND 
                                                           (ResultsI.CalendarMemberNameMonth = 'August')) AS [August FTE]
                                                           
  FROM            [#temp_table1] AS Results
  GROUP BY Results.ResourceName, Results.ProjectName

  Note that the reason I store it in a temp table and then query it again is to flatten it out.  The first query is very fast, but creates a row for each month.  I need the months as columns for my purposes.

  The schema of the table I created looks like this:

  CREATE TABLE [dbo].[StandardHoursByMonth](
  	[StandardHoursKey] [uniqueidentifier] NOT NULL,
  	[TimeMonthOfTheYear] [tinyint] NOT NULL,
  	[TimeYear] [smallint] NOT NULL,
  	[StandardHours] [smallint] NOT NULL,
   CONSTRAINT [PK_StandardHoursByMonth] PRIMARY KEY CLUSTERED 
  (
  	[StandardHoursKey] ASC
  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  ) ON [PRIMARY]

  
  

  My final query still takes about 2 minutes to run (1 minute in this form, but I have a number of custom fields I removed).  I suspect that further optimization would be possible, but not sure how.  I'm generally satisfied with a gazillion percent performance improvement though.

  Thanks for all of your help.

  - Reg Dwight, PCP

  • Marked As Answer by RegDwight Thursday, May 17, 2012 2:49 AM
  •  
  • Reply
  • Quote

   
• Thursday, May 17, 2012 2:35 AM

  Moderator

   

   
  

  0
  Excellent. Thanks for sharing this and good luck with the yoga!

  ---

  Rod Gill

  The one and only Project VBA Book

  Rod Gill Project Management

  • Reply
  • Quote