snehacha
New Member.
435 views

Resource monthly actuals by projects

Jump to solution

Hi,

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!

0 Likes
1 Solution

Accepted Solutions
Erik Cole Acclaimed Contributor.
Acclaimed Contributor.

Re: Resource monthly actuals by projects

Jump to solution

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
,u.full_name
,tsl.time_sheet_line_id
,tsl.work_item_type
,tsl.work_item_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

0 Likes
2 Replies
Erik Cole Acclaimed Contributor.
Acclaimed Contributor.

Re: Resource monthly actuals by projects

Jump to solution

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
,u.full_name
,tsl.time_sheet_line_id
,tsl.work_item_type
,tsl.work_item_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

0 Likes
snehacha
New Member.

Re: Resource monthly actuals by projects

Jump to solution

Thank you so much!! this worked!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.