One Trick to Export P6 Resource Assignment Data 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 when I export P6 resource assignment data to Excel with the standard exporting feature on P6 until I found a way to leverage 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.

So let’s go it.

Export P6 Resource Assignments To Excel With The Help of Reports

Step 1 – Select the Assignment tab

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

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

Step 3 - Filter the resources

Right-click on the left table and choose Filters.

Step 3.1 - Filter the resources

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

Step 4 - Choose spreadsheet fields

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

Step 4.1- Choose spreadsheet fields

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.

So here’s where the trick part starts. Rather than use P6 export to Excel featuer, we will export P6 resource assignment data with the help of P6’s internal Reporting system.

Step 5 – Run the Report Wizard

Step 5 - Run the Report Wizard

Go to Tools menu and select Report Wizard.

Step 6 – Use current screen

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

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.

Step 8 - Finishing the Report

Step 8.1 - Finishing the Report

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

Step 8.2 - Finishing the Report

On this screen click “Run Report”.

Step 9 – Change the format of 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 an export of the P6 resource assignment data in an Excel file as shown in the following screenshot:

open p6 resource assignment export in excel

Wrap Up

The reason that we do all of these efforts to export P6 resource assignment 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 export P6 resource assignment data to Excel?

What has worked for you in the past?

Let us know by leaving a comment below.

Author:
I started working as a scheduler 4 years ago. I have been working for different companies in different industries since then. I am writing tutorials because I believe knowledge and experience should be shared.I do like watching historical documentaries and reading novels.