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
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
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
Right-click on the left table and choose Filters.
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
Right-click on the right hand table (spreadsheet) and from the menu choose Spreadsheet Fields and then Customize.
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
Go to Tools menu and select Report Wizard.
Step 6 – Use current screen
In the report wizard menu, select “use current screen” radio button and hit Next.
Step 7 – Use Time distributed data
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.
On this screen you can give a name to your report.
On this screen click “Run Report”.
Step 9 – Change the format of report
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:
The reason that we do all of these efforts to export the data to Excel is as follows:
- We do not have too much graphical features in P6 so we cannot develop perfect managerial reports in Primavera P6 alone.
- 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.
- 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.