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

Staffing Profiles Information

SOLVED
Go to solution
Highlighted
Daniel Galvez
Collector

Staffing Profiles Information

Hi all,

Does somebody knows in witch table are the information of a staffing profile written? I need to make a query that shows the total of hours from each role into the staffing profile?
In the table rsc_staffing_profiles I only can find the basic information from the the staffing profile.

Kind regards,
Daniel Galvez
7 REPLIES
Erik Cole
Honored Contributor
Solution

Re: Staffing Profiles Information

Staffing Profile data model enclosed.

The hours are stored in ITG_COMPRESSED_ELEMENTS, and you have to 'decompress' them into days first if you want to query by date range. Otherwise, you could just sum(REPEATS_COUNT * VALUE) to get a total for each for each OWNER_ID (position).
Daniel Galvez
Collector

Re: Staffing Profiles Information

Hi Erick,

Thanks a lot for the response!!!! you are a master!!!!!! This is that I need!!!!

Thank you very much!!!!
Daniel
Semenov Alexand
Esteemed Contributor

Re: Staffing Profiles Information

Hi Daniel!
Can you help me!
I need link OWNER_IP/POSITION_NAME with data periods.
In result I need: ROLE/POSITION_NAME--SE--PERIOD(week,month,year)
Many thanks!!!
Daniel Galvez
Collector

Re: Staffing Profiles Information

Hi Semenov,

Sorry for the delay in my response... Semenov I do not have that kind of query because for my case we only need the total time that the role is needed. But maybe my query helps you in something...

select
max(S.POSITION_NAME) CARGO,max(S.ROLE_ID) COD_ROL,sum(Z.REPEATS_COUNT * Z.VALUE) CANT_HORAS_ROLE,S.POSITION_ID,
max(W.AV_ID_TIPO_COSTO_TOTAL/160) COSTOXHORA , sum(Z.REPEATS_COUNT * Z.VALUE)* max(W.AV_ID_TIPO_COSTO_TOTAL/160) COSTO_TOTAL
from rsc_positions S, itg_compressed_elements Z, av_criterios_costos W
where S.position_id=Z.OWNER_ID
and W.ROLE_ID=S.ROLE_ID
and S.POSITION_ID in (select S1.POSITION_ID
from kcrt_request_details T, rsc_positions S1
where S1.STAFFING_PROFILE_ID=T.PARAMETER5
and T.REQUEST_ID = REQID)
group by S.POSITION_ID;

I hope it helps you...
Kind regards,
Daniel
Semenov Alexand
Esteemed Contributor

Re: Staffing Profiles Information

Thanks,Daniel!!
Don Seagraves
Super Collector

Re: Staffing Profiles Information

I understand the FORECAST, ALLOC and ACTUAL hours are found on
ITG_COMPRESSED_ELEMENTS and you have to 'decompress' them into days first if you want to query by date range.

I want the data this way so I can sum by month for each person.

How do you decompress this data?
Erik Cole
Honored Contributor

Re: Staffing Profiles Information

//Add this to "OnDomLoad" event