How to create a task count report based on the resource name in project server 2010?

Az összes válasz

• 2012. április 26. 17:09

  Moderátor

   

   
  

  0

  You could use the sql below as a starting point. It contains resource name, task finish and 'Cert Type' as well as Task UID. Change 'Cert Type' to your custom field name.

  It will return the basic data. Then in your reporting tool group the data by Resource Name and have the dates across the top with a Count function acting on the UID field.

  Select MSP_EpmResource_UserView.ResourceName, MSP_EpmTask_UserView.TaskFinishDate, MSP_EpmTask_UserView.[Cert Type] 
  From MSP_EpmResource_UserView Inner Join MSP_EpmAssignment_UserView On MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID 
  Inner Join MSP_EpmTask_UserView On MSP_EpmAssignment_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID

  
  

  ---

  Brian Kennemer - Project MVP
  DeltaBahn Senior Architect
  endlessly obsessing about Project Server…so that you don’t have to.
  Blog | Twitter | LinkedIn

  • Megjelölte válaszként: Rose 25 2012. május 4. 13:58
  •  
  • Válasz
  • Idézés

   
• 2012. április 27. 6:57

   

   
  

  0

  Hi Lovely--

  I used the Task Count report of EPM 2007 Report pack 2 with minor changes for one of my customer.

  In my case I mentioned TaskCf1 & TaskCf2

  select RUV.ResourceName,
  PUV.ProjectName,
  TUV.TaskName,
  TUV.[TaskCF1],
  TUV.[TaskCF2],
  (ADUV.AssignmentActualWork + ADUV.AssignmentActualOvertimeWork) as [Total Actual Work],
  TUV.TaskPercentCompleted as [%Complete],
  [Task Status] = case  
                 when TaskPercentCompleted = 100 then 'Complete'
                 when TaskPercentCompleted < 100 and ( Getdate() > TUV.TaskFinishDate ) then 'Over Due'
                 when TaskPercentCompleted = 0 then 'Not Started'
                 when TaskPercentCompleted > 0 then 'In Progress'
                 end,
  ADUV.TimeByDay
  into #t
  from MSP_EpmAssignmentByDay ADUV
  left outer join
  MSP_EpmAssignment_UserView AUV
  on ADUV.AssignmentUID = AUV.AssignmentUID
  left outer join
  MSP_EpmProject_UserView PUV
  on ADUV.ProjectUID = PUV.ProjectUID
  left outer join
  MSP_EpmTask_UserView TUV
  on ADUV.TaskUID = TUV.TaskUID
  left outer join
  MSP_EpmResource_UserView RUV
  on AUV.ResourceUID = RUV.ResourceUID
  --where 
  --aduv.TimeByDay between ''' + cast('12/1/2001' as varchar(50)) + ''' 
  --and ''' + cast('12/1/2012' as varchar(50)) + ''' 
  --and RUV.ResourceNTAccount = ''' + 'Amit Khare' + '''
  select [Task Status], COUNT([Task Status]) as [Task Count],cast(round(100 * (cast(COUNT([Task Status]) as decimal)/cast((select COUNT(*) from #t)as decimal)),0) as int) as [% of All Tasks] from #t group by [Task Status]
  --Drop table #t

  You can provide the Resource name, CF1, Cf2  & dates etc to filter the data. Once you have the data, You can use the Graph.

  Hope that helps.

  ---

  Thanks, Amit Khare |EPM Consultant| Blog: http://amitkhare82.blogspot.com http://www.linkedin.com/in/amitkhare82

  • Válasz
  • Idézés

   
• 2012. április 27. 7:30

   

   
  

  0

  Thanks a lot for the responses.

  I will try out the solutions and will upate the post.

  ---

  Thanks, Lovely

  • Válasz
  • Idézés

   
• 2012. május 4. 13:58

   

   
  

  0
  thanks a lot the solutions helped me.

  ---

  Thanks, Lovely

  • Válasz
  • Idézés