Note: the prints of this article are based on a Portuguese installation of the software used. However, they will be translated to the English language.
The Problem
With the launch of Microsoft Project 2013, many new features were introduced to help project managers maximize their experience in managing their projects. The feature that I liked most was the new Reports session, which gives you an entire set of reports and dashboards where you can see, understand, and share information about different aspects of your projects.
The problem here is that as many corporations, as well as individuals, are still using old versions of Microsoft Project. Consequently, they do not have access to this feature – and they get a bit frustrated.
The Solution
The good news is that with the use of two complementary tools – Microsoft Access and Microsoft Excel – it is possible to create stunning visual reports and dashboards without Project 2013. Additionally, you can create the reports while maintaining the connection with the original data, which means that as soon as you update the schedule you can also refresh the report.
At the end of the day, you can have a dashboard that looks like this:
First step – exporting the data to access
Few people know, but ever since the 2007 version of Microsoft Project, it is possible to export data to a Microsoft Access database. As soon as the information is exported, you can use Microsoft Excel to connect to the database and then build the dashboard based upon your needs.
I´ve created a simple schedule in Microsoft Project 2007, which I will use as the example for this article:
To export the information to Microsoft Access, follow these steps:
- If you are using Project 2007: click on Report > Visual Reports
- If you are using Project 2010: click on Project > Visual Reports
- If you are using Project 2013: click on Reports > Visual Reports
After choosing the option that applies to your version of Microsoft Project, you will see the Visual Reports dialog box. At the bottom of the dialog, click on Save Data:
After that, click on Save Database:
Select the place where you want to save the file, as well as its name. Make sure that you are saving the file with the extension Microsoft Access Database – (.mdb)
Creating the dashboard
Once you have exported the data, you have to switch to Microsoft Excel to import the data from the saved database. After following the required steps, the wizard will show the list of available tables. The schedule information is stored at the MSP_EpmTask table. Select the table and then click OK:
To finalize the process, you will have to choose the option to show the data as a Pivot Table:
Now you can see that all the information related with the schedule of your project is available on the fields of the Pivot Table:
Now it is up to you and your creativity! You can make as many combinations and as many Pivot Tables as you need in order to create the dashboards you want. Here is the first example of the article:
Next time, when there are updates to the schedule, the only thing you will have to do is to export the data from Microsoft Project to Microsoft Access again, in order to replace the old database. Then, when you open the spreadsheet, it will read the new data and your dashboard will be updated automatically.
I hope this article is useful!
