Project and Portfolio Management Practitioners Forum
Showing results for 
Search instead for 
Did you mean: 

Resource monthly actuals by projects

Go to solution
Super Collector

Resource monthly actuals by projects


I'm creating a portlet to display resource monthly actuals by projects. E.g. a resource A submited 80 hours for a project XYZ, and 40 hours for project WUV in the month of August 2016. I need to display his actuals by month by projects. In the database I can only find actuals by time periods, but not by dates. I need all the actuals he submitted between Aug 1 and Aug 31. How should I achieve this? Is there any ootb table/function to get this info? If not, has anyone created any custom function for this? Thank you!

Erik Cole
Honored Contributor

Re: Resource monthly actuals by projects

Hi....our time periods are weekly and I've toyed with the following query to break it out by day since the time is actually entered & stored that way in tm_actuals_effort. You might find it helpful...

select ts.time_sheet_id
,ktmg_utils.derive_work_item_description(tsl.WORK_ITEM_ID,tsl.WORK_ITEM_TYPE) "DESCRIPTION"
,ae.actual_effort,p.start_date + ae.effort_order "ACTUALS_DATE"
from tm_actuals ta
join tm_actuals_effort ae on ae.actuals_id = ta.actuals_id
join tm_time_sheet_lines tsl on tsl.time_sheet_line_id = ta.time_sheet_line_id
join tm_time_sheets ts on ts.time_sheet_id = tsl.time_sheet_id
join ktmg_periods p on p.period_id = ts.period_id
join knta_users u on u.user_id = ts.resource_id
where 1=1
--and ts.time_sheet_id = 149761
and u.full_name = 'Erik Cole'
and ta.totals_flag = 'Y'
and tsl.state != 5
and ae.actual_effort > 0

Super Collector

Re: Resource monthly actuals by projects

Thank you so much!! this worked!

//Add this to "OnDomLoad" event