The idea of this tutorial was inspired by a question in LinkedIn forum, a planner have received a primavera schedule using WBS, as in the oil and gas industry it is very useful to have different layouts to present the schedule to different audience, and more importantly the Work breakdown structure is based on geographic area in the construction phase, and systems in the commissioning phase, the tutorial will use the APEX sample schedule to convert a WBS to activities codes, it is assumed you
have already worked with the Primavera P6 SDK.
Download Tutorial Assets
Download the files to complete this tutorial on your own.
- Project Plan (.xer) : Apex xer.zip
- P6 SDK Excel Tool : Dictionary6v7.xls (Please note that this tool was created by Oracle Primavera and we do not provide support on its use)
- WBS to Activity Code xls : WBS-To-Activity-Code-P6.xls
Step 1 – Open the schedule.
The APEX schedule organizedby the default “classic WBS layout”.
Step 2 – Export WBS Dictionary to Excel
In P6, goto the WBS screen. Customize the columns so only WBS code, and WBS name are displayed.
Highlight all WBS rows, copy and paste the two columns to the worksheet “WBS dictionary” of the “WBS-to-Activity-Code-P6.xls” file (see Tutorial Assets). Make sure that your WBS structure is well organized (*). It means every level must have the same exact number of characters. In this regards P3 was more rigorous, as the structure of the WBS is defined first. In P6, it is generated automatically. It is very easy to edit the WBS Code, to have the same number of character for every level.
The excel formula in “column F” generates a unique list of the number of characters sort in ascending order, it is an array formula (that’s why there is curly braces between the formula, if not, use CTRL+SHIFT+ENTER to execute it).
In this example, WBS code with four characters -eg “APEX”, this is level 1. WBS Code with 7 characters – eg “APEX.AM”, this is level 2.
Of course, L1, L2 are just used as an example, in a real project, levels are usually defined as project, phase, sub-project, area, discipline, items, etc.
Step 3 – Export WBS Activity Assignment to Excel
Remove the Group and Sort by WBS, in columns add WBS and Activity ID.
Copy and paste the two columns to the excel worksheet “Activity codes per Activity” in columns A and B. Excel columns C through G will be generated automatically, by default, if no code is available, a ” -“ is assigned.
Step 4 – Create Activities Codes Dictionary and Import to P6
First define the new project activity codes in P6 that will represent the WBS.
Back in your excel file, goto the “activity code dictionary” worksheet. Using an excel pivot table, the excel file will generate an activities codes dictionary that we have to load back in P6 on this worksheet.
Using the dictionary excel utility (Dictionary6v7.xls – instructions on using this file can be obtained from http://support.oracle.com. Search for “dictionary6v7.xls”), click on “Get Activity Codes” (you need the SDK to be installed and configured).
Copy and paste the activities dictionary from the excel file, for every level add a dummy “-“ without a code description, so P6 will not show the ugly “no code” when you organize by activity code for activities without a code assigned.
Now you can import back to P6 the activities codes dictionary (click “Set Activity Codes” on the Login tab). Make sure you import to the right project.
Now your project is loaded with the activity codes dictionary.
Step 5 – Import activities codes assignment to P6
Make a new Excel export. Select the newly defined activities codes (L1-L5) as columns in your layout.
Open the exported excel file.
Use a Vlookup formula to retrieve the values of L1, L2, L3, L4, L5 from the excel sheet Activity codes per Activity. In the Excel file, there is sample vlookup sheet to show how the formula works.
Import the updated export file back to P6. The WBS is now transfered to the L1-L5 project activity codes.
Step 6 – Enjoy.
Now, with a new layout the schedule is organized by activity codes.
Primavera P6 has very strong activity codes capabilities. Unfortunately, many planners use only the WBS as it is very easy and intuitive, but once you start tailoring the schedule, printing for a different purpose and audience, in a way that make a sense to them, activity codes should be used as Primavera only permits one WBS per project.
(*) : if your WBS has not the same number of character for every level, you can use alternative method, explained here.