26 aprilie 2012 16:47
I have two task level custom field one for task status and another for Task Type. I need to create one graph having the count of tasks assigned to resources as Data series and Task End Date in X axis. and Task status and Task Type as filters.
How can I achieve this? Please help.
26 aprilie 2012 17:09Moderator
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
- Marcat ca răspuns de Rose 25 4 mai 2012 13:58
27 aprilie 2012 06:57
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
27 aprilie 2012 07:30
Thanks a lot for the responses.
I will try out the solutions and will upate the post.
4 mai 2012 13:58thanks a lot the solutions helped me.