Custom Visual Reports for Multiple Project Schedules

Microsoft offers two main cloud-based platforms that allow organizations and individuals to manage their portfolios, programs, and projects at an enterprise level. They are Project for the web and Project Online.

While Project for the web provides simple, yet powerful work management capabilities for project managers and team members to collaborate in their initiatives, Project Online is a flexible solution that provides powerful project management capabilities for planning, prioritizing, and managing projects and project portfolio investments (you can learn more about these two platforms here).

Although a large number of firms from different industries use either Project for the web or Project Online to support their project management initiatives, there is a significant number of organizations and individuals who are still using the standalone version of Microsoft Project to manage their schedules. One of the biggest struggles for these users is the fact that, contrary to what happens when using a cloud-based PPM platform, the standalone version of Microsoft Project does not offer a portfolio-level report that allows users to consolidate information about all their .mpp files in a single visualization.

As a dashboard addict (you can confirm this here, here, and here 😊), I’ve tried to come up with a solution that would allow users of the standalone version of Microsoft Project to create a portfolio report for their projects.

The Approach

To make it happen, the following approach will take place:

1. I will use the strategy of exporting the schedule’s data into a Microsoft Access database (as described in this article).
2. Once all .mpp files are exported as .mdb databases, I will use Microsoft Excel to obtain the data, thus combining Power Query and Power Pivot to transform and load the data accordingly (this can also be achieved by using Power BI).
3. Finally, once the data is transformed and loaded, the dashboard will be created.

Step #1: Exporting the Data

The first step is to export the schedule’s data into a Microsoft Access database. Use the process I linked to above in item #1 above to learn how to accomplish that.

I like using the approach of naming the .mdb files with a standard prefix and suffix, so it is easier to manipulate the files in Power Query when transforming them. In the example below, I am creating a new Microsoft Access database from a project called ‘Advanced AI Database Technology.’ Note that I am appending to the name of the file the prefix, ‘Database-,’ and the suffix, ‘.mdb.’

Step #2: Obtaining and Transforming the Data

With all the schedules exported, the second step is to use Microsoft Excel to retrieve them. In Excel, go to Data > Get Data > From File > From Folder. Browse to the folder where all the schedules have been placed, as shown below:

In the next dialog, click Transform Data to initiate the process of manipulating the files.

You are now transforming your data in Power Query. The very first action is to filter the files being retrieved, so that if there are files of different extensions other than the .mbd, they are left out of the query. I usually have a single folder that contains the schedules (.mpp files) and the databases (.mdb files) together, so that’s the reason I personally need to take this action.

Next, we combine all the files.

Power Query needs to be informed of the object(s) to be extracted for each file. Select the MSP_EpmTask table.

At this point, the magic happens! Power Query will combine all the files based on common attributes from the extracted object.

Now that the results are available and you have the combined data from all files, it is time to organize them. As not all the imported columns will be used to create the dashboard, it is best to remove the columns that aren’t required making the report lighter and more efficient. Use the Choose Columns command in Power Query to select the following:

• Name
• TaskIntUID
• Is Critical
• Is Milestone
• Is Summary
• Name
• Percent Complete
• Scheduled Duration
• Scheduled Start
• Scheduled Finish
• Other columns that may be relevant for you

Moving forward, as this will be a portfolio dashboard, you will need data from a project-level perspective, but, as you might want to navigate to the schedule to see task-related information, you will also need task-level data. Because of that, rename the first query and call it ‘Tasks.’ You will then have to duplicate the ‘Tasks’ table and rename it to ‘Projects,’ as shown.

It is important to make sure that the Projects table only has project-level information. To accomplish that, apply a filter by selecting the tasks with Id 0 in the TaskIntUID column.

Following these steps will guarantee that you have all the required information to build the portfolio dashboard. If you are familiar to Power Query, you can create additional columns for your tables, such as calculated columns that will determine the status of projects and tasks, as well as KPIs and other attributes that are relevant to your organization. After all of this is finished, click Close & Land.

Step #3: Creating the Dashboard

With the data loaded into the spreadsheet, make sure that the Queries & Connections dialog is available. Right-click the Tasks query and select Load To… adding the query into the Data Model.

In the Import Data dialog, select the options as highlighted below:

Once this is completed for the Tasks table, repeat the same steps for the Projects table. This is important, so you will be able to create a relationship between the two tables.

To do so, you will have to use Power Pivot (and this, then, justifies adding the tables to the Data Model 😊). From the Power Pivot tab, click Manage.

From within Power Pivot and from the Home tab, click Diagram View. Create a new relationship between the two tables by using the Source.Name column (you can drag and drop the column from one table into the other).

After the relationship between the tables is created, get started building the dashboard based on your organization’s needs. My preference is to create multiple Pivot Tables in auxiliary tabs within an Excel spreadsheet, and then use them to feed the main dashboard.

When there are updates to your schedules, you can re-export the data into the Microsoft Access database, using the approach of replacing the existing .mdb files. Then, back in Excel, you can right-click a Pivot Table and refresh it, so the latest data will be obtained, and your portfolio dashboard will be updated.

Happy reporting!