Thursday, 26 April, 2012 4:47 PM
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.
Thursday, 26 April, 2012 5:09 PMModerator
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
- Marked As Answer by Rose 25 Friday, 4 May, 2012 1:58 PM
Friday, 27 April, 2012 6:57 AM
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
Friday, 27 April, 2012 7:30 AM
Thanks a lot for the responses.
I will try out the solutions and will upate the post.
Friday, 4 May, 2012 1:58 PMthanks a lot the solutions helped me.