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

Resource Management SQL

Highlighted
kev marks
Regular Collector

Resource Management SQL

Hi,
We are using Resource Management Functionality in PPM 7.1. We need to build some custom Crystal reports to integrate the Project Request and Staffing Profile / Res. Mgmt / Time Mgt data.

Has anyone built thier own code similar to this, We tried to look at what the "Analyze Assignment Load" Portlet and "Analyze Resource Pools" sql was, but we had no luck in pulling the code out.

Has anyone wrote any SQL or views that they think would help out? We are just looking for a base to start from, instead of recreating work that others have already done.

We do have the TRM already.

Thanks in advance
Kevin
kevmarks@gmail.com

6 REPLIES
Varun Singh
Regular Collector

Re: Resource Management SQL

Hi Kevin,

We have build the analyse resource pool report, although we still not complete. This is undergoing a testing and will soon will let you know the outcome.

Regards
Varun
Arguing with a fool, proves there are two
Nir Hadar
Occasional Contributor

Re: Resource Management SQL

Hi Kevin,
I looking for a similar portlet, it would help me a lot if you could send me the answer you get or anything else in this matter .
if i'll make any progress i'll inform you.
thanking you in advance.
kev marks
Regular Collector

Re: Resource Management SQL

Varun,
Did you have success with the Res Mgmt report?

Kevin
Emir Gocen
Super Collector

Re: Resource Management SQL

Hi everyone,

I was able to pull the SQL code out of the Analyze Assignment Load Portlet (AALP) via enabling JDBC debugging with the maximum level through workbench's Edit->Debug Settings. Then I refreshed the browser which was on the AALP, disabled the debugging opiton. The log was created on Home_Path_of the PPM\server\serve_rname\log with a name like jdbc.server_name_date_time.log. I have provided the SQL code at the attachment.

Due to the fact that we are not using the resources tab for the operational requests (small ones) AALP is not capturing the scheduled effort of the operational requests. As a result, I have created a custom cluster chart portlet. I will share the details with you when I am done with the testing.

Kind regards.

Emir
Emir Gocen
Super Collector

Re: Resource Management SQL

SQL code attached.
Emir Gocen
Super Collector

Re: Resource Management SQL

I was not ablo to attach the code so I have written it down.

SELECT TO_CHAR(ASSIGNS.calendar_day,'MM/DD/YY') period_name, ASSIGNS.planned_effort value,USERS.username name, USERS.user_id user_id, ASSIGNS.work_item_id work_item_id, ASSIGNS.group_name request_type_name
FROM KRSC_daily_plans_v ASSIGNS, KNTA_users USERS, KCRT_requests REQUESTS, RSC_resources res
WHERE (ASSIGNS.calendar_day) BETWEEN TO_DATE('12/20/09','MM/DD/YY') AND TO_DATE('04/10/10','MM/DD/YY') AND ASSIGNS.user_id IN (30397) AND ASSIGNS.user_id = USERS.user_id AND res.user_id = USERS.user_id AND ASSIGNS.workload_flag = 'Y' AND ASSIGNS.work_item_entity_id = 20
AND REQUESTS.STATUS_ID != 4 AND ASSIGNS.work_item_id = REQUESTS.request_id
ORDER BY ASSIGNS.work_item_id asc, (ASSIGNS.calendar_day) asc

Emir
//Add this to "OnDomLoad" event