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: 

staffing profile load for period

Highlighted
Semenov Alexand
Esteemed Contributor

staffing profile load for period

Hi ALL.
I need create an report from staffing profile.
Neede columns for report
POLE--SE--PERIOD

But I can't resive period date for SE.
Perion not for all sp, custom month,week i.e.
Can you help me???
Many Thanks!!!!
16 REPLIES
Semenov Alexand
Esteemed Contributor

Re: staffing profile load for period

Sorry.
ROLE--SE--PERIOD
Erik Cole_1
Esteemed Contributor

Re: staffing profile load for period

Are you trying to decode ITG_COMPRESSED_ELEMENTS?
Semenov Alexand
Esteemed Contributor

Re: staffing profile load for period

Erik, thanks.
I try use ITG_COMPRESSED_ELEMENTS, but what you mean to decode? In this table allow repeats and values. How to link this to date/calendar?
Thanks
Erik Cole_1
Esteemed Contributor

Re: staffing profile load for period

That's what I mean...you have to decompress the data in order to 'build' a calendar. Drop me an e-mail to erik.cole@gpi.com and I'll send you the details.
Phanishwar
Collector

Re: staffing profile load for period

Hi Erike,

I also have a similar requirement to build staffing profile report for cisco quaters..i also need to have a calendra..please help me out....many thanks in advance!!
Shravan Kathuri
Senior Member

Re: staffing profile load for period

Hi Erik,

We have also similar requirement where we need to know the FTE's month wise.

Can you provided the details how to get the data month wise?


Thanks
Shravan kumar.K
Semenov Alexand
Esteemed Contributor

Re: staffing profile load for period

Hi AII!
Anybody solve this problem?
Thanks
Erik Cole_1
Esteemed Contributor

Re: staffing profile load for period

Wow, seems like there are a lot of requests for this.

Here is some SQL that will 'expand' the compressed data and return a staffing profile position with the load broken down by date. You need to give it your STAFFING_PROFILE_ID and OWNER_ID (position_id).

FAIR WARNING: this is an as-is 'quick & dirty' solution, and it can take a while to run if you are doing it for an entire staffing profile or several at the same time to get a full view of resource demand.

You can wrap it in a function and/or use it as a subquery to group hours by week/month/whatever.



Select COMPRESSED_ENTRY_SEQ,REPEATS_COUNT,VALUE,
((rownum-1)+(select trunc(START_DATE) from RSC_STAFFING_PROFILES where STAFFING_PROFILE_ID = 30208)) as MYDATE
from
(
select * from
(
select ce.COMPRESSED_ENTRY_SEQ,ce.REPEATS_COUNT,ce.VALUE
from ITG_COMPRESSED_ELEMENTS ce
where ce.OWNER_TYPE = 'FORECAST'
and ce.OWNER_ID = 31079 -- position id
)
)
CONNECT BY PRIOR COMPRESSED_ENTRY_SEQ = COMPRESSED_ENTRY_SEQ
AND level < ABS(REPEATS_COUNT) +1
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL
Semenov Alexand
Esteemed Contributor

Re: staffing profile load for period

Erik,thanks.
Anybody run this query?
I can't-have loop user data error.
Don Seagraves
Super Collector

Re: staffing profile load for period

I get the same error:
SQL Error: ORA-01436: CONNECT BY loop in user data
01436. 00000 - "CONNECT BY loop in user data"

Not sure how to run the example provided.
Erik Cole_1
Esteemed Contributor

Re: staffing profile load for period

How strange...can you post exactly the code that is generating this? What PPM version are you?

I just ran this _exact_ sql in sqlplus, plsql Developer, and AquaData Studio and it ran fine in all three. PPM 7.5 SP2 Perhaps a data issue?
Don Seagraves
Super Collector

Re: staffing profile load for period

We are on 7.5 SP6.

See attached ouput from query...
Erik Cole_1
Esteemed Contributor

Re: staffing profile load for period

What Oracle version are you guys? I'm running this against 10.2.0.3
Don Seagraves
Super Collector

Re: staffing profile load for period

Oracle Database version 10.2.04 Standard Edition
Semenov Alexand
Esteemed Contributor

Re: staffing profile load for period

Hi All.
With your help and HP Support i have solution:

select v.owner_id position_id,v.position_name, round(sum(effort),2) plan_effort
from
(select t0.owner_id,t2.position_name, t0.compressed_entry_seq, t0.value * ( least((to_date('31.03.2010', 'dd.mm.yyyy') - trunc(t3.start_date)), sum(t1.repeats_count)) - greatest((to_date('01.03.2010','dd.mm.yyyy') - trunc(t3.start_date)), sum(t1.repeats_count) - t0.repeats_count)) effort
from itguser.itg_compressed_elements t0, itguser.itg_compressed_elements t1, itguser.rsc_positions t2, itguser.rsc_staffing_profiles t3
where t1.owner_type = 'FORECAST'
and t1.owner_id = t2.position_id
and t2.staffing_profile_id = t3.staffing_profile_id
and t3.staffing_profile_id = 30420
and t0.owner_type = t1.owner_type
and t0.owner_id = t1.owner_id
and t0.compressed_entry_seq >= t1.compressed_entry_seq
group by t0.owner_type,t2.position_name, t0.owner_id, t0.compressed_entry_seq, t0.repeats_count, t0.value, t3.start_date
having sum(t1.repeats_count) > (to_date('01.03.2010', 'dd.mm.yyyy') - trunc(t3.start_date))
and (sum(t1.repeats_count) - t0.repeats_count) < (to_date('31.03.2010','dd.mm.yyyy') - trunc(t3.start_date))) v
group by v.owner_id,v.position_name


Also i attach interest prsentation
Erik Cole_1
Esteemed Contributor

Re: staffing profile load for period

I like this - very fast. Good find!
//Add this to "OnDomLoad" event