Filters Fail when Exporting from P6 to Excel

On 09.23.11, In Tutorials, by Michael Lepage

Tutorial Info

Program:
Version:
Difficulty:
Time to Complete:

Primavera’s custom Activity filters are great for focusing in on a subcontractor’s work list or even to hide Activities that are already completed. But when it comes to filtering down a 5000 line project plan to Activities in a specific timeframe, P6′s filters fall flat. Here’s the Gotcha: any filter that uses a rolling date or P5 / P6′s built-in date variables will not work when exporting P6 data to Excel.

What variables are you talking about?

P6 has the following 6 built-in date variables that can be used when building custom filters:

PS : Earliest Project Start
PF : Latest Project Finish
DD : Data Date
CD : the current date (according to Windows)
CW : the current week
CM : the current month

These variables come in real handy to create filters that display a rolling date range. With them, you can create the essential “Lookahead” layouts like “3 Week Lookahead” which displays only the Activities starting within the next 3 weeks.

Lookahead filters are essential for short-term planning. They let you focus on work that is coming up soon. And with large projects, they are essential.

It’s common for a project planner to send a subcontractor an excel spreadsheet with all of his activities coming up in the next 3 weeks. But since the rolling filters don’t work during an excel export, we’ll need a new approach.

Workaround

The workaround is pretty simple. Copy the filter to create a version specifically for use when exporting data to excel. Replace the date variables with specific date values in your filter for the current time period. Proceed with the export to excel.

Fixed Date Filter in P6

Although you’ll have to edit the dates in this filter over and over during the project , it’s much easier to filter Activities in P6 than it is to try to filter them in Excel.

Oracle has yet to resolve this P6 Gotcha, so make sure you drop them a line – “Dear Oracle….”

Michael Lepage

Michael is a Primavera P6 and Project Management expert working with companies large and small. He's also a trainer, instructional designer, and is pretty good at flying remote controlled helicopters. He writes tutorials because he loves to teach and is pretty keen on technology. He also holds a Project Management Professional cerfication from PMI.org.

Visit Authors Website | Read more posts by Michael Lepage

Create a P6 Project Dashboard in Excel

How To Link A P6 Schedule To The BOQ With Different Levels Of Detail

How To Remove Resource Costs from XERs in Primavera P6

15 Responses

M.V.DHEERENDRA RAO

10.11.2011

Your Comments
Dear Michael ,
I appreciate for sharing your knowledge and valuable information regarding Primavera P6 . I pray god that you continue doing the same way and enlighten the future generations .

Thanks & Regards,

Quick Tip: Zoom! P6 Activities to Excel in 8 clicks!

1.22.2012

[...] exporting data to Excel in general. So be sure your layout does not use a rolling date filters. Here’s a workaround to that [...]

Aftab

9.14.2012

Dear Michael-
A couple of quick Questions->
1. How do you show Actual Work on Bars as Performance % Complete and the rest of Bar as Remaining Work? That is, if the Activity is 60% Complete->the “Blue” Part of the Bar should show 60%, while the Remaining 40% should show Remaining Work-irrespective of where the Data Date Line falls in between the Actual Start Date and Forecast Finish.
2. I have tried Importing Budgeted Cost-Units from Excel to P6 but it does not work, Importing Budgeted Units works fine. I have unticked the Enterprise/Resources/Details/”Calculate Costs from Units” Box and even deleted the Price/Unit for the Resource-but the direct Import of Budgeted Cost-Units just does not work. The Only way I could do this was by creating a User Defined Field, inputting data into it in Excel, Importing the data from Excel to P6 and then doing a Global Change for Budgeted Cost-Units=User Defined Field. Is there a reason why the direct Import of data entered for Budgeted Cost-Units from Excel to P6 does not work-or are there some other settings I need to alter?

Thanks Very Much-

Aftab

9.14.2012

Dear Michael-

Is there any way to show Activity “Steps”-that is the “Real”/Actual Steps (& their Status) and not just Numbers (such as Steps Completed=1 etc.) in the Table or the Bar Chart Views-and also in Reports (corresponding with the Main Activities themselves)?

Thanks Very Much-

Aftab

9.14.2012

Dear Michael-

Also, is there any way to Export/Import-from P6 to Excel->The Activity Steps and related “Status”->corresponding with the Main Activities?

Thanks Very Much-

Aftab

Michael Lepage

9.14.2012

Hi Aftab,
Thanks for your interest in Plannertuts. Here are my best answers to these questions:

1) In Bars, configure the “Timescale” of the Actual Bar – set it to “Performance % Complete”. Then, ensure you have the Remain Bar on (separate line). That should do it. NOTE: The Remain Bar will display the remaining duration – not necessarily the remaining work.

2) Aftab, I don’t know of any field called “Budgeted Cost-Units” in P6. Is this a typo perhaps?
Warmest regards,
Michael

Michael Lepage

9.14.2012

Hi Aftab,
I’m afraid there is not any way to display steps in the table. The best alternative is to not use them, and replace your steps with activities – which is not really ideal in a lot of situations. They cannot be displayed on the Gantt chart either.
They can be printed in reports, underneath an activity.
Hope that helps,
Michael

Michael Lepage

9.14.2012

Aftab,
There is no easy way to do export and import steps. I can suggest creating a p6 report that reports on steps. On the print-preview screen, send the data to an ascii csv file. That’s the only way I know to export steps. I do not have any idea on how to import them.
Regards,
Michael

Aftab

10.13.2012

Thanks Very Much for your answers Michael.

As regards the question on Budgeted Costs above->the Budgeted Costs are those as related with Resource Units, as opposed to “Expense” Costs. When I enter Budgeted Costs as related to Units in the “Resource” Tab in Excel and try to Import them-it does not work; the only way it works is if I create a User Defined Field in P6->Export to Excel->Enter Budgeted Costs in the User Defined Filed->Import Excel file to P6->and do a Global Change to enter in the Budgeted Cost from the User Defined Filed. Seems that I should be able to directly enter Budgeted Cost in Excel and Import it directly-but it does not work, either with a Cost ID Designation (in line with Resource) or without a Cost ID Designation.

Appreciate all your help and answers.

Best Wishes-

Aftab

Michael Lepage

10.15.2012

Aftab,
I just did some testing and was able to update Budgeted Costs through excel. Some tips
1) Make sure you are updating the resource assignments. In excel this data is on the TASKRSRC tab.
2) When typing in values in Excel, P6 is very picky about data types else they won’t import. You have to put an apostrophe before the value, like so: ’3800 or ’12.50 . The apostrophe indicates that this is a Text field, which is what P6 is looking for.

Let me know if that helps you,
Michael

Aftab

10.18.2012

Thanks Michael.

I did try and put an apostrophe before the numeric values for Budgeted Cost in the TASKRSRC Tab (for Resource Assignments)-but it did not work. Could you please elaborate on how you introduced/copiedapostrophes for a Range of Cells in the Budgeted Cost Column before numeric values are entered (in the required manner) and then entered numeric values for Budgeted Costs so that Import of Budgeted Cost is successful? Did you also have to input Resource IDs and Cost Account IDs, in addition to the existing Activity ID Identifiers for the Import of Budgeted Costs to occur as needed? I also tried marking the “Budgeted Cost” Cells/Column as “Text” but this did not work either.

Best Wishes-

Aftab

Michael Lepage

10.20.2012

Aftab,
I want you to look at ASAP Utilities, it’s an excel plug-in that offers many features. In the text features, there is one that allows you to insert characters before text in a range of cells. Use it to insert the apostrophe. I’ll do a post on this soon.
Michael

Aftab

10.24.2012

Thanks Very Much Michael-

Another Basic Question->

Why are the following 2 different and calculated differently?->

1. “At Completion Labor Units”-Calculated based on “Remaining Duration” (in Days) x Units/Day. + Actual Units to Date.

COMPARED TO->

2. “Estimate At Completion Labor Units”-Calculated as “Budgeted Units ‘Minus’ Earned Units” + Actual Units to Date.

Seems there are 2 Sets of Rules to calculate Estimate at Completion->
The First based on “Forecast Remaining Duration” and the Other based on Units->“Budget” and “Earned Value”.

Thanks Again & Best Wishes-

Aftab

Michael Lepage

10.26.2012

Hi Aftab,

Estimate At Completion Labor Units is an earned value field calculated as “Actual Labor Units + Estimate to Complete Labor Units”. There are options for calculating ETC in different ways in P6 – these provide the choice of a more optimistic calculation all the way to a pessimistic calculation. These ETC options on the WBS screen, on the Earned Value tab.

The At Completion Labor Units field gives you a similar result which is based on Actual Units and Remaining Units.

The Earned Value fields are usually used if you are tracking Earned Value Cost..and then as I said you can tweak these calculations for a more optimistic or pessimistic result.

I hope that helps sort this out a bit.
Warmest regards. M

Aftab

10.28.2012

Thanks Very Much Michael.

However, the P6 calculation for “Estimate At Completion Labor Units” and “At Completion Labor Units” gives you different results, which are not similar->due to the way they are calculated as described above and below. Is it just a way to show results using alternative methods of calculation-as both of these methods for calculating EAC have due significance-one based on Budget constraints and the Other on “true” Forecast?

1. “At Completion Labor Units”-Calculated based on “Remaining Duration” (in Days) x Units/Day. + Actual Units to Date.

2. “Estimate At Completion Labor Units”-Calculated as “Budgeted Units ‘Minus’ Earned Units” + Actual Units to Date.

Thanks Again & Best Wishes-

Aftab