1 Reply Latest reply: Nov 15, 2012 11:42 PM by gCubed RSS

    P6 capacity planning

    708631
      complete. thanks.
        • 1. Re: P6 web - capacity planning
          gCubed
          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