This discussion is archived
1 Reply Latest reply: Nov 16, 2012 5:42 AM by gCubed RSS

P6 capacity planning

708631 Newbie
Currently Being Moderated
complete. thanks.
  • 1. Re: P6 web - capacity planning
    gCubed Journeyer
    Currently Being Moderated
    The capacity planning view pulls a number of record sets (projects in a portfolio, project types, roles in a portfolio, role spread data, and role calendars) that are used to display the capacity planning grid but depending on the user selection it applies processing logic (i.e. summing by timeline) before rendering the UI view.

    I played around with this a little and here is a couple of SQL that might help point you to an answer (Just a guess on my part, hopefully it will point you in the right direction).
    On a side note I think using the P6 web services SpreadService.ReadProjectRoleSpread would be easier and less messy!

    I believe that you need a couple SQL queries to get the record sets that are needed to get the information the capacity planning view uses.

    1. Projects within a given Portfolio
    2. Roles on the projects within a given portfolio
    3. Spread data for each project by role
    4. Calendar data for each role (play time over so I didn't look into this dimension)


    Projects within a given Portfolio:

    DECLARE @PortfolioName VARCHAR(255)
    SET @PortfolioName = 'Key Projects over $500K'

    SELECT pw.WBS_ID, pc.PROJ_ID, pw.WBS_NAME, pc.PROJ_CATG_ID, pv.PROJ_CATG_NAME
    FROM PCATVAL pv
    INNER JOIN PROJPCAT pc ON pc.PROJ_CATG_ID = pv.PROJ_CATG_ID
    INNER JOIN PCATTYPE pt ON pt.PROJ_CATG_TYPE_ID = pc.PROJ_CATG_TYPE_ID
    INNER JOIN PROJWBS pw ON pw.PROJ_ID = pc.PROJ_ID
    INNER JOIN PRPFOLIO pr ON pr.WBS_ID = pw.WBS_ID
    INNER JOIN PFOLIO pf ON pf.PFOLIO_ID = pr.PFOLIO_ID
    WHERE pt.PROJ_CATG_TYPE = 'Project Type'
    AND pf.PFOLIO_NAME = @PortfolioName
    ORDER BY pv.PROJ_CATG_NAME, pw.WBS_NAME

    Roles on the projects within a given portfolio

    DECLARE @PortfolioName VARCHAR(255)
    SET @PortfolioName = 'Key Projects over $500K'

    SELECT sr.SUMTRSRC_ID, sr.PROJ_ID, sr.WBS_ID,sr.ROLE_ID, pw.WBS_NAME, rs.ROLE_NAME
    FROM SUMTRSRC sr
    INNER JOIN projwbs pw ON sr.PROJ_ID = pw.PROJ_ID AND pw.PROJ_NODE_FLAG='Y'
    INNER JOIN PRPFOLIO pr ON pr.WBS_ID = pw.WBS_ID
    INNER JOIN PFOLIO pf ON pf.PFOLIO_ID = pr.PFOLIO_ID
    LEFT OUTER JOIN PROJECT pj ON sr.PROJ_ID = pj.PROJ_ID
    LEFT OUTER JOIN PROJWBS pw1 ON sr.wbs_id = pw1.WBS_ID
    LEFT OUTER JOIN ROLES rs ON sr.ROLE_ID = rs.ROLE_ID
    WHERE sr.SPREAD_TYPE IS NULL
    AND pf.PFOLIO_NAME = @PortfolioName
    AND sr.RSRC_ID IS NULL
    AND pw1.PROJ_NODE_FLAG = 'Y'
    ORDER BY pw.WBS_NAME


    Spread data for each project by role

    DECLARE @PortfolioName VARCHAR(255)
    SET @PortfolioName = 'Key Projects over $500K'

    SELECT sr.SUMTRSRC_ID,sr.PROJ_ID,sr.WBS_ID, sr.ROLE_ID, sr.START_DATE, sr.END_DATE,
    sr.SPREAD_TYPE, sr.STAFFED_TOTAL_QTY, sr.UNSTAFFED_TOTAL_QTY, sr.STAFFED_REMAIN_QTY, sr.UNSTAFFED_REMAIN_QTY
    FROM SUMTRSRC sr
    INNER JOIN PRPFOLIO pr ON pr.WBS_ID = sr.WBS_ID
    INNER JOIN PFOLIO pf ON pf.PFOLIO_ID = pr.PFOLIO_ID
    WHERE sr.SPREAD_TYPE <> 'FinancialPeriod'
    AND pf.PFOLIO_NAME = @PortfolioName
    AND sr.SPREAD_TYPE IS NOT NULL
    AND sr.RSRC_ID IS NULL
    AND sr.PROJ_ID IS NOT NULL

    Again, I didn't spend anytime to check to see if I got all the right records and I really think the using the SpreadService.ReadProjectRoleSpread would more better approach to getting the information you desire but it was fun looking for a solution.

    Gene

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points