Tutorial-How to generate S curve/histogram from Primavera, P6 Part I The following steps should be done before S curve is generated. 1. 2. 3. 4. 5. 6.
Establish WBS Adding activities, Activity codes, Activity sequencing Define resource dictionary Assign activity resource and budgeted man hour Save as a baseline schedule Record the actual status when the actual works are done and schedule it based on report date
Plan data can be produced if the steps 1 to 5 are done. The Actual data can also be exported if all the steps are done. Suppose you have already done all these steps as we are learning how to generate S-curve/ Histogram chart from P6 in this post. There is more than one method to generate the time-based S curve and histogram chart from P6. The related posts you might be interested are How to Get Resource Loading Histogram/S Curve in Primavera P6 and Creating discipline wise manpower histogram using Primavera P6 export data. This post demonstrates how these curves are produced by using Excel export data from Primavera, P6. Use the following procedures;
Open the Assignment window Bring out the columns that you want to export to excel Group/Organize the column data Set the Timescale format Export to Excel
Open the "Assignment" window You must be at resource assignment window when you are preparing to export S curve/histogram data to excel. To view "Assignment" window 1. 2.
Open the selected project On the menu bar, go to View >> Toolbars >> Directory to see “Assignments” tag
3. Click on Assignments tap, then you will see “Resource Assignments” window
Bring out the Columns that you want to export to excel The first thing you need to find out yourself is which columns are required to be reported and how do you want to analyze the data after exported. Suppose you want to view Overall Budgeted Units and Actual Units that can be used to analyze overall progress. And you might need to look at spreadsheet information such as Cum Budged Units and Actual Units in order to analyze periodic data (i.e weekly budgeted unit, weekly actual units, weekly manpower, etc). That means Cum Budgeted Units and Actual Units from the Spreadsheet Fields are also displayed to calculate weekly or monthly progress S curve/histogram data. To View Overall Budgeted Units and Actual Units Columns 1. Go to View on the menu bar >> Columns >> Customize. 2. And then you will see the Columns window. In the selected options box, Activity ID, Activity Name, Resource ID Name, Start and Finish columns are shown by default.
3. Select Actual Units and Budgeted Units from units group in the available options box, then click the forward arrow diagram to show actual units and budgeted units as shown in figure below.
4. After Actual and Budgeted Units are selected, hide Start and Finish by clicking backward arrow if you don't want to see it. And Click OK Then resource assignments window can be viewed as shown in figure below.
LinkWithin
How to Import and Export Excel data Activities To/From P6 The import export feature using spreadsheet (XLS) tool can be utilized for the following purposes but are not limited to; Assign/change the activity resources to activities Assign new activity names and modify the existing ones Update physical progress via user define fields to activities Assign/modify user define fields to activities Import activity codes to activities
Assign activity code via excel using Import/Export Wizard To modify or add to the exported sheet containing activity names, WBS code, activity codes and user define fields, and import the data into P6, 1) Go to File > Export > Select Spreadsheet-(XLS) > Next > Select Activities > Next > mark on export check box > Next > Add > Modify Template 2) Customize the desired columns. For example: To assign activity code ―Discipline‖ into activities, select ―Discipline‖ column into selected options box. Name the template so that you can find it back when you need. See figure below.
3) After setting up the desired template, Go to Next > Select the Excel file location > Next > Finish. Finally, you will get the excel spreadsheet data which will be imported after adding new discipline codes to those activities do not have discipline code before.
4) Suppose the exported excel file is as below and assume that the new codes, (FABR2 and ERST) would be added to that sheet.
The following tips should be considered when you will import spreadsheet data,
Make a copy of your project before importing into existing one. The database field names where the data are in the first row of spreadsheet must not be modified. For example;task_code, status_code, wbs_id,task_name, actv_code_discipline_id, 'start_date,end_date,resource_list, delete_record_flag as shown in above figure. Activity codes (FABR2 and ERST in this post) and resource codes should have assigned (they must exist) in project dictionary before attempting to import. Activity ID should not be changed otherwise can not be imported. Some of the column heading containing (*) sign can not be changed.
5) To import the updated sheet to P6, go to File > Import > select the import format as Spreadsheet-(XLS) > Next > select file to import > select Activities > select the destination project to be imported > Next > Finish. Finally, you will see that these can be updated with a short amount of time using import/export tool. The activity names, WBS code, activity codes and user define fields can be exported from P6 with a very simple way. To do that, go to activity column area > click one of the activities > right click > go to Export to Excel > Select existing file name or give a new file name, and you will get the export file. After you made changes , you may import to P6 using Step-5.
Filters Fail when Exporting from P6 to Excel
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.
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….‖
How to Get Resource Loading Histogram/S Curve in Primavera P6 The resource histogram allows us to use projected and historical actualized data to trend resource quantities, resource hours and cost in future. It also allows us to determine the potential risks of excessive supply of resources or lack of resource requirement.
This tutorial demonstrates how resources allocation chart (histogram/S curve) is produced in Primavera P6. Assume that you have done ―Assigned activities and Sequenced activities to see resource loading histogram/S curve bar chart in Primavera P6. After assigning and sequencing of activities, (1) Allocate overall (2) Assign resources (3) View resource loading histogram/S curve.
resource in
and activity
their detail
rated section
cost and
1) How to allocate overall resources and cost? a) Go to Enterprise > Resource > Note: When you are at Resource, you will see the filter name ―Current Project Resources‖ If you have already opened a resource loaded project. See figure below
A blank project resource dictionary only will be displayed if no resource has been added to current project. See below image.
b) Add New Resource (Note : If the existing resources are not applicable for a specific project or no resource has been assigned to current project.You can skip this step if the required resources are available in resource dictionary.) When you try to add new resource, you will get the message like ―In order to add resource, the filter will be changed to display all resources‖ and you will see all resources for all the projects. New Resource Wizard will be prompted, and follow the wizard. You will be asked to enter the following,
Resource ID, Resource Name, Allocate Resource Type (Labor, Non labor and material), Rate (Price/Unit), Default Units/Time and Max Units/Time, Contact address if applicable Resource roles which describes their skills Resource Calendar Select “Auto- compute if you want actual units are updated by Primavera or select “Do not auto-compute actual” if you can update manually for actual man hours spent. Timesheets set up and log in
After entering of all data, you will notice that the newly added resources are not in the desired location. Therefore, allocate those resources according to their resource level. For example, Pipe fitter, Rigger/Signaler, General worker, Fire watches man, Workshop labor, Painter, etc are allocated under construction department of Petrochemical Projects. See figure below.
2) How to assign resources in activity detail section? Go to Activities page > Select the activity > Go to Resource tab which is next to Relationships tab > Add Resource which is located near the bottom of activity detail box > Select required resource and assign it. You may also assign resource details such ; resource curve, budgeted units and actual units.
3) How to view and print resource histogram and S curve? (a) Go to Activities page > Select Resource usage profile, and then you will see ―Current Project’s Resources‖ Column on the left and histogram and bar chart on the right side of the layout. The individual loading chart, for instance, welder’s resource profile is shown in the following figure.
Remember that you are not able to view both materials and labor or non labor histogram bar chart at the same time as they do not have the same unit of measure. You will be able to view material resource curve, both labor and non labor curve , labor or non labor curve each time individually.
b) You can customize the appearance of bar chart by the Resource Usage Profile Options. Right Click on the bar chart area to find Resource Usage Profile Options. You can select ―By date‖ to see interval data (Histogram Chart) and select ―Cumulative (S Curve) to see total manpower unit in the following check box below.
c) The following check box is used when you want to show manpower resource profile instead of man hour unit. P6 will calculate total men per period (interval) based on working hrs per period (interval) and total man hours per period. For example, total men per week would be 10 when total man hours for a week are 400 and 40 working hrs per week.
d) And then, select all resources in resource column to view overall man power allocation. To print the bar chart, Go to print preview > page set up > Options > Select ―Profile‖ and unselect Activity Table, All columns, Grid lines and Gantt chart. Set the time scale start from ―PS-Earliest Project Start‖ to ―PF-Latest Project Finish‖. Finally you will see the bar chart as below.
e) According to above bar chart, the weekly interval manpower is shown on the left side and total manpower allocation is on the right side.
How to view "Stacked Histogram? You can also see ―Stacked Histogram‖ for this manpower allocation. Right Click on the bar chart area to find ―Stacked Histogram‖. Firstly, click on "Stacked Histogram" tag, and then you can go to "Resource Usage Profile Options" to get screen shot in step-g. See figure below.
Go to Resource Profile options > Assign Resource Filter/Group Name > Select Total cumulative curve as shown in Figure below.
Finally, you will get Stacked Histogram as shown in below. The stacked histogram represents the manpower allocation by individual trade/discipline for a particular period as well as total manpower allocation (total manpower = 200 men, see the following figure) for all time . It can be printed the same way done on item 3, d.
Are you working on multiple projects environment ? Go to "Open" select all projects that you wish to view resource profile by pressing and holding control key if you are handling multiple projects. THE SOLUTION MENTIONED ABOVE IS FOR WHAT VERSION OF PRIMAVERA AS MINE SHOWS THE SAME SCREEN SHOTS AS MENTION ABOVE BUT DIAGRAMS SHOWS IN F STEP IS DIFFEENT FROM WHAT I GET EVEN THOUGH I FOLLOW THE SAME STEPS. WHEN I CLICK/CHECK STACKED HISTGRAM ALL THE BARS WITH S CURVE JUST GO AWAY AND NOTHING ON THE SCREE.I click resource usage profile options and screen i get is same as in steps b,& c , but the one shown in step g is not available in my P6 , I DON' SEE Assign Resource Filter/Group Name > Select Total cumulative i have attached screen shot what i get. PLEASE HELP ME OUT TO LEARN THIS STACKED GRAPHS Reply Replies
1. AnonymousApril 4, 2012 at 4:27 PM Azhar, if you don't see the Total Cumulative curve, your resources are material. you need change their type to Non-Labour or Labour resource. Amireh
Reply
2. Min OoMarch 14, 2011 at 10:08 PM @Azhar Dear Azhar, This is based on P6.0. I think you probably couldn’t find “Stacked Histogram” and “Resource Usage Profile Options” tag as shown in step-f. (I have added the new screen shot for better understanding). If you can follow step-f, step-g can be done easily to assign “Resource Filter/Group Name” which will bring you to produce the S curve/Stacked histogram. Stacked Histogram and S cuve would be produced if you select “Total cumulative curve” (See step-g). If you select on “No cumulative curve”, the Stacked histogram only will be displayed. I hope it would help. Reply
3. Mohammed YousifMarch 17, 2011 at 1:43 PM @Azhar Dear Azhar, For the "Stacked Histogram" to show up, you need to use a filter to add specific resources. To this: 1- Go to the "Resource Usage Profile Options", 2 - On the data tab, on the "Select Pattern/Color" section, Click Add, to add a filter on the resources defined in your project. Do a separate filter for each resource you want to show on the Stacked Histogram. 3- On Filters Dialog, Filter Name should be a descriptive label for the resource in question. Select the resource by "Resource ID". 4-Now you can choose a pattern and color for this resource on "Resource Usage Profile Options" . 5- Repeat the same for other resources ( a single resource each time). Hope this will help
Reply
4. AnonymousMay 27, 2011 at 6:57 PM Hey, this is Kushal this side. I am using P6.2 Version. I get stuck up when I try to go for Future Period Bucket Planning. I allocate the resources, attach a curve to the resource for an activity, but when the Resource Usage Spreadsheet is opened, I don't find the activity cells for any period to be manually editable. I have tried many a times but have failed eventually. I read in the P6 help that the cells have to be white instead of grey for manually editing the resource distribution for the period. What is the concept of this, can anybody please help me out in this regard. Thanking you in advance Reply
5. Min OoMay 31, 2011 at 12:59 AM @Anonymous Kushal,this could be the solution for you. please follow the link: http://www.toolboxforplanning.com/2011/05/working-with-resource-usage-spreadsheet.html Reply
6. AnonymousMarch 21, 2012 at 7:38 AM Hi Min! I learned your tips for manpower stack histogram, however i find difficulty during Print set up. I would like to ask how to increase the font size of the scale (for manhours & cummulative manhours) in P6. Please do share... regards, Anthony D. Reply Replies
1. Min OoMarch 21, 2012 at 9:54 PM Hi
Anthony
D,
It can be done by adjusting scaling. Go to page tag under Page Setup. Then,select the appropiate scale. Normally,better result can be achieved by selecting more than 100%.
Reply
7.
AnonymousJune 18, 2012 at 7:05 PM Hi its good learning experience ..good work Reply
8. AnonymousJuly 4, 2012 at 5:29 AM I need a resource graph that shows linear feet over time. My objective is to determine how many crews will be need to lay various feet of pipe per day. I have primary resources assigned as well as material resources to each project. How do I show the both in the my graph? I need to display the graph as a stacked histogram. Thanks! Reply Replies
1. Min OoJuly 26, 2012 at 1:04 PM Hi, A stacked histogram for both material and manpower resources can be displayed in excel using P6 export data. Please follow the link ;http://www.toolboxforplanning.com/2011/03/developing-resourcecurveprogress-curve.html
Reply 9. HaithamAugust 25, 2012 at 7:13 PM Hi How can i assign an s-curve type (i.e bellshaped) to all resources at onetime Reply Replies
1. Min OoDecember 11, 2012 at 1:49 PM Any S-Curve to all resources can be assigned at onetime by two ways; (1)Resource Assignment Window : Go to "Project", Select "Resource Assignment",bring out the "Curve" column, then fill down the selected curve type to all resources. (2)Global Change : Go to "Tools","Global Change", Create New Global Change query by selecting "Activity Resource Assignments" from "Select Subject Area" drop down list and Parameter is Curve = Bell Shaped.
Reply 10. AnonymousSeptember 18, 2012 at 10:27 AM thanks alot. it was very helpful.
Reply 11. Jothi GanesanOctober 9, 2012 at 10:48 AM What is the meaning of Primavera planning Level 1,2,3,4 ? how to plan quantity wise planning and scheduling ? please help me
[email protected] Reply 12. AnonymousOctober 27, 2012 at 5:46 AM How come my stacked histogram not equal to sum of single Resource usage profile? thanks Reply 13. AnonymousJanuary 9, 2013 at 4:55 PM how to change y axis in resourse usage profile sheets.for egs my y axis shows 1000d ,2000d etc. i want to show in units 10,20 ,30 etc please advice Reply 14. AnonymousJanuary 11, 2013 at 7:09 AM Is there a way for P6 to calculate the manpower in number of craft and display on the curve? For instance, will P6 divide the weekly total hours by 50 hrs/wk to give a headcount?
WBS ( Work Breakdown Structure ) for Engineering and Construction Below figure is shown up to WBS (level-2) for an EPC project including shutdown scope for a petrochemical revamping project . The detail design engineering is broken down into disciplines engineering. Construction is broken down into preturn around, turn around and major construction. The level 1 WBS elements are as follows; 1. 2. 3. 4. 5.
Feasibility study Basic design and engineering Detail design and engineering Procurement Construction
And Detail design and engineering is further broken down into level 2 WBS as follows; 1. 2. 3. 4. 5. 6.
Project engineering Mechanical engineering Process engineering Piping engineering Civil/Structure engineering Electrical/Instrumentation engineering
Level 1 WBS element , Construction can be decomposed into level 2 WBS as follows if revamp scope is included; 1. 2. 3.
Pre Turn Around construction Turn Around construction Major construction
Construction would be decomposed into level 2, 3 WBS as follows if the project scope dosen't include revamp work. Level 2- Plant Area Name Level 3- Offsite Fabrication/Assembly Work Level 3-Onsite construction Work
Excel 2003 Tutorial-Creating Progress S curve Progress S curve can be easily created in excel 2003. It is normally used for graphical presentation of accumulation data. For example, weekly or monthly progress of engineering drawings and documents, production of sheet metal plates, construction of pipe rack and so on. Suppose the monthly plan progress, forecast progress and actual progress are available as shown in Fig.1
Fig.1 (1) Click the chart wizard menu on the standard tool bar ,,"Chart Wizard-Step 1 of 4-Chart Type" window will be appeared and select standard line and chart type, and click "Next" as shown in Fig.2 .
Fig.2 (2 ) "Chart Wizard-Step 2 of 4-Chart Source Data" window box appears and click data range on the data range tag as shown in Fig.3. Source Data-Data range box appears for you to select the designated data.
Fig.3 (3) Select the progress data as shown in Fig-4, you would see Chart Source Data window and click "Next" (Fig-5).
Fig.4
Fig.5 (4) Name the chart title "Progress Curve" in chart title box, "Monthly" in category (X) axis box and "Percent Complete" in value(Y) axis on the Title tag and click "Next". See Fig-6
Fig.6 (5) Choose chart location either "As object in" if you want to see in the same spreadsheet or "As new sheet" (chart will be appeared in the new spreadsheet ). Chart format and line color can be customized either by right click on the chart area or right click on the progress line. See Fig.7
Creating discipline wise manpower histogram using Primavera P6 export data - to be red again Procedure for creating discipline/trade wise manpower histogram using P6 export data is similar to the post How to generate S curve/histogram from Primavera, P6 - Part 1 to 3. You should have resource loaded plan to export manpower data from P6 to prepare resource loaded histogram chart. The following steps are a guide to produce manpower histogram; 1. 2.
Open the selected project that you need to export data Select the Assignments tag on the Directory or go to Project on the menu bar and go to Resource assignments. You will see then Activity resource columns such as Activity ID, Activity Name, Resource ID Name, Start and Finish by default. There is Remaining Units column in Resource Usage spreadsheet field column by default.
3.
As resource name (discipline wise) needs to be shown in Activity resource columns, you need to customise default view. To do so, go to View on the menu bar>>Columns>>Customize. Look for Resource Name in General tag in Available options box. Select Resource Name into Selected options box.
4.
5.
6. 7. 8. 9.
Select Budgeted Units and Actual Units in the spreadsheet field columns if you want to see budget and actual data. You will get remaining early and late manpower data if select Remaining Earl Units and Late Unit. In this tutorial for instance, I just selected the budgeted units. To see budgeted units, go to Resource Usage Spreadsheet area and right click>>select Spreadsheet fields>> Select Budgeted Units. To set resource usage hour per period, go to Resource Usage Spreadsheet area and right click>>select Timescale>>select month/week (to show weekly data) from date interval drop down list. Go to View>> Group and sort by>> Customize and tick on Show Grand Totals, Show Summaries only to see summary data. Go to View>> filters and add the Resource ID names that you want to export data.
10. Make sure the data is organized as you like to display in excel sheet in order to reduce rework after exporting to excel. To do that, stay at the Resource Assignment view, go to View on the menu bar > Group & Sort by > mark on Show Grand Totals and Show Summaries Only box just to display overall level and discipline level summary resource data overtime. 11. Finally you will get resource loading data ready to export to excel as shown in figure below. Manpower field names such as Coded welder, Hot Work Sentry, Trade Assistant, Pipe fitter/Boilermaker and Rigger are displayed in the Resource Name column. Man hour distribution data is shown in the resource usage spreadsheet field.
12. To export, go to activity resource assignment area >> select all>>copy and paste to excel. The man hour distribution will vary according to time scale. Suppose if the time scale is set up by (month/week), the data is weekly man hour data. If you want to convert the weekly manhour to weekly manpower , that feature is available in P6. The periodic average manpower can be calculated using the spreadsheet option box by selecting "Calculate Average" and entering working hours per week. 13. Finally you can create Histogram Chart using that excel data. If you faced a problem of doing this, then leave the comments.
Tutorial-How to generate S curve/histogram from Primavera, P6 Part III Suppose the exported data is as shown in figure below. As we set Cum Budgeted Units and Actual Units in the spreadsheet option field, you will see Cum Budgeted Units and Actual Units data through the project start to the end date.
Important Point: In order to get these data , activity resource, Budgeted Units and Actual Units must be assigned to each activity as described in Part I. If you are not sure how to assign resource and Units, go to How to Assign Activity Resource and Budgeted Unit in P6 .
To analyze overall progress quickly, you can find Plan Value Qty (Budgeted Units) is 3047 and Earn Value Qty (Actual Units) as of 28 Nov 09 is 490. Therefore overall progress as of that data date is (490/3047) 16%. To create progress S Curve and weekly progress histogram chart, populate the progress data . Then finally you will get the S curve and histogram curve showing progress data and bar chart.
How To Link A P6 Schedule To The BOQ With Different Levels Of Detail P6 Schedule and BOQ challenges A contract’s Bill of Quantities is always meant to be more details then the schedule, at least in theory. In many cases, the planner and the estimator are not working in perfect coordination and you may end with two different level of details for the same project. In case you need some background, check out Bill of Quantities explained. Let’s assume you are assigned to a new project that the company has just won. After opening the schedule you will notice that although the bill of quantities and the schedule total budget are the same, the level of the details is different – and the budget was loaded manually from the bill of quantities without any formal relationship between the activity ID, and the pay item. There are many approaches to handle this situation. One of them is to keep manually loading the hours into the schedule, knowing there’s a risk that the next planner will have to do the same guess work as you have done before. Or, you can add extra information that will make your life easier later, when the project receives all those updated BOQ.
We’ll show you this last approach with the help of Excel and some great formulas for manipulating the data. We’ll start with our BOQ, our P6 schedule, do some manipulations in Excel and eventually get it all back into P6.
Preparing The Data Unfortunately, the first step is the most tedious one and there is no easy short cut. First, we’ll create a new User Defined Field in Primavera called ―Bill Item No.‖. Add the column to your layout so we can add data to that field. Remember, in this situation we already have a schedule built. So basically it is a guess works; you print the bill of quantities and you compare the same WBS and the activity descriptions and the assigned budget hours and you start filling in values for the column ―Bill Item No.‖ There are three types of relations. 1. One to One: one activity represents one pay item on the BOQ. 2. One to Multiple: one activity is the summary of many pay items. For example, all culverts of different sizes are grouped under one activity – “Culverts Construction.” 3. Multiple to One: in many cases the BOQ has the total quantities but in the schedule you need to split the work into more detailed sections.
Transforming the data in the spreadsheet Copying P6 Activity Data to Sheet 1 In P6, adjust your columns so that only ―Activity ID‖ and ―bill Item No‖ are on-screen. Make sure there is no grouping (grouping set to
). Highlight all rows and copy the two columns – Activity ID, and the Bill of item.
Paste them into columns A and B on Sheet 1 in the Excel spreadsheet. The formulas on Sheet 1 will do some calculations on the new data.
Any Bill Item No cells with multiple items (like Activity A2650 above with values 31,32,33,34,35) cannot be used in Excel as such. So we need to split these Bill Item No out to multiple rows. Notice the delimiter used here is a comma. We used Excel formulas found here. In column C we find the position of the comma in the cell, then we use mid() to extract the value of the Bill Item. As an alternative, you can always use the excel option ―Text to Columns‖ to have the same result.
On Sheet 2, we use a formula to UNPIVOT the data from Sheet 1. The formulas do all of the work automatically. It means we transpose the pay item from row to column, the formula is courtesy of Dick Kusleika,
Moving on to Sheet 3 Copy the two columns ―Activity ID‖ and ―bill Item No‖ from Sheet 2 and paste them on Sheet 3 as values, filter the blank values from bill item No and delete those row that have blank values for Bill Item No. The column repeated will show how many times the pay item was repeated, the value of budget hours is taken from the tab BOQ divided by the number of times the item was repeated.
Notice now, that we have created a proper data mode between Activity ID and Bill Item No.
Moving on to Sheet 4 A pivot table will summarize the data from sheet 3 by activity_ID.
Voila, you now have the budget hours per activity ID and you can now import back your Budget into P6. You can do get the data back into Primavera P6 by exporting your resource assignments from P6, then perform a look up to bring in the values from sheet 4. If you need help on using lookups, you can always read this post for help on using vlookup in excel.
Wrap Up It is very important to document the links between the schedule and external data, as it will be much easier later when the quantities change and you need to sync your schedule.
How to Convert a Project’s WBS to Activity Codes in Oracle Primavera P6 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.
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).
/p> 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.
Wrap Up 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.
Quick Tip: How To Convert an EPS Activity Code to a Project Activity Code in Oracle Primavera P6 EPS Activity Codes are great when we want to have a set of Activity Codes available to certain projects based on where they sit in your EPS structure. An example might be having a set of Activity Codes for each Department in your database. Typically, EPS nodes would be set up for each Department, allowing an organization to specific a set of EPS Activity Codes for each Department that are only available to their projects. Many organizations setup and use EPS Activity Codes as a means to establish standardized codes for groups of projects. But what happens if we want to move the project to another EPS node and need to preserve the coding? What if the EPS Activity Codes don’t exist at the project’s new EPS node? Will we have to redo all of that coding inside the project? AND what happens if we need to export the project to another database? Will the coding come along? Fortunately, there is a way to preserve the coding in both of these situations. It is possible to convert an EPS Activity Code to a Project-level Activity Code in Primavera P6 allowing you to preserve your coding when moving or exporting a project. Here’s how:
Step 1 – EPS Activity Code Setup Assuming we’ve got an EPS Activity Code set up like so:
Step 2 – Open the Project Open the Project that uses the EPS Activity Code.
Step 3 – Copy and Move Copy the Project and paste it to an EPS node where the EPS Activity Codes do not exist. You’ll see a message like this one.
To preserve the EPS Coding, Primavera P6 will copy the EPS Activity Code to a Project Activity Code to be stored inside the Project and the coding assignments will stay put.
If it makes sense, move the copied Project back the original EPS node. You can chose to keep or delete the original project – it’s up to you. And now you can also export the project with the coding intact.
Create A Festive Gantt Chart Xmas Tree In Primavera P6 For all you creative types out there who love to create something awesome in a manner which is rather non-traditional….this tutorial is for you. In the spirit of the holidays, here are the steps to create a festive Gantt chart Xmas tree in Primavera P6, complete with decorations.
Getting Started with the Gantt Xmas Tree’s Structure 1. Create a new, empty Project plan and name it ―Gantt Xmas Tree‖. 2. On the Defaults Tab (Project screen), set the default Calendar to a 24 hour 7 days per week calendar (or something similar. Best if it is 7 days per week). Open your project for editing. 3. Start by adding a start milestone activity. This will be the ―Star on Top‖ of the tree. Drag the milestone into the future about 8 weeks on the Gantt Chart. When asked to set a ―Start on or After‖ constraint to this activity, click YES. The constraint will HO-HO-HO-HOLD it in place.
4. Add a new activity, set the Duration to 5 days. Drag the activity out on the Gantt chart to line up with the previous start milestone. 5. Repeat step 5 two more times, increasing durations to 10d and 20d, while humming a holiday ditty or two.
6. Duplicate A1010 through A1030 by copy and paste. Increase durations as shown below, drag and drop on the Gantt to add Start on or After Constraints.
7. Stop. Get eggnog……and continue.
8. Repeat step 7 two more times, using the values shown.
9. Add two more activities for the trunk of the tree, as shown. Did you get that eggnog yet?
Adding Colour to the Tree Trunk We change the colour of the tree’s trunk by adjusting the Gantt Chart Bars options. 1. Right-click on the Gantt and choose Bars from the menu. 2. Add a new Bar using the Add button. Name it ―Trunk‖. Move it to the bottom of the Bars list using the Shift-Down button. The Gantt Chart Bars are ordered in layers and moving it down moves the Trunk as a top layer.
3. We’ll now change the filter associated with this Bar. Click in the ―Filters‖ column to see the Filters pop-up window. Add a new Filter, called Trunk.
4. Configure the filter as shown, to filter on Activities named ―Trunk‖. Click OK to save the Filter. Ensure the Filter is checked on and click OK to return to the Bars screen.
5. Change the color of the Trunk Bar from green to brown. Click OK to close the Bars window.
Decorating your Primavera P6 Gantt Tree When I first tried to trim my Gantt Xmas Tree, I thought using the Bar features was a good idea. You can certainly use Bars and Filters to accomplish this, but there’s a much easier way. Here’s how: 1. Right-click on the Gantt, choose Attachments->Text. A text attachment lets you put text on the Gantt Chart and drag it to anywhere you like. Add some colour to your text and use a fun font like Wingdings, and you’ve got decorations.
2. The letter ―l‖ (lowercase) using the Wingdings font at 20 pts in red makes nice Xmas balls. You can experiment with other letters in Wingdings and other fonts to make this creation truly yours. Add as many as you wish, have fun with it.
Wrap-Up Now what? Copy and paste your Gantt Xmas Tree into any ole schedule you like. Make it a surprise for your project managers and sponsors, and executives. At the very least, print it and post it somewhere your colleagues can admire your advanced Primavera P6 skills!
A short Holiday note from PlannerTuts
We want to take a minute to thank everyone who reads our work at Plannertuts.com and keeps coming back for more. Its always a pleasure to share ideas with this great community! I personally want to thank all of the great Authors who have contributed their ideas and work to PlannerTuts. Happy Holidays everyone. We looking forward to an exciting 2013! —Michael
3 Votes
Tutorial-How to generate S curve/histogram from Primavera, P6 Part II To View Cum Budgeted Units and Actual Units Columns in Spreadsheet Fields 1. Click on the layout area and select Resource Usage Spreadsheet as shown in figure below.
2. After Resource Usage Spreadsheet window is opened, you will see resource usage spreadsheet field area (i.e blue shading area). See figure below.
3. Point the mouse to anywhere on the resource usage spreadsheet field area or click on Display button to view the desired columns . Go to Spreadsheet Fields >> Customize as shown in figure below.
4. Then you will see the spreadsheet field window showing Available Options and Selected options. Remaining Units
(Remaining) is shown in selected options field by default. It is used when the remaining percent complete or remaining man hour data are analyzed. For now, select Remaining Units and hide it by clicking backward field. Click the forward arrow button to select Cum Actual Units and Cum Budgeted Units as shown in figure below. In case you wish to view Early Curve and Late Curve , then select the Cum Remaining Early Units and Cum Remaining Late Units.
5. Finally, you will see the resource usage spreadsheet field showing Cum “budgeted units and actual units” fields and data that can be exported to Excel Spreadsheet by copy and paste option.
Group/Organize the Column Data Things that we should have done before export P6 data to excel are resource grouping and time scale format. Resource grouping represents what you want to report or analyze. Group the resource as total when you want to see overall summary data only. To view Group and Sort window, go to View >> Group and Sort by >> Customize. In Group and Sort window, mark on Show Grand Totals and Show Summaries Only to see overall summary data. See figure below.
Set the Timescale format You may change the time scale format when you are preparing to export spreadsheet data. The exported P6 data would be displayed as weekly if the date interval is set to month/week. To see this changes, click the display button on the spreadsheet field area. Go to Timescale and you will see Timescale window >> set to Calendar in the date format type box and Month/Week in the date interval box.
Then what is next??
Export the P6's Spreadsheet Data to Excel Now, you are ready to export P6's S curve/Histogram data to Excel spreadsheet. Select on the shading area and select all. And then copy and paste the data to excel. See figure below.
Tutorial-How to generate S curve/histogram from Primavera, P6 Part III Suppose the exported data is as shown in figure below. As we set Cum Budgeted Units and Actual Units in the spreadsheet option field, you will see Cum Budgeted Units and Actual Units data through the project start to the end date. Important Point: In order to get these data , activity resource, Budgeted Units and Actual Units must be assigned to each activity as described in Part I. If you are not sure how to assign resource and Units, go to How to Assign Activity Resource and Budgeted Unit in P6 .
To analyze overall progress quickly, you can find Plan Value Qty (Budgeted Units) is 3047 and Earn Value Qty (Actual Units) as of 28 Nov 09 is 490. Therefore overall progress as of that data date is (490/3047) 16%. To create progress S Curve and weekly progress histogram chart, populate the progress data . Then finally you will get the S curve and histogram curve showing progress data and bar chart.
Get the downloadable file to learn more
Working With Resource Usage Spreadsheet in P6 Resource Usage Spreadsheet View is a quick site for you to see the overview of resource distribution hours or men overtime. To get there, Go to Resource Usage Spreadsheet icon on tool bar or you can find that View on menu bar. Go to Show on Bottom , then finally you can see Resource Usage Spreadsheet. Spreadsheet view is used for the following application; 1) To analyze the overall resource as well as trade/craft wise allocation periodically and along the schedule frame. a. As below figure, item (1) represents the resource allocation hours per activity for remaining work. Item (2) represents the total resource allocation hours for all activities carried out by each resource (welder in this case) for each period. b. For both items (1) and item (2) are used to analyze the resource availability over periods against the resource assignment for each activity. c. Item (3) displays the current opened project resources to be managed for each trade/craft. d. Item (4) shows the desired columns such as activity name, start, finish, remaining units, etc. e. Item (5) shows the required spreadsheet field data. To change field data, do the right click on the spreadsheet field area, go to Spreadsheet Fields, Customize and select the option that you want to analyze. 2) To edit or adjust resource allocation if the auto populated hour or men are not the same as available resource. a. Item (6) represents the editable cell areas filled with white background colour. Take note that the finish date will be changed when the data followed by 8 (i.e. 12, 20, 20, 8) are added. The data within the planned date can be edited without changing the dates. b. Item (7) shows the cell areas filled with gray background color that can not be edited. The cell area for a particular period when the activity start date is not available or doesn’t plan to start or when the activity is completed 100% can not be edited. Sometimes, you may notice that some cell areas can not be edited manually as the actual date is mistakenly updated and then unclick the actual finish date which makes the remaining resource hour “0” (zero). In that case, go back to the Activity Detail box and enter the remaining units hours manually. Eventually, you can edit the data as required.
inShare0 You might also like:
Developing Resource Curve/Progress Curve from P6 Data Using Report Wizard if you want to get the detailed S curve report and histogram , you have to export it into excel. It is done with a few clicks using Report Wizard. Report Wizard helps you save your time by creating resource allocation reports for each type of histogram/s curve.
Step to create time based histogram and S-curve 1. Open the selected project. 2. Go to Tools > Report, when you at there, you will see All Reports layout showing all the reports. 3. Look for Report Group: Resource Usage to organize to the newly added report to group in resource usage group so that you can find this report easily when you need. 4. Right click > Add > New Report > Next > Select the “Resources” in the Subject Area box > Select Time Distributed Data check box to see the project data over time frame and click Next . 5. To configure selected subject areas for the required data, select the Resource Name in the columns box, Add filter for current project’s resource as shown in figure below.
If you intend to review a resource graph that also shows material supply and non labour (i.e Cranes, Trucks, etc ) resources over time, include them in the above resource filter. Then, you will be able to review how many manpower or equipments will be required to erect tons of steel structure, various feet of pipes per each day or each week. Unlike the Resource Usage Profile in the activity view in P6 , all resource can be displayed in a single bar chart using a clustered or a stacked histogram by exporting P6 data to excel spreadsheet. 6. You will find Timescale tag and Time Interval Fields tag in the Date Options window upon completion of step 5. Select Show Time Intervals and Show Total check boxes to display project data over period and all time respectively. 7. In Timescale tag, set the time scale start as PS-Earliest Project Start, time scale finish as PFLatest Project Finish, date intervals as week (select day, month as desired).
Review Resource Loading 1. To review manpower loading, select “Calculate Average “ option in Timescale tag to calculate manpower allocation per period. Select Budgeted Unit (to display planned data) and Remaining Early Unit (to produce forecast data) in Time Interval Fields tag. 2. Name the Report Title and click “Next” and save it. 3. Go to Run report > Select ASCII Text file > Select Field Delimiter and Text Qualifier , and choose the output file location to export P6 data to excel as shown in figure below.
4. Finally, the exported manpower loading data would be looks like this. Planned and forecast data are the same in this post as project hasn’t been scheduled. 5. Now, you can produce the desired resource histogram chart in excel.
Review Progress Curve You can also export P6 data to make progress S curve using this method. 1. Create the new report by following all the steps in "Step to create time based histogram and Scurve". 2. Leave “Calculate Average” check box as blank (i.e. do not select it). As a result you will get resource hours data. 3. Actual progress data would be produced when the actual works are begun. To display actual progress data, add Actual Units to Time Interval Fields tag. 4. Run the report using step -3 in "Review Resource Loading " and export it to excel. The exported data is man hour unit. 5. Calculate the periodic progress from the periodic hours divided by total project hours. 6. Once progress calculation is done in excel, insert S curve chart to view the progress curve.
The related posts that you might be interested in are How to Get Resource Loading Histogram/S Curve in Primavera P6 (Shows how resource allocation chart/S curve are produced in P6), Creating discipline wise manpower histogram using Primavera P6 export data (It will guide you how to develop trade/discipline wise manpower histogram without using Report Wizard), Tutorial-How to generate S curve/histogram from Primavera, P6 - Part I,Tutorial-How to generate S curve/histogram from Primavera, P6 - Part II, Tutorial-How to generate S curve/histogram from Primavera, P6 - Part III (You can learn step by steps guide to develop progress S curve/histogram by using excel data from P6 ). inShare0 You might also like:
How to Work with Look-ahead Schedule Using P6 Ideally, you have to report the look-ahead schedules after you've completely satisfied with your estimations and schedules of the project and approved by project manager and/or client, and project started progressing. To be able to communicate the project team what the upcoming activities or next goals are within look ahead period so that the necessary resource planning are done ahead of the planned date, look ahead schedule is deployed in scheduling. It is a tool to help you maintain control of your work and also indicate how importance planning is meeting overall project schedule. In addition to that, your client will also request to produce the look ahead schedule containing the upcoming activities within the next three weeks, for instance, so that they are aware what and when the necessary actions to be taken (e.g. document review by client, permit approval operation, etc), when would be the interfaces required among the stakeholders, what activities are being done, and what activities/deliverables are expected to be done in next two to three weeks. inShare0
Creating discipline wise manpower histogram using Primavera P6 export data Procedure for creating discipline/trade wise manpower histogram using P6 export data is similar to the post How to generate S curve/histogram from Primavera, P6 - Part 1 to 3. You should have resource loaded plan to export manpower data from P6 to prepare resource loaded histogram chart. The following steps are a guide to produce manpower histogram; 1. 2.
Open the selected project that you need to export data Select the Assignments tag on the Directory or go to Project on the menu bar and go to Resource assignments. You will see then Activity resource columns such as Activity ID, Activity Name, Resource ID Name, Start and Finish by default. There is Remaining Units column in Resource Usage spreadsheet field column by default.
3.
As resource name (discipline wise) needs to be shown in Activity resource columns, you need to customise default view. To do so, go to View on the menu bar>>Columns>>Customize. Look for Resource Name in General tag in Available options box. Select Resource Name into Selected options box.
4.
5.
6. 7. 8. 9.
Select Budgeted Units and Actual Units in the spreadsheet field columns if you want to see budget and actual data. You will get remaining early and late manpower data if select Remaining Earl Units and Late Unit. In this tutorial for instance, I just selected the budgeted units. To see budgeted units, go to Resource Usage Spreadsheet area and right click>>select Spreadsheet fields>> Select Budgeted Units. To set resource usage hour per period, go to Resource Usage Spreadsheet area and right click>>select Timescale>>select month/week (to show weekly data) from date interval drop down list. Go to View>> Group and sort by>> Customize and tick on Show Grand Totals, Show Summaries only to see summary data. Go to View>> filters and add the Resource ID names that you want to export data.
10. Make sure the data is organized as you like to display in excel sheet in order to reduce rework after exporting to excel. To do that, stay at the Resource Assignment view, go to View on the menu bar > Group & Sort by > mark on Show Grand Totals and Show Summaries Only box just to display overall level and discipline level summary resource data overtime. 11. Finally you will get resource loading data ready to export to excel as shown in figure below. Manpower field names such as Coded welder, Hot Work Sentry, Trade Assistant, Pipe fitter/Boilermaker and Rigger are displayed in the Resource Name column. Man hour distribution data is shown in the resource usage spreadsheet field.
12. To export, go to activity resource assignment area >> select all>>copy and paste to excel. The man hour distribution will vary according to time scale. Suppose if the time scale is set up by (month/week), the data is weekly man hour data. If you want to convert the weekly manhour to weekly manpower , that feature is available in P6. The periodic average manpower can be calculated using the spreadsheet option box by selecting "Calculate Average" and entering working hours per week. 13. Finally you can create Histogram Chart using that excel data. If you faced a problem of doing this, then leave the comments. Get the excel template data to learn more. inShare0
Tips and Tricks on Schedule Integrating and Statusing using P6
If you are an owner’s representative planner you may have a difficulty to integrate contractor’s schedules into your integrated schedule. There are two ways of schedule integration; (1) Using Copy and Paste Activities (2) Update to an Existing Project.
Schedule Integration Using Copy and Paste Activities Integrating schedule developed by contractors may not be easy if the WBS structures are different from your project. If all the WBS Codes are the same, it can be imported and updated easily. If it doesn't exist, multiple schedules developed and updated by contractors can be integrated into your schedule using copy and paste activities in P6. Once the project progresses, the new information might be added to contractor’s schedule due to changes of sequences for some reason. It is typical that contractors would issue the revised schedules during project execution. The revised reschedules can also be integrated into your schedule by copying WBS from the contractor’s schedule and pasting into your schedule. The following diagram shows the process of work flow when multiple schedule are integrated into one. Suppose your schedule containing owner’s activities has been developed (Step-1) and the three contractor schedules required to integrate into your schedule have been issued to you (Step-3).
Step-4 Import Contractor’s schedules XER file to your database. When starting importing new file to your database, In the Import Project Options dialog box, choose Create New Project option under Import Action field. Click on the Import To field to select the EPS location that you want to import. Select Default Configuration in the next dialog box Update Project Options. Click Next and then Finish the importing process. Step-5 Open All projects (i.e. your schedule and contractor’s schedules) To open the all projects, select all projects that you want to open. Right Click and select Open Project. Suppose you have Project A, Project B and Project C and Integrated Schedule (your schedule) as shown in the following figure.
And Go to the WBS and see the WBS structures of projects. You will see WBS structures of contractor are not the same as yours. The following figure shows WBS structure before integrating activities.
Step 6 Copy the activities from Project A,B and C into the Integrated Schedule Click on the Project A, Right click and then Copy Project A and paste it to the first level (Integrated Schedule). Repeat the same for the Project B and C. You will find the new WBS copied from new projects as shown in the following figure.
Go back to Step-1 to do the necessary links among the projects. Finally you will see a stand alone integrated schedule containing all contractor’s activities. Remember to reassign project calendars to contractor’s activities if their calendar’s specifications are different from global calendar. The default calendar will be assigned into contractor’s activities by default when the activities are being copied from their projects. After completed, assign the baseline and make a back up copy of the integrated schedule. When you have new information for interfacing works among the contractors or other parties, provide the necessary information to contractors Step-7. When you get the revised schedules from contractors go back to Step-4, 5 and 6 and 7 accordingly. Make sure all projects have the same data date. Fix the broken links as the links done in Step -1 will be lost when you delete the existing WBS (shown in Step-6). The advantages of doing by the above method are ;
You can have stand alone project once integrated. You just need to open one project
The disadvantages are
You need to re-links and reassign project calendar again when the revised schedules are imported. There is an easy way to work with multiple projects if the contractor’s schedules are not required to integrate into one and you just want to link contractor’s schedule ( as external links) and yours. The amount of work you are doing will be lesser than above method (copy and past activities).
What is next ??.. After Step-1, 2, 3, 4 and Step-5 (as described on above and as shown in process diagram) are done, you just need to link among the opened project interfaces. Then, assign the baseline and save a copy of back up schedule. When you received the revised schedule from contractor’s, go back to step 4 and import the projects.
Schedule Integration Using Update to An Existing Project This is different from the above method that saves your time.
In the Import Project Options dialog box, choose Update Existing Project option under Import Action field. Click on Import To field to select the opened Project A that you want to update. In the next dialog box Update Project Options, Add a new configuration and name it as Update to an Existing Project. See figure below.
Select the Update to an Existing Project and click on Modify tag to modify its default configuration as shown in the figure. In the Modify Import Configuration dialog box, select Update Existing option under Action field for Resource, Resource Codes and Calendars. To use that new configuration, remember to mark it “Yes” which is next to Update to an Existing Project layout configuration. Repeat the same process when the project B and C are being imported. After all contractor’s schedule are imported, check the new data. Make the necessary linkages if the new activities added that affect the successor activities among the projects.
This method makes easier than the above method (Copy and Paste activities). It saves a lot of time when the subsequent updated schedules are imported. inShare0 Email ThisBlogThis!Share to TwitterShare to Facebook
