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

Basic query for time management portlet

Highlighted
Greg_Shrout
Collector

Basic query for time management portlet

Hello, everyone. I'm having a devil of a time putting together a portlet that shows time management hours against users and projects in a pie chart or even a list-type portlet. Would anyone care to share an example time management portlet query? The ones that come out of the box are virtually useless.
Thanks.
-Greg
4 REPLIES
Sangiliraja_1
Collector

Re: Basic query for time management portlet

Hi Greg,

I have many portlets that have time managament reporting functions. What type of details are u requiring in Time Reporting. May be i can help u.

Thanks
Sangiliraja.P
Greg_Shrout
Collector

Re: Basic query for time management portlet

OK, here's an example: Let's say in a pie chart I'd like to see the percentage of time a specified group of users are spending on all their projects. From that pie chart, each piece of the pie represents a project. If you click on the project, it will show a list portlet that shows the project, users, and time.
Another use would a simple list portlet that shows who has filled out their timesheet this week.
Or, a stacked bar chart on total hours to each project, filtered by organization structure or security group.
Or, Stacked bar chart, Time metric, by Asset, with the ability to track assets and how time is divided up (enhancements, bug-fixing, etc).
Whatever you have would be great.
Thanks!

Sangiliraja_1
Collector

Re: Basic query for time management portlet

Hi Greg,

I think it is possible. You should be able see do this withh much more effort.

ITG_AUDIT_EVENTS, tm_time_sheet_lines l,
tm_time_sheets s,
tm_actuals ac,
tm_actuals_effort ef,
knta_users u,
ktmg_periods
are tables that can be used for capturing the timesheet data.

Thanks
Sangiliraja.P
Greg_Shrout
Collector

Re: Basic query for time management portlet

Thanks, your input is appreciated. I'm looking for ways to improve this query, or maybe use a different one. This is what I've got so far:
SELECT work_item_cat, sum(actual_time) FROM
(SELECT ku.username
, ku.last_name
, ku.first_name
, ou.org_unit_name
, ku.department_meaning
, lstat.meaning line_status
, DECODE(l.work_item_type, 'TASK', 'PROJECT', 'REQUEST', DECODE(rt.request_type_id, 30397, 'ASSET', 'REQUEST'), l.work_item_type) AS work_item_type
, DECODE(l.work_item_type, 'REQUEST', DECODE(rt.request_type_id, 30397, act.activity_name, wi.work_item), wi.work_item) as work_item
, DECODE(l.work_item_type, 'REQUEST', DECODE(rt.request_type_id, 30397, an.asset_name, rt.request_type_name), 'PROJECT', pp.project_name, 'PACKAGE', wf.workflow_name, 'TASK', pp.project_name, 'MISC', 'Misc. Items') AS work_item_set
, DECODE(l.work_item_type, 'REQUEST', DECODE(rt.request_type_id, 30397, act.activity_name, wi.work_item), 'PROJECT', 'PROJECT', 'PACKAGE', wf.workflow_name, 'TASK', 'PROJECT', 'MISC', wi.work_item) AS work_item_cat
, TO_CHAR(ic.start_time, 'DD-MON-YYYY') actual_date
, kp.period_name
, ae.actual_effort actual_time
FROM tm_time_sheets t
, itg_components ic
, tm_time_sheet_lines l
, tm_actuals a
, tm_actuals_effort ae
, ktmg_work_items_v wi
, kcrt_request_types rt
, kcrt_fg_pfm_asset an
, kwfl_workflows wf
, pm_projects pp
, ktmg_activities_v act
, rsc_resources rr
, knta_users_v ku
, krsc_org_units ou
, krsc_org_unit_members oum
, ktmg_periods kp
, knta_lookups lstat
WHERE 1 = 1
AND ae.actual_effort > 0
AND lstat.lookup_code = l.state
AND lstat.lookup_type = 'TM - Time Sheet Line Statuses'
AND kp.period_id = t.period_id
AND oum.user_id = ku.user_id
AND oum.org_unit_id = ou.org_unit_id
AND rr.user_id = ku.user_id
AND t.resource_id = rr.resource_id
AND t.calendar_id = ic.calendar_id
AND t.time_sheet_id = l.time_sheet_id
AND a.time_sheet_line_id = l.time_sheet_line_id
AND a.actuals_id = ae.actuals_id
AND ae.effort_order = ic.component_seq
AND a.totals_flag = 'N'
AND l.work_item_id = wi.work_item_id
AND l.work_item_type = wi.work_item_type_code
AND rt.request_type_id (+) = wi.work_item_set_id
AND an.request_id (+) = wi.work_item_id
AND wf.workflow_id (+) = wi.work_item_set_id
AND pp.project_id (+) = wi.work_item_set_id
AND act.activity_id (+) = a.activity_id
/****
AND ic.start_time BETWEEN TO_DATE('','yyyy-mm-dd hh24:mi:ss')
AND TO_DATE('','yyyy-mm-dd hh24:mi:ss')
****/
ORDER BY l.work_item_id, actual_date)
GROUP BY work_item_cat
//Add this to "OnDomLoad" event