Create a P6 Project Dashboard in Excel

Project Dashboard in Excel

Tutorial Files

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),

3
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

  1. This workbook is not P6 specific, as the actual values per period are store in Excel, you can use any scheduling software you want.
  2. The VBA macros are used only to fill down formula.
  3. We use ID as a reference here, it can be the schedule Activity ID, or Bill Item ID.
  4. We’ve already shown you how to move from Activity ID to Pay Item.

The workbook is organized on 5 sections.

  1. Dictionaries: data that you input only once when you start your project (WBS, Non working days, reporting Period, Commodities definition).
  2. The project baseline.
  3. Progress data: the percent complete of activities and the spent hours by reporting period.
  4. 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).
  5. 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)

8

Commodities

Here we group the activities by major commodities, if you group Activities they have to share the same Unit of measurement.
7

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.
9

Baseline

4
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

1

All the progress figures are input in this sheet.

Suppose you want to update progress for the period 20 Jan 2012,

  1. Select 20 Jan 2012 in the drop list menu.
  2. Copy and paste the values from the previous period (the actual is cumulative).
  3. 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.
  4. 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.
  5. 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)5

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”.

10

2

 

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
Did you learn something with this tutorial?  Leave us a comment or rate this post and let us know what you think.

Comments

  1. says

    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

  2. says

    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.

  3. dash_84 says

    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??

  4. says

    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

  5. says

    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

  6. says

    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

  7. says

    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

  8. says

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>