Project and Portfolio Management Practitioners Forum
cancel

Staffing profile export or query

Highlighted
applecow
Contributor.

Staffing profile export or query

Hi,

 

New to PPMC and I'm temporarily stuck with having to run some reports, I'm hoping someone can help me out a bit. 

 

Our current staffing profiles have forecast data (hours), resource name, position as well as a few other fields. I'm looking to generate an export that includes all the fields on the staffing profile, but the default export only gives me part of what I need. 

 

I tried to create a query and while it works for majority of the resources, I run into issues of data not coming through when a resource is either

 

1) has not been soft-booked / committed

2) a placeholder has been created in the staffing profile (with hours) but the resource name is blank

 

Also, a particular forecast request can be fulfilled by multiple resources (dividing the hours forecasted). Is it possible for the report to encompass this?

 

I'm intermediate at best when it comes to SQL and running BO queries, so I will appreciate any guidance you guys can give me. I can clarify if anything is unclear. 

3 REPLIES
d4y4n4
Honored Contributor.

Re: Staffing profile export or query

Hi,

You can check the data model guide http://support.openview.hp.com/selfsolve/document/KM1014814 to find if something is missing in your SQL

Please share the SQL you are using to look at it

Regards,
Dayana Campos
applecow
Contributor.

Re: Staffing profile export or query

 

SELECT
  RPT_DIM_REQUESTS.REQUEST_ID,
  RPT_DIM_PROJECTS_MV.PROJECT_NAME,
  RPT_DIM_RM_RESOURCES.USER_NAME,
  decode(( RPT_DIM_RM_RESOURCES.LAST_NAME ), null, 'Not Assigned', ( RPT_DIM_RM_RESOURCES.FIRST_NAME ) ||  '  ' ||( RPT_DIM_RM_RESOURCES.LAST_NAME )),
  RPT_DIM_RM_POSITIONS.POSITION_NAME,
  RPT_DIM_RM_ROLES.ROLE_NAME,
  RPT_DIM_RM_RESOURCES.RESOURCE_CATEGORY,
  RPT_DIM_RM_STAFF_PROFILES.CONTAINER_TYPE,
  RPT_DIM_RM_RESOURCEPOOLS.RESOURCE_POOL_NAME,
  RPT_DIM_RM_RESOURCEPOOLMGRS.RESOURCEPOOL_MANAGER_NAME,
  RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH,
  round(SUM(( CASE ( @Prompt('Time Granularity:','A',{'Year','Half Year','Quarter','Month','Week'},MONO,CONSTRAINED,{'Month'}) )
WHEN 'Week' THEN (case RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_TYPE when  8 then RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_HOURS end)
WHEN 'Month' THEN (case RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_TYPE when  4 then RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_HOURS end)
WHEN 'Quarter' THEN (case RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_TYPE when  5 then RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_HOURS end)
WHEN 'Half Year' THEN (case RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_TYPE when  7 then RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_HOURS end)
WHEN 'Year' THEN (case RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_TYPE when  6  then RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_HOURS end)
ELSE (case RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_TYPE when  4  then RPT_FCT_RM_RSC_DEMAND_AGGR.PERIOD_HOURS end)
END ) ) ,3)
FROM
  RPT_DIM_RM_STAFF_PROFILES,
  RPT_DIM_REQUESTS,
  RPT_DIM_PROJECTS_MV,
  RPT_DIM_RM_RESOURCES,
  RPT_DIM_RM_POSITIONS,
  RPT_DIM_RM_ROLES,
  RPT_DIM_RM_RESOURCEPOOLS,
  RPT_DIM_RM_RESOURCEPOOLMGRS,
  RPT_DIM_PPM_FISCAL_PERIODS,
  RPT_FCT_RM_RSC_DEMAND_AGGR
WHERE
  ( RPT_DIM_RM_STAFF_PROFILES.STAFFING_PROFILE_ID=RPT_DIM_RM_POSITIONS.STAFFING_PROFILE_ID(+)  )
  AND  ( RPT_DIM_RM_RESOURCEPOOLS.RESOURCE_POOL_ID=RPT_DIM_RM_RESOURCEPOOLMGRS.RESOURCE_POOL_ID(+)  )
  AND  ( RPT_DIM_RM_STAFF_PROFILES.PFM_ENTITY_CONTAINER_ID=RPT_DIM_REQUESTS.SOURCE_ENTITY_ID(+)  )
  AND  ( RPT_DIM_PROJECTS_MV.REQUEST_ID(+)=RPT_DIM_REQUESTS.REQUEST_ID  )
  AND  ( RPT_DIM_RM_RESOURCEPOOLS.RESOURCE_POOL_ID(+)=RPT_FCT_RM_RSC_DEMAND_AGGR.RESOURCE_POOL_ID  )
  AND  ( RPT_DIM_RM_ROLES.ROLE_ID=RPT_FCT_RM_RSC_DEMAND_AGGR.ROLE_ID  )
  AND  ( RPT_FCT_RM_RSC_DEMAND_AGGR.RESOURCE_ID=RPT_DIM_RM_RESOURCES.RESOURCE_ID(+)  )
  AND  ( RPT_FCT_RM_RSC_DEMAND_AGGR.POSITION_ID=RPT_DIM_RM_POSITIONS.POSITION_ID  )
  AND  ( RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY=RPT_FCT_RM_RSC_DEMAND_AGGR.START_DATE(+)  )
  AND  ( RPT_DIM_RM_STAFF_PROFILES.STATUS_CODE <> 4  )
  AND  
  (
   ( RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY  BETWEEN
--Start Date logic
(CASE ( @Prompt('Time Granularity:','A',{'Year','Half Year','Quarter','Month','Week'},MONO,CONSTRAINED,{'Month'}) )
 WHEN  'Week' THEN 
 (select min(RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY) 
  from RPT_DIM_PPM_FISCAL_PERIODS 
  where PERIOD_ID_WEEK=
  (select PERIOD_ID_WEEK
   FROM RPT_DIM_PPM_FISCAL_PERIODS 
   where DATE_OF_THE_DAY =
   (select min(RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY) 
    from RPT_DIM_PPM_FISCAL_PERIODS 
    where ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('Begin Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
   )
  )
 )
 ELSE (select min(RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY) 
     from RPT_DIM_PPM_FISCAL_PERIODS 
     where ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('Begin Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
    )
 END) 
AND 
(WITH tmpTable AS (select /*+ materialize*/ count(*) as tmpValue
 from RPT_DIM_PPM_FISCAL_PERIODS
 where PERIOD_ID_WEEK= 
    (select PERIOD_ID_WEEK
     from RPT_DIM_PPM_FISCAL_PERIODS
     where DATE_OF_THE_DAY =(
          select max(DATE_OF_THE_DAY)
          from RPT_DIM_PPM_FISCAL_PERIODS
          where ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('End Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
          )
    )
  and ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('End Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
)  
select  
(  CASE  ( @Prompt('Time Granularity:','A',{'Year','Half Year','Quarter','Month','Week'},MONO,CONSTRAINED,{'Month'}) )
     WHEN  'Week' THEN 
        CASE WHEN
        (select tmpValue from tmpTable)  < 7 THEN
            (select max(RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY) 
                 from RPT_DIM_PPM_FISCAL_PERIODS 
                 where ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('End Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
            ) - (select tmpValue from tmpTable)
        ELSE
            (select max(RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY) 
             from RPT_DIM_PPM_FISCAL_PERIODS 
             where ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('End Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
            )   
        END
    ELSE
        (select max(RPT_DIM_PPM_FISCAL_PERIODS.DATE_OF_THE_DAY) 
             from RPT_DIM_PPM_FISCAL_PERIODS 
             where ( RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH=
        @Prompt('End Period:', 'A',
                             'Fiscal Periods\Available Periods',MONO,CONSTRAINED) )
        )
    END) as end_date
 from dual
 )  )
   AND
   RPT_DIM_RM_STAFF_PROFILES.CONTAINER_TYPE  IN  @prompt('Enter value(s) for Staffing Profile Type:','A','Staffing Profiles\Staffing Profile Type',Multi,Free,Persistent,,User:0)
   AND
   RPT_DIM_REQUESTS.REQUEST_ID  IN  @prompt('Enter value(s) for Request Id:','N','Request Information\Request Id',Multi,Free,Persistent,,User:1)
  )
GROUP BY
  RPT_DIM_REQUESTS.REQUEST_ID, 
  RPT_DIM_PROJECTS_MV.PROJECT_NAME, 
  RPT_DIM_RM_RESOURCES.USER_NAME, 
  decode(( RPT_DIM_RM_RESOURCES.LAST_NAME ), null, 'Not Assigned', ( RPT_DIM_RM_RESOURCES.FIRST_NAME ) ||  '  ' ||( RPT_DIM_RM_RESOURCES.LAST_NAME )), 
  RPT_DIM_RM_POSITIONS.POSITION_NAME, 
  RPT_DIM_RM_ROLES.ROLE_NAME, 
  RPT_DIM_RM_RESOURCES.RESOURCE_CATEGORY, 
  RPT_DIM_RM_STAFF_PROFILES.CONTAINER_TYPE, 
  RPT_DIM_RM_RESOURCEPOOLS.RESOURCE_POOL_NAME, 
  RPT_DIM_RM_RESOURCEPOOLMGRS.RESOURCEPOOL_MANAGER_NAME, 
  RPT_DIM_PPM_FISCAL_PERIODS.PERIOD_NAME_MONTH

 

Thanks for the quick response!

 

The issue that I am running into is if a resource manager has not assigned a resource to a forecast (even if we have a resource tied to the forecast and the Position Name field is filled out on the SP), it doesn't show up in the report. It only shows when a resource gets assigned. Similarly, UserName is blank and Resource Name shows 'Not Assigned'

 

I'm running some forecast metrics off that Position Name field. Thanks for your help. Taking a look at the modeling guide now as well.

applecow
Contributor.

Re: Staffing profile export or query

Sorry, not Position Name.

 

Resource Category isn't showing up