How to Export Resource Assignment Data to Excel from P6

featimageexcel1

Export P6 Resource Assignments to Excel

As you know Primavera P6 has a lack of graphical features so often we schedulers need to export everything to other software packages (mostly Excel) to produce an understandable schedule and graphs. I always had a problem exporting resource related data to Excel with the standard exporting feature on P6 until I found  this way to use the Reports feature. Here you can use the current screen to develop a report and instead of making it a text report, we can have it produce an Excel report. This tutorial is good for anyone who wants to develop better resource curves and analyze resource assignments.

Step 1 – Select the Assignment tab

image001

When you opened the desired project, on the left hand side, you can choose different options. In this case, to see the assignments, you need to select the aAsignment tab

Step 2 – Resource Assignments page

image002

In this table, we have a resource assignments table on the left hand side and time-distributed data on the right. We can modify these tables in some different ways. I describe my way below:

Step 3 – Filter the resources

image003

Right-click on the left table and choose Filters.

image004

In the Filters screen write the following formula:
<where Budgeted Units/Time is greater than 0>. In this way you will have only the resources that have assignment not all the resources that has been defined.

Step 4 – Choose spreadsheet fields

image005

Right-click on the right hand table (spreadsheet) and from the menu choose Spreadsheet Fields and then Customize.

image006

In the open menu we have two categories of columns, Cumulative and Time interval. We’ll select Cum. Budgeted Units from Cumulative and Budgeted Units from the Time Interval – in this way we will have data for each month separately and cumulatively. You can select any kind of data for your report purposes. For this report we do not need any specific column on the left hand table. What we need is just the resource name but it is visible that you can add any specific column to that table whenever is needed according to your report requirements.

Now we have data to develop S-curves. Now we focus on exporting these data to Excel.

Step 5 – Run the Report Wizard

image007

Go to Tools menu and select Report Wizard.

Step 6 – Use current screen

image008

In the report wizard menu, select “use current screen” radio button and hit Next.

Step 7 – Use Time distributed data

image009
In this menu tick “ Time distributed option” and  below it select  “ Activity Resources and Role Assignment” and hit Next.

Step 8 – Finishing the Report

In the following screens, you can simply click Next.

image010

image011

On this screen you can give a name to your report.

image012

On this screen click “Run Report”.

Step 9 – Change the format of report

image013

Select the “ASCII Text File” radio button and make sure that the “View File when done” box is selected and click OK.

Now you have the data in an Excel file as the following screenshot:

image014

Wrap Up

The reason that we do all of these efforts to export the data to Excel is as follows:

  1. We do not have too much graphical features in P6 so we cannot develop perfect managerial reports in Primavera P6 alone.
  2. Resource curves in Primavera P6 do not show the percentage progress and monthly increments and this is not good when you look at a curve from a project manager’s perspective.
  3. If you want to calculate the manpower for each, in P6 you have to divide all man hours for every month to an exact number (for example 208) but as you know the working hours in each month is different because we have different for each month. Consequently we will not have a perfect estimation of manpower needed for each month in P6 while we can have an exact estimation in Excel.

In this way you can develop S-curves with the use of Excel easily.

Leave Us a Comment

What do you think about this method of extracting data to Excel? What has worked for you in the past? Let us know by leaving a comment below.









Comments

  1. janshar says

    Sir, it shows error when i put the zero or 8h/d for filtering(step-3). I have to restart primavera for further use. Please help

  2. says

    Converting to manpower from the hours have ONE challenge in that it does not cater for simultaneous jobs. Therefore by example if I have 2 jobs of 4 hours for an Instrument role, one starts at 8am and the other follows at 10am, I require 2 x Instrument Technicians. The outcome as per your example will produce a manpower requirement of 1 instead of 2. (8 hours in an 8 hour or 12 hour shift)

  3. says

    In your example you really do not need to export the resource information to excel. You can just highlight the data in P6 and copy and paste it into excel.
    All of the steps after step #4 are not needed.

  4. Billf says

    When trying to run the report at Step 9, I get an application event code
    VQASC-0071-E and am unable to run the report.

  5. steve says

    It didn’t produce an excel report in the format that you show. It just listed the activity Id, description, dates and units all along the same line.

    Any pointers?

    • Fareed says

      Sounds like there was only one incidence of the resource selected. Or maybe you selected the ‘only summary’ option?

  6. Hatem Ismail says

    Thanks a lot,
    I have done that many times before without using report wizard, only selecting first left topmost cell of the left pane on P6 resource assignment page ,pressing shift and selecting last left bottommost cell of the same pane then Ctrl+c and Ctrl+v to excel sheet, and it is done. It worked well all these times.

  7. Ann Collins says

    Kazem … life saver! I’ve been busting my head on how to do this.
    Question … what I’m more interested is exporting the cumulative budgeted costs as I’m trying to fudge a cashflow scurve.
    I’ve created a nonlabour resource and applied a total budgeted cost.
    Following your instructions, but where I come unstuck is the figures in the spreadsheet aren’t exportinging cleanly e.g. figure is splitting over 2 columns, or $20,000.00 comes out as 000.00$20.
    Any tips?

  8. zoltan palffy says

    an alternative to running the report wizard is to highlight the data then copy and paste into excel

  9. Ann Collins says

    Appreciate your reply Zoltan. Unfortunately the screen view won’t allow me to select the text,
    otherwise I would do exactly that, which has led me to investigate the report export option.

    • Fareed says

      You can actually use the ‘activity usage spreadsheet’ button (from the bar chart window/activity view instead of the resource assignment view). Just make the necessary changes to the layout at the bottom of the screen so it shows what you want and then copy and paste. Here you won’t have any problem copying.

    • zoltan palffy says

      ANN

      All you have to do is select the first cell and hold down the shift and the control key at the same time and select your month and the top row with the number in it. Then press control c open excel then press control V. I have done it a hundred of times.

  10. Ann Collins says

    I’ve worked it out. On the ‘Report Generated’ screen, prior to selecting ‘Run Report’, deselect the ‘Format Numbers’ box. The report can then be run and sent to ‘ASCII Text file’ without the comas or decimal points disrupting the formatting.

  11. Craig says

    I’m not sure what happened. One of my schedulers tried this and now when the get into the Resource Assignments icon all they get is a error “0.000000h/d is an invalid Unit of Measure” I’ve tried to hwelp but not getting anywhere. Any ideas?

  12. ZYman says

    Thank you. Can you publish similar article on “How to input actual resource information?” Let’s say if i update weekly.

Leave a Reply

Your email address will not be published. Required fields are marked *