When it comes to program and portfolio management of projects, a Gantt chart view is often desirable with milestone and other key dates superimposed. Within the Microsoft suite of products and features, there are several options to produce Gantt charts of your portfolio.
However, what options are there for printing out Gantt charts when using Microsoft Project Online/Server? This article is going to consider some options.
Portfolio Reporting Options
Listed below are a few methods that can be utilized to view a Gantt chart of a desired program or portfolio:
- A Project Center view
- A Power BI report utilizing a Gantt visualization
- Custom reporting – typically SSRS reports.
This requires for a data warehouse solution to be developed and the availability of a Reporting Server, as Project Online does not natively support SQL Server Reporting Services (SSRS). However, PowerBI Premium includes an SSRS report server.
- Project Roadmap, which is a recently released product from Microsoft.
- Excel or similar. This involves extracting data from Project Online using oData feeds and requires advanced Excel skills.
- MS Project. The later part of this blog will discuss this option in more detail.
Working Through the Options
I have often noticed when on-site with a client, a large print out of a Gantt chart view of the PMO’s portfolio of projects. Quite often these are generated in Excel or in PowerPoint; I have even seen hand drafted versions using a whiteboard and post-it notes which are updated each week at a team meeting. The point of note here is that considerable effort has been expended to extract information out of Project Online into Excel (or similar) just so the format could be tweaked and then printed.
Using a Project Center view or PowerBI are not really plausible options for printing out a Gantt chart view of a portfolio. Those two technologies are designed for on-screen interaction; not for printing on a large A0 form factor.
SSRS reports are certainly printer friendly and highly customizable. However, since SSRS reports aren’t natively supported on Project Online and require an entire custom reporting warehouse and report server to be made available, this option may be cost prohibitive. Also, data integrity issues will likely need to be approved by your IT security team (sigh!). Additionally, the technology looks dated compared to PowerBI and suffers from the inability to be able to quickly slice and dice which projects to include in the report; instead, the parameters need to be reset and the report refreshed which may take seconds to minutes to complete.
Project Roadmap is a recently released tool by Microsoft, specifically designed to generate a portfolio view of projects that have the capability to incorporate multiple Project Online instances; and in the future, other sources will be supported. Multiple roadmaps can be created and maintained. However, once again this tool is designed for on-screen interaction; not for printing out.
The last option listed above was Microsoft Project. Now, to be clear, MS Project is a project scheduling tool and a very powerful tool at that. The inbuilt reports and custom views that can be created make it a powerful reporting tool and certainly supports printing out to a large A0 printer. Let’s not forget the ability to have multiple timeline bars as well. However, typically MS Project client is used to manage individual projects or perhaps a master project (that contains many sub-projects). An easy to use Program/Portfolio view is not what the tool is designed for.
However, that being said, the remaining part of this blog is going to focus on some options to leverage MS Project in concert with either VBA macros or server-side custom scripts to allow MS Project to analyse your entire portfolio of projects.
The basic concept to leverage MS Project to view an entire portfolio of projects is to develop a custom solution that interrogates Project Online and retrieves details of the entire portfolio and then create a single project that contains tasks that represent projects and/or key dates and milestones.
An example is shown below.
To build this “PortfolioView”’ project, I have used two different approaches, each with their pro’s and cons. These are:
- Utilize a VBA macro to query Project Online using oData queries. A user form can also be utilized to allow selection of which projects to display based on certain parameters. For example, only display projects in “Construction” phase and where the Sponsor is Peter Williams. Additionally, include key milestones within the selected projects. If there are hundreds of projects in the portfolio though, then it may take 5 to 10 minutes to populate the schedule after executing the macro.
The development of such a macro is no trivial matter, it does require advanced skill with VBA development. In the appendix to this blog, I have included core parts of the VBA code that retrieve Project Online data using oData feeds. Using oData feeds is MUCH faster than other alternative methods.
- Use a PowerShell script that executes on a remote server to update a single project with all the desired Project, Milestones and other key tasks from the entire portfolio. The script can be scheduled to run every “xxx” minutes.No matter which of the two approaches are taken, custom views within the PortfolioView project can now be readily set up to display the projects/milestones desired. Once again, VBA code and user forms can be leveraged to create/update such views dynamically is so desired.
As you can see, there are many options in producing a portfolio Gantt chart, and several of these options are well documented online. This may be the first time though that you have read about leveraging the capabilities of MS Project for portfolio Gantt charts and general portfolio analysis. If you currently considering Portfolio reporting, then I encourage you to reach out to EPM Partners to discuss your needs further.
Below is some sample VBA code that can be used to interrogate Project Online oData feeds.