The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Can we get a report for how many ITGs have been completed by a group of users

Highlighted
ramana.vkn
Collector

Can we get a report for how many ITGs have been completed by a group of users

Hi,

In PPM we can get the reports based of 'Assigned to' field.

In the same way can we get the report for ITG's using 'completed by' field.

This field can be seen under 'Status' section of request.

Regards,
Venkat
8 REPLIES
Mahen M
Honored Contributor

Re: Can we get a report for how many ITGs have been completed by a group of users

I am not sure what you mean by ITG in this context.

You mean to ask for a report on who closed the Request.

You wanty to find what are the requests which is closed by X or Y, Right?

Regards,
Mahendran M
ramana.vkn
Collector

Re: Can we get a report for how many ITGs have been completed by a group of users

Hi Mahendran,

We need to find what are the requests which are closed by X and Y.
Mahen M
Honored Contributor

Re: Can we get a report for how many ITGs have been completed by a group of users

select * from prodpitg.KWFL_STEP_TRANSACTION_HISTORY
where status = 'COMPLETE' AND
step_transaction_id =
(select max(step_transaction_id)
from prodpitg.KCRT_STEP_TRANSITIONS_V
where request_id = 184992 and step_name = 'Close (Immediate Success)')


The Last_Updated_by of the above query gives the name of the person who closed the request, Please modify the Query to your requirement.

Regards,
Mahendran M
ramana.vkn
Collector

Re: Can we get a report for how many ITGs have been completed by a group of users

Hi Mahendran,

I hope I should have been more specific.

When a user login to PPM and click on â Reportsâ and creates a report by specifying â Request Typeâ , â Assigned Toâ , â Created start Dateâ and â Created end Dateâ . By this user will get all the requests that are assigned to say User â Aâ and User â Bâ within the specified date.

In the same way can we need to find what are the requests which are closed by User 'A' and User 'B' between specified dates using reports.

Can this be achieved?
Mahen M
Honored Contributor

Re: Can we get a report for how many ITGs have been completed by a group of users

Hi,
That is OOTB Report, I dont think that we have a field called 'Closed by' like 'Assigned To'. You can create a Custon Portlet or you can Query the DB to get the desired results.

Regards,
Mahendran M
ramana.vkn
Collector

Re: Can we get a report for how many ITGs have been completed by a group of users

Thanks Mahendran

I am trying to create a portlet to do this, In Request List portlet we have a filter Creation Date From: To:

Could you please tell me how can we add this filter in other portlets.
Mahen M
Honored Contributor

Re: Can we get a report for how many ITGs have been completed by a group of users

Venkat,
That is Java Portlet developed by JSR168 and you are writing a Query to the portlet, both are different to the base.

You can create a new filter called Start date and End Date and when you give the date you have to write the 'Where' clause.

Sample Query.



and (WT1.SCHED_START_DATE between
nvl(to_date(
CASE WHEN '[P.START_DATE]' LIKE '%START_DATE%' THEN
NULL
ELSE '[P.START_DATE]'
END, 'yyyy-mm-dd hh24:mi:ss'), to_date('2006-11-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) and

nvl(to_date(
CASE WHEN '[P.END_DATE]' LIKE '%END_DATE%' THEN
NULL
ELSE '[P.END_DATE]'
END, 'yyyy-mm-dd hh24:mi:ss'),sysdate) OR
WT1.SCHED_FINISH_DATE between
nvl(to_date(
CASE WHEN '[P.START_DATE]' LIKE '%START_DATE%' THEN
NULL
ELSE '[P.START_DATE]'
END, 'yyyy-mm-dd hh24:mi:ss'), to_date('2006-11-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) and
nvl(to_date(
CASE WHEN '[P.END_DATE]' LIKE '%END_DATE%' THEN
NULL
ELSE '[P.END_DATE]'
END, 'yyyy-mm-dd hh24:mi:ss'),sysdate))

Regards,
Mahendran M
Greg_Shrout
Collector

Re: Can we get a report for how many ITGs have been completed by a group of users

Venkat: Here's portlet query that I use extensively. Please note the data range at the end of the query. Good Luck!
-Greg
SELECT r.request_id REQUEST_ID, r.request_id REQUEST_ID_HYPERLINK, r.priority_code PRIORITY_CODE, r.description REQUEST_TITLE,
s.status_name WORKFLOW_STAGE, rh.parameter41 STATUS_NOTES, substr(rh.parameter42,1,150) REQUEST_SUMMARY,
rh.creation_date CREATION_DATE, SUBSTR(c.full_name,INSTR(c.full_name,', ',1,1)+2) || ' '||
SUBSTR(c.full_name,1,INSTR(c.full_name,',',1,1)-1)||' '||SUBSTR(rdb1.visible_parameter18,INSTR(rdb1.visible_parameter18,', ',1,1)+2) || ' '||
SUBSTR(rdb1.visible_parameter18,1,INSTR(rdb1.visible_parameter18,',',1,1)-1) OWNERS, RANK() over(order by to_number(rh.parameter4)desc,
r.priority_code) RANK, to_number(rh.parameter3) CUST_RANK_POINTS, to_char(to_date(rdb2.parameter5, 'YYYY-MM-DD HH24:MI:SS'), 'mm/dd/yy') ||' '||
chr(10)|| to_char(to_date(rdb2.parameter20, 'YYYY-MM-DD HH24:MI:SS'), 'mm/dd/yy') DELIVERY_DATES, rdb3.parameter6 ACTUAL_DATE,
rh.parameter17 BUSINESS_STRATEGY_CODE, rh.parameter19 CURRENT_STATUS, rh.parameter12 REQUESTING_CUSTOMER,
rdb1.visible_parameter15 ASSIGNED_TECH, to_number(rh.parameter4) IT_RANK_POINTS, decode(rdb2.parameter38,'Request Cancelled',0,
decode(rh.VISIBLE_parameter16,'< $1,000',500,'$1,000 - $10,000',5000,'$10,000 - $50,000',30000,'$50,000 - $100, 000',75000,'$50,000 - $100,000',
75000,'$100,000 - $250,000',175000,'$250,000 - $500,000',375000,'$500,000 - $750,000',625000,'$750,000 - $1,000,000',
875000,'$1,000,000 - $2,000,000',1500000,'$2,000,000 - $3,000,000',2500000,'$3,000,000 - $4,000,000',3500000,'$4,000,000 - $5,000,000',
4500000,'Over $5 Million',5500000,rh.parameter26)) BUSINESS_VALUE_1, to_number(rh.parameter5) TECHNICAL_POINTS,
rdb1.parameter20 REQUEST_TYPE02, (select c1.full_name from kcrt_contacts c1 where rh.parameter6 = c1.contact_id) SECONDARY_CONTACT1,
rdb1.parameter23 ESTIMATED_EFFORT, decode(kintana.pel_is_numeric(substr(rdb1.parameter1,1,4)), 'T',rdb1.parameter1,null) EST_TECH_DEL_DT,
rdb1.parameter11 NAMED_PROJECT, rdb1.parameter39 TECH_NEEDED, rdb1.parameter35 ASSIGNED_ENV, rh.parameter26 BUSINESS_VALUE_EXACT,
rdb1.visible_parameter25 IMPLEMENTER, rdb3.visible_parameter4 TEAM_OWNER, rh.visible_parameter17 BUSINESS_STRATEGY_TEXT,
rh.parameter26 BUSINESS_VALUE_2, c.full_name CUSTOMER_OWNER, rdb1.parameter16 IT_BUSINESS_GROUP, rdb1.parameter17 IT_FUNCTIONAL_TEAM,
rdb1.parameter19 TEAM_MODULE, rdb1.visible_parameter18 IT_OWNER, rdb1.visible_parameter25 IMPLEMENTER_2, rdb3.visible_parameter4 IT_TEAM_OWNER,
substr(rh.parameter43,1,150) BUSINESS_SUMMARY, substr(rdb3.parameter46,1,150) TG4_NOTES, rh.parameter18 CREATED_BY,
rh.parameter2 REMEDY_OR_SR_NO, r.last_update_date LAST_UPDATED, rdb3.parameter5 IS_THIS_AN_EMERGENCY,
rdb2.parameter38 REASON_TO_CLOSE,
substr(rdb3.parameter44,1,150) PROD_RESULTS, decode(rt.request_type_name,'Oracle Change Request','Request','IT Change Request','Request','IT Change Log','Log','') REQUEST_TYPE,
rdb1.parameter2 LOG_TYPE,
rdb3.parameter40 PLANNED_DATE
From KINTANA.KCRT_STATUSES s,
KINTANA.KCRT_REQUESTS r,
kintana.kcrt_request_types rt,
KINTANA.KCRT_REQ_HEADER_DETAILS rh,
KINTANA.KNTA_USERS u,
KINTANA.KNTA_USERS u1,
KINTANA.KNTA_USERS u2,
KINTANA.kcrt_contacts c,
(select * from KINTANA.KCRT_REQUEST_DETAILS where batch_number = 1)rdb1,
(select * from KINTANA.KCRT_REQUEST_DETAILS where batch_number = 2)rdb2,
(select * from KINTANA.KCRT_REQUEST_DETAILS where batch_number = 3)rdb3
Where (r.request_type_id = 30062 or r.request_type_id = 30133 or r.request_type_id = 30257)
and r.request_type_id = rt.request_type_id
and s.status_id = r.status_id
and u1.user_id = r.created_by (+)
and r.ASSIGNED_TO_USER_ID = u.user_id(+)
and r.request_id = rh.request_id
and r.contact_id = c.contact_id (+)
and u1.user_id = u2.user_id (+)
and r.request_id = rdb1.request_id(+)
and r.request_id = rdb2.request_id(+)
and r.request_id = rdb3.request_id(+)
and r.status_code not like 'CANCEL%'
AND rdb3.parameter6 >= ([P.ACTUAL_DATE_START.TO_STRING])
AND rdb3.parameter6 <= ([P.ACTUAL_DATE_END.TO_STRING])
order by to_number(rh.parameter4) desc, r.priority_code
//Add this to "OnDomLoad" event