
Tutorial Files
- Excel Spreadsheet (Excel 2007+): PTuts_Project_Dashboard.xlsm – 1.5Mb
One of our previous site manager told us (the project control team), that if we bring to him another report that have more than 1 page, he will just throw it away (sic), actually managers care only about the big picture, and don’t bother reviewing every small details. The purpose of this tutorial is to create a relatively good looking Excel Project dashboard with the most relevant graphs and indicators on it.
What can P6 Professional do?
Let’s see what P6 offers in this regards (here P6 refer to professional, EPPM has a whole stacks for reporting and dashboarding). P6 has a very rudimentary graphics reports, and the bundled report writer is a limited piece of software, but that’s fine, P6 is scheduling software not a reporting engine.
In that case, we do expect that P6 offer an easy way to query the data from the database, so we can generate all those fancies graph and reports, and that’s the real issue here:
- P6 does not store the period actual progress by default, you have to use store period performance, unfortunately financial periods is defined at the database level, it is not project specific ; for example if you have two different projects with different cut off days, you are out of luck. (Unless you create a different database)
- Even if you want to use the store period performance, when the project progress and the initial quantities changes, and those engineer on site start changing the previous recorded progress, because that supervisor were exaggerating the figures, then maintaining the progress in P6 will be a time and hard consuming task.
- The spread data are not stored in P6 database, they are calculated on the fly by the client software (summaries project work only at the WBS level not the activity level),
So if you are using P6 professional and you need to query the spread data at the activity level, your only solution is to use the SDK, which is a clunky piece of software. And if you are not an expert on the P6 database schema you may end up corrupting your data. (For the record the API was removed from P6 Professional, it is only available now in P6 EPPM)
What you should Use?
- If you are working with a big contractor, probably you are using a proprietary in-house database to manage progress or EPPM, and you do have access to a all kind of live dashboard reports. So this tutorial is not only not useful but highly not recommended. I would not imagine the frustration of Project controls manager when their team starts using offline systems 🙂
- If you are very comfortable using the P6 SDK, and the limitation of financial period doesn’t particularly bother you then fine, query the data from P6 Professional and use your favourite reporting software to generate your Dashboard ( Crystal reports, Excel, Tableau ….. ).
- If you are not an expert in the P6 database schema, and your project has a lot of quantities revisions, then Excel (if properly used) can be a very good and convenient tool. Many professional hate it because many planners tend to create very complicated spreadsheets that no one understands except for them.
How the excel Project Dashboard workbook is organized
- This workbook is not P6 specific, as the actual values per period are store in Excel, you can use any scheduling software you want.
- The VBA macros are used only to fill down formula.
- We use ID as a reference here, it can be the schedule Activity ID, or Bill Item ID.
- We’ve already shown you how to move from Activity ID to Pay Item.
The workbook is organized on 5 sections.
- Dictionaries: data that you input only once when you start your project (WBS, Non working days, reporting Period, Commodities definition).
- The project baseline.
- Progress data: the percent complete of activities and the spent hours by reporting period.
- Data: it pulls data from the sheets and presents in a database friendly format (values are listed in column, with the first row as header).
- Output: the Dashboard.
What is inside every tab of the Project Dashboard spreadsheet
WBS
Copy your WBS dictionary here.
WBS_Split
This sheet will use the WBS dictionary to split the WBS by level; it is used for the Actual sheet, as we need to organize the activities by
WBS level
In a previous tutorial, I showed an “ugly” solution how to split the WBS level, but for this tutorial, I am using a much more elegant solution (if you want to see an example of beautiful solution check this one)
Commodities
Here we group the activities by major commodities, if you group Activities they have to share the same Unit of measurement.
Holidays (Optional)
You list all the non working period of your project (weekend, holidays …); it is used only if you want to create the spread in Excel
Period
Your reporting period, in our case we used Friday as a cut off.
Baseline
We use the planned dates from P6 and the non-working period to generate the planned values spread. It is really useful if your schedule is not resource loaded, or if you want a quick way to have a first look at how the overall curve will look like.
Obviously you can later overwrite those values and use your P6 spread, you need only to normalise your data. (0- 100%).
Spent_Hours
here you input the spent hours (the actual burnt hours by direct labour), notice we record the spent hours at the project level, then we prorate the data based on the progressed activities on the reporting period, it is not very accurate as the best way would be to record at the activity level or at least the commodity level, but getting this kind of information is always “problematic with the field People”
Actual
All the progress figures are input in this sheet.
Suppose you want to update progress for the period 20 Jan 2012,
- Select 20 Jan 2012 in the drop list menu.
- Copy and paste the values from the previous period (the actual is cumulative).
- Update the % complete for every activity that has progress in this period, the % complete is always between 0 and 100 %.
- The % field is “installed quantities/ Quantities at completion”.
- The quantities at completion are your best available estimation at the time of your update. In most cases the initial quantities in the BOQ are just estimation and have to be updated once the work has started.
- It make sense once the activity is 100 %, the quantity at completion is frozen.
- Select if the earned value is based on the current quantities or the Baseline Quantities, notice the planned values are always based on the baseline.
- Although the Overall percent completed based on the current is more accurate, many clients prefer to use the original baseline quantities unless they release a new revision.
- Note: the overall percent complete is a subjective figure as it depends on the budget quantities, but the installed quantities are a fact and they are independent from the budget.
- Click on calculate.
Data
All the data from other sheets are grouped here, when you group your data into columns, you can easily and quickly create your reports and charts.
To reduce the size of the excel file, when you close the workbook there is a VBA macro that deletes all the records except the first two rows. (They are generated every time you click on calculate in the “Actual” sheet)
Dashboard
- Overall Progress planned vs. actual.
- Main commodities planned vs. actual.
- Productivity curve.
- Tabular report Progress Planned vs. Actual by WBS.
- Tabular report Quantities Planned vs. Actual by major commodities.
In order to group many reports in one Page, a good trick is to create your reports in a separate tab then copy and paste it on the dashboard using “Paste Picture Link”.
Wrap Up
Although P6 Professional is hugely used planning software, some very important features used daily by planners are missing, the reporting capabilities are very limited, and it is very difficult for the average users to mine P6 database (Especially spread field) to extract useful data.
Recently Oracle has added the extended schema to P6 database but unfortunately it is available only to EPPM users.
the limited functionalities of store period performance, the non ability to define financial period per project and the complicated use of SDK ( specially for a production system), all that have an unfortunate result : a very powerful Database with a excellent business rules but limited capability to generates good and useful reports.
I really hope that Oracle continue improving P6 Professional and not concentrating only on EPPM, because there will be always a huge market for standalone users.
Things for you to try
Excel is by far the most used software to manage data, it can save you a lot of hours if automate your workflow, and if you want your complexes spreadsheet to be used and understood by others, you can use very simple steps.
- Separate data from reports.
- Always write your datasets as a database, first row as header and the rest values.
- Add a help section where you document how your formulas work.
Here are some must-know functions to learn if you want to create awesome reports in Excel
- Countif: check if the item is unique (the bill item should be unique).
- VLookup: (even better index/match) to look up values.
- Sumifs: for calculating sum with conditions.
- Offset ( just Google it , it is awesome)
- Named range
- How to record a VBA macro
—
Hi sir I am from Afghanistan. I work as Scheduler with Construction Company here in Afghanistan. Sir if you have schedule for a project which have been completed so i need that project XER file because i need to learn some thing new. Thanks please sent it on my email. Thanks again
I dropped in my wbs and split it (which took excel about 10 mins to complete) after that i’m not sure what to do. It’s seems unclear to me what this tutorial is saying to do to get the other figures.
Luke
your program is ms project or primavera ?
Thank you , but the file attached ((ptuts_Project_ Dashboard.XLS)) is not found .
Please, check the link of the file .
Dear Mimoune,
Really useful information. However I am not able to understand few things:-
1) Whatever data is populated in excel is it populated manually (i guess not) or is it extracted from Primavera? If latter is the case, how is it done?
2) There seem to be one excel full of formulas and macro which is used for the dashboard. Can you share that? If not can you explain in a bit detail how exactly this report can be made from excel??
hi Dash
1-all the data are from P6, the wbs, the activities, the dates, you copy your data from P6 and you past exactly in the same columns
2- all the formula are in data, they just lookup values from baseline and actual, and present the data in columns
Thank you very much.
It was helpful…
I’m using Primavera P6 v7. Do i have to have a baseline set in order for the dashboard to work
yes you need the dates from the baseline in order to get the planned values
hi Reed
yes you need a baseline, actually you activity id, the planned start, planned finish, planned quantity and planned budget.
in order to compare to the planned curves you need a baseline, you have to populate the following columns.
activity id, planned quantity, planned budget, planned start and planned finish
dear admin
greeting
thank very much for great tips but I have ask about another topic ….. I ask about tool (check in , check out) in Primavera P6 R8.2 …what is uses & applications of this is tool & how can we use the is tool to our projects or our schedules
dear admin
greeting
thanks for great tips & explanation…but I have question of another topic… my question about tool (check in , check out) in Primavera P6 R8.2 …what is uses & applications of this is tool & how can we use the is tool to our projects or our schedules
zeinco
i am not the admin, to contact the administrator please use this form
http://www.plannertuts.com/about/
cheers
Master : Mimoune Djouallah
my question it is not exclusive to admin …I will be thankful if you can answer me
hello sir,
iam a student of NICMAR pune
working on managinf project budget by using p6 and excel
i want to cover whole project from initial to last including all finanacial aspects
after p6 i want to import whole p6 file into excel
please give me some suggestions on my working
Great work Mimoune, thanks.
Please Sir, I want to know how to calculate Budgeted Manpower in P6.
Thanks
Hi Mimoune
A quite useful tool. Thank you for sharing this. It definitely took hours to develop. I developed similar excel dashboards myself, but you definitely have gone way further. Much appreciated.
Do keep up the good work.
Cheers
Kajal
I wish we could automate the initial data entry by exporting it from an XER. Have you thought about that?
Kajal
Thanks, very useful
Coloration. The quality of color, or even “yellowness”, from the diamond may vary through natural stone in order to natural stone. You will need to understand what shade to choose from the color range through light in order to dim. The color you select needs to be established about complexion: don’t forget, which diamond is designed to become used as well as flaunted, so it will want to look excellent about whoever’s wearing it.
salomon new
Complete the particular structure. To begin with a person new mosaic jewellery undertaking, think about do is to draw jewellery to behave like a design and style bottom. Obtain a piece of paper as well as a pen along with track the jewelry design on the paper. Utilize smooth fat jewellery you’ve got since the searching for style.
NMD Sneakers http://www.rosser-sav.com/nmd-r1-w-pk-shock-pink-core-black-running-white.html
Colour. The degree of coloration, as well as “yellowness”, of the diamond may vary coming from natural stone for you to natural stone. You’ll need to know very well what hue to select inside the coloration scale coming from mild for you to darker. Large you pick needs to be established on appearance: don’t forget, that diamond is meant to end up being worn as well as flaunted, so it may need to look great on whoever’s wearing it.
new balance 1260 womens http://www.ifindwholesalers.com/new-balance-mrl996cg-unisex-classic-running-shoes-clearance.html
You can also determine if there are particular jewels that are being sold on sale prices. These can possess a seasonal figure, particularly when they are delivery jewels. By way of example, amethyst is often a jewel that could sell a lot in Feb. Staying so, sellers might imagine of skyrocketing its cost. Nevertheless, probably two to three a few months following, this can end up being sold from lower prices and regarded as being a discount jewel.
adidas cologne http://www.okshoppy.com/online-adidas/superstar-clearance.html
Inside nowadays world actors will almost always be thought to be trend emblems. Their own organic propensity would be to spotlight them sesalomon menes simply by undertaking functions just like simply by inscribing tattoo designs. These people printer a wide variety of styles on their body as a way to exhibit their own exclusive trend affirmation. Not surprising, the diehard supporters of these actors perform related styles to demonstrate their own love and respect in direction of their most favorite stars. This is the way by which actor or actress tattoo designs possess acquired enormous popularity amongst folks.
adidas iniki runner http://www.okshoppy.com/online-adidas/sales-on-yeezy-750-boost.html
I’m given PrimaveraReader third-party software for this purpose. And it does a great job.
hello sir
I have create the project in Primavera and then inside the project, I have attached the images and document in notepad after that I am open this project in Dashboard then all images and document are visible in the dashboard. after that, I am using another pc and open this project in the dashboard after that the document is visible but images are not visible.
plz, help me how to solve this problem.
HOW DO I GET EARNED AND BURNED UNITS FROM P6 . DO U HAVE ANY FORMAT FOR THIS EARNED AND BURNED UNITS DURING PROJECT , KINDLY CLARIFY THIS DOUBT FOR ME