9 Replies Latest reply: Feb 26, 2013 7:27 AM by 708631 RSS

    Report help / question

    708631
      I want to pull a specific report. either from the p6 tool reporting or directly from the database.
      Using Primavera p6 version 7.

      All the Role names and capacity / demand time for each role over a timespan where the role is assigned to an activity, and the activity project, grouped by project.
      Can someone help ?

      thanks for any help
        • 1. Re: Report help / question
          ama
          Hello,

          You should be able to create a report using the Report Wizard. In the Select Additional Subject Areas section (screen) of the wizard, choose Activity Resource and Role Assignments. In the next section (screen) of the Report Wizard, Configure Selected Subject Areas, click on Activity Resource and Role Assignments, and then click on Columns, and choose the durations that you need. Then it is simply a matter of configuring and formatting the report to suit your needs.

          Hope this helps!
          • 2. Re: Report help / question
            gCubed
            From the database, I think it is something like this.

            SELECT pj.proj_short_name, pw.wbs_name, rs.role_name , --rl.max_qty_per_hr, rl.max_qty_per_hr * 40,
            SUM(
            CASE
            WHEN sr.staffed_target_qty = 0.0 THEN unstaffed_target_qty
            ELSE sr.staffed_target_qty
            END) AS demand_hours,
            MIN(sr.start_date) AS start_date,
            MAX(sr.end_date) AS end_date
            from dbo.SUMTRSRC sr
            INNER JOIN ROLES rs ON sr.role_id = rs.role_id
            INNER JOIN ROLELIMIT rl ON rl.role_id = sr.role_id
            INNER JOIN PROJWBS pw ON pw.proj_id = sr.proj_id
            INNER JOIN PROJECT pj ON pj.proj_id = pw.proj_id
            WHERE sr.start_date > = '2010-08-01'
            AND sr.end_date <= '2010-12-31'
            AND sr.spread_type = 'Week'
            AND pw.proj_node_flag = 'Y'
            GROUP BY pj.proj_short_name, pw.wbs_name, rs.role_name
            ORDER BY pj.proj_short_name


            Not sure if it completely correct but it should point you in a direction if you can't get the information via the report's wizard.

            Gene
            • 3. Re: Report help / question
              708631
              thank you. but I get an error and do not see this table

              SUMTRSRC

              are you sure this is in V7 ?



              SQL Server Database Error: Invalid object name 'SUMTRSRC'.
              • 4. Re: Report help / question
                gCubed
                Sorry about that it wasn't a V7 database.

                I just spun up a V7 and it appears the fields you need are in a table called taskrsrc.

                In it you will find the role_id, target_qty.

                Gene
                • 5. Re: Report help / question
                  708631
                  I have been trying to get the query you gave to work with v7 and that table, but not successful yet. can you get it working with the taskrsrc table ?
                  • 6. Re: Report help / question
                    gCubed
                    I check TRSRCSUM and it appears that the spread data is saved in a field called spread_data. It appears to be some type of pipe delimited data.

                    At this point if I had this problem, I would use the spread services -- ReadPorjectRoleSpread operation via either the integration or web services to get role spread information.

                    To use the raw information in the TRSRCSUM, you will need to figure out how to the spread the total_qty based on your selected window of dates against each of the assignments for activities that falls within you requested window.

                    I took a crack at a simple spread based on calculating a multiplier for the target_qty base on the number of days within a given requested selection window for each of the assignments that fall within the window.

                    Best guess, not sure it is completely correct and not pretty (the target window is in year 2009 because my P6V7 database is an old PUBDB demo database).

                    So in this example, I am looking for a 19 day window for roles on activities that fall in that window and then using the calculated multiplier to scale the total_qty base for the given window.

                    Again not sure if this is completely correct and I would recommend using the API spread service which will give you the correct answer but it was an interesting problem to look at.



                    DECLARE @startDate DATETIME
                    DECLARE @endDate DATETIME

                    SELECT @startDate = '2009-04-05 08:00:00.000', @endDate = '2009-04-24 16:00:00.000'


                    SELECT pj.proj_short_name, rl.role_name, tr.proj_id, tr.role_id, @startDate as start_date, @endDate as end_date,
                    tr.target_start_date, tr.target_end_date,
                    CASE WHEN tr.target_start_date > @startDate THEN tr.target_start_date ELSE @startDate END as window_start_date,
                    CASE WHEN tr.target_end_date < @endDate THEN tr.target_end_date ELSE @endDate END as window_end_date,
                    DATEDIFF(HH,tr.target_start_date, tr.target_end_date) AS target_days,
                    DATEDIFF(HH,
                    CASE WHEN tr.target_start_date > @startDate THEN tr.target_start_date ELSE @startDate END,
                    CASE WHEN tr.target_end_date < @endDate THEN tr.target_end_date ELSE @endDate END
                    ) AS target_window,
                    tr.target_qty, tr.target_qty / DATEDIFF(HH,target_start_date, target_end_date) AS target_qty_day ,
                    CASE
                    WHEN DATEDIFF(HH,
                    CASE WHEN tr.target_start_date > @startDate THEN tr.target_start_date ELSE @startDate END,
                    CASE WHEN tr.target_end_date < @endDate THEN tr.target_end_date ELSE @endDate END
                    ) > DATEDIFF(HH,target_start_date, target_end_date) THEN 1.0
                    ELSE
                    CAST(DATEDIFF(HH,
                    CASE WHEN tr.target_start_date > @startDate THEN tr.target_start_date ELSE @startDate END,
                    CASE WHEN tr.target_end_date < @endDate THEN tr.target_end_date ELSE @endDate END
                    ) AS FLOAT) / Cast(DATEDIFF(HH,target_start_date, target_end_date) AS FLOAT)
                    END AS window_multipler,
                    CASE
                    WHEN DATEDIFF(HH,@startDate, @endDate) > DATEDIFF(HH,target_start_date, target_end_date) THEN 1.0
                    ELSE CAST(DATEDIFF(HH,@startDate, @endDate) as Float) / CAST(DATEDIFF(HH,target_start_date, target_end_date) as FLOAT)
                    END * target_qty AS simple_spread
                    FROM dbo.TASKRSRC tr
                    INNER JOIN dbo.ROLES rl ON rl.role_id = tr.role_id
                    INNER JOIN PROJECT pj ON pj.proj_id = tr.proj_id
                    WHERE tr.role_id IS NOT NULL
                    AND tr.target_end_date >= @startDate
                    AND tr.target_start_date <= @endDate
                    AND DATEDIFF(HH,tr.target_start_date, tr.target_end_date) > 0
                    • 7. Re: Report help / question
                      708631
                      thank you so much , this is actually working great so far.

                      Here is last question,
                      how can I show the assigned time per role with this per month/year.

                      So when I look in p6 in a projects activities, I can see the role, then the assigned percentage of time per month.
                      • 8. Re: Report help / question
                        gCubed
                        See if target_qty_per_hr is the field you need.


                        Gene
                        • 9. Re: Report help / question
                          708631
                          thanks, this does look like the field, but I have not been able to break it up into months / quarters per year.