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
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.
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
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 an export of the P6 resource assignment data in an Excel file as shown in the following screenshot:
Wrap Up
The reason that we do all of these efforts to export P6 resource assignment 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.
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.
So Many Thanks for your good Thinkness
Sincerely
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
Thanks , very useful
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)
thanks alot buddy. hghly helpful. 🙂
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.
P6 keep throwing error and hangs up as soon as I enter in the filter where budgeted units/time is greater than 0. Please help.
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.
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?
Sounds like there was only one incidence of the resource selected. Or maybe you selected the ‘only summary’ option?
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.
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?
an alternative to running the report wizard is to highlight the data then copy and paste into excel
Thanks so much!!! I didn’t have much luck running the report wizard, but this worked.
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.
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.
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.
And since there’s no one way for it, you can chose whichever you like whenever…!!!
I know this is way later than your comment but I use the Select All command under Edit, sometimes I have to do the Select All twice but then just past in Excel and done.
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.
Great tip. Thanks very much.
Great, thanks.
Thank you for information, it’s very helpful
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?
It will be very helpful.
Thank you. Can you publish similar article on “How to input actual resource information?” Let’s say if i update weekly.
Good tutorial.. What if we use the ‘activities’ instead of ‘activities resource and role assignments’? Are we still able to get the manhour data from it?
Hi,
Could anybody help me with resource importing from excel to primavera P6??
If resource wizard is created in p6 & exported to excel, then the data will import. But it doesn’t import when additional data is inserted in excel.
Thanks
Hi Jijo.
Have you got any idea to import the resource spreadsheet with changes.?? If so, pls share. It would be a great help.
Dear Sir,
I exported my resources which is my BOQ value as the budgeted cost. But now I want to make import this after making changes in the amount . I could not import this. Can you guide me to make this.
Thanks
Syed
Hi, I found this very useful except when I exported into excel some of the formatting was off and I had to spend some time fixing the format so everything was in the right place for me to then run a resource curve. Is there a limit on the number of characters in an Activity description that will be exported before carrying over to the next cell?
Thanks
Emma
No. I think you want to turn on cell Wrapping in Excel to see all the activity description in the same cell with no truncation. Try that.
No Need to such a big Procedure for transferring resource assignment in Excel,
>> From table of P6, copy required and paste it to Excel file…
So simple….along with distribution its related all information like activity details, dates will come automatically.
Enjoy P6
Tell us steps to understand
ill try that, thank you very much!
I would like to ask.Is the filter defined in this tutorial is enough to ensure that resources displayed are only assigned to the open project and not to other projects?
It’s not the filter, but the Resource Assignments screen only displays resources that are assigned to the currently opened project.
Thanks, found it useful
Hi there – how can I display “Budget Units / Time” instead of displaying “Budget Units”? I want to generate a ‘head count’ report, instead of having a Men-hours report.
Thank you
Can you please explain this topic in with video please
Dear sir,
what is the quickest method to estimate and assignment materials and manpower resources ? by another meaning i need a simple data structure to be easy re-exported from excel to P6
Thanks so much
I tried those steps in my tool, it was uploaded but some of the activities, especially the budget estimation had completely collapsed & it is in different value
Is there is any other way to get it clear???
i got some help from this site, but i need clear explanation
Pretty decent workaround when you’re unable to copy+paste to Excel (i.e. P6 via Citrix)
It’s still important to double check the data though, as generating CSV reports in P6 can sometimes have formatting issues.
Hi, is there a way to present the information instead of monthly, lets say weekly?
Got it, you can adjust the Timescale in Date options. Thanks
Or just click an activity hit ctrlA open excel hit ctrlV, same thing really but much quicker
Your web site appears to be having some compatibilty problems in my chrome browser. The text seems to be running off the webpage pretty bad. If you want you can contact me at: %EMAIL% and I’ll shoot you over a screen shot of the problem.
adidas stan smith 1 http://www.frwebs.fr/adidas%20stan%20smith%201-ID11890.html
After paste to Excel, the units include the alpha character d (units/time period = day). e.g.; the cells in Excel contain “2.0d” instead of “2.0”. I just want the numeric values copied to Excel without the alpha characters. The “d” does not show the P6 resource assignments window. I’m using Citrix in case that matters.
Wonderful Post!!! Thanks for sharing this great post with us…
I really enjoyed this article. I need more information to learn so kindly update it.
Thank You for this !!!
Most of us will admit we have put a lot of time and effort into solving this, but thanks to Plan Academy ( Again ! and Again !!!…….) and All the hard working people behind this company. We appreciate everything ! This is why we use Plan Academy as one of our most visited resources for P6.
You’re very welcome!
Hi Jijo.
Have you got any idea to import the resource spreadsheet with changes.?? If so, pls share. It would be a great help.
Thanks in advance.
On the resource assignment sheet is their a possibility we could spread Earned units like Budgeted & Actual Units. !
Great solution. You made S curves easy. Thanks
thanks John!
Hi Michael, the method really works and very easy. Thank you. However, talking about the resource summary, when i changed the units to mandays and have the spreadsheet options set to calculated average, the P6 table shows the required average mandays per month. But when the report is run, the resulting values are still in manhours. How do we change that? Thank you.
Bobby
In that case, you’ll have to change some settings in the Wizard. On Step 8, you can click the “Timescale” button and choose “Months”, and you can set the averaging in there as well.