Page 1 of 7
Appendix A.57-A.59 Earned Value Management
Visual Reports: Earned Value Chart
Visual Reports was new in Office 2007. In MS Project 2010 it works pretty much the same way, but maybe a different path (tab, command selection). Please read pages A.57 – A.59.
On pages A.58-A.59 the book talks about creating the Earned Value chart in Excel. In the Help blue box it mentions changing the level of usage data from weeks to months. If you do the steps in #3 then you will get an Excelpivot chart that look like this:
Technically it works, but I don’t like it because it shows quarters and not enough detail. I would prefer months. So…. my suggestion is to close the Excel program and return to MS Project. Then repeat the steps in #3, but change the level of usage data from Weeks to Months. Then click the View button. You will get a chart that looks like this:
Hmm….It is still not what I want. It shows only the totals. Well this is where you need to know Excel and how to work with pivot tables and pivot charts. I will show you how to modify the table and chart to get the months data.
In Excel, click the Assignment Usage withEV worksheet tab to go to the pivot table. Click anywhere in the pivot table to get the PivotTable Field List task pane. Your screen should look like:
In the PivotTable Field List scroll down the list of fields to under Time, the Time Monthly Calendar field. Click it to get the check mark.
Point to the Time Monthly Calendar field and drag it with the left mouse button down to the Row Labels area:
Your table should look like:
We are almost done. The pivot table right now is showing the 2009 year totals, but we want to see the monthly totals. In the pivot table click the + expand button next to 2009.
Now let’s look at the chart. Click the Chart1 worksheet:
It’s much better, but there are two small things I would like to change. I don’t like the AC abbreviation in the legend and I would preferthe $ currency format on the Cost vertical axis. So we need to changethese in the pivot table and then they will change on the chart. Click the Assignment Usage withEV worksheet tab. In cell E4 change AC to Actual Cost. Switch back to Chart1 to see the change in the legend. Go back to the theAssignment Usage withEV worksheet. Select cells C5 – E12, on the Home tab click the Number Format drop-down arrow (shows General), click Currency. Then change the decimal places to zero by clicking the Decrease Decimal button in the Number group. The pivot table should look like:
The pivot chart should look like:
As you can see it helps to know Excel pivot charts and tables. I strongly recommend that you take the CIS 151 Excel class if you have not done so already.
Saving the Excel Workbook
If you are in Excel 2007 you will get a warning about saving the .XLS (Excel 2003 file format). Make sure you use the Office, Save As… command and save it as an Excel 2007 workbook.
Opening the Excel Workbook
Because you created the Excel workbook from the MS Project file (Visual Reports…, View button) there is now a connection between the Excel file and a data cube .cub file on your computer. When you open up the file you will see the Security Options message/button (depending on your security settings).
If you try to make changes in the Excel workbook you will get warning messages about the external data connections. Enable the content/connections by selecting the Options… button on the security message. Then click the Enable this content option and OK button.
Here’s the tricky part. The .cub file is stored where ever MS Project wants to put it on your computer. It usually does not travel with the Excel file. So make sure you do all of the formatting and changes before you move the Excel file to a flash drive or to another computer. If you are not on your computer, then you may not be able to make changes to parts of the pivot table/chart. There is a way to save the data to a data cube .cub file and then open up the data cube .cub file in Excel. Then create the pivot table and chart from the .cub file. Using the data cube file gives you the flexibility to create all kinds of charts from a snap shot of your project data. This of course is beyond the scope of this class.
I hope these notes are helpful to you.