How to Print a Program/Portfolio View of Projects

Posted By Posted by: Peter Williams on February 6, 2019

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:

  1. A Project Center view
  2. A Power BI report utilizing a Gantt visualization

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

  4. Project Roadmap, which is a recently released product from Microsoft.

  5. Excel or similar. This involves extracting data from Project Online using oData feeds and requires advanced Excel skills.
  6. 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:

  1. 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.
  2. 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.

Conclusion

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.

Appendix:

Below is some sample VBA code that can be used to interrogate Project Online oData feeds.

Option Explicit On
Option OnPrivate Module
' References that need to be added:
' Microsoft XML, v6.0
' Microsoft Scripting Runtime

Const ODataErrorFirst As Long = 100
Const ODataCannotReadUrlError As Long = ODataErrorFirst + 1
Const ODataParseError As Long = ODataErrorFirst + 2


' XML namespaces:
Const AtomNamespace As String = "http://www.w3.org/2005/Atom"
Const ODataNamespace As String = "http://schemas.microsoft.com/ado/2007/08/dataservices"
Const ODataMetadataNamespace As String = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"


Function GetODataCount(ByVal strUrl As String)
    Dim hReq As MSXML2.XMLHTTP60
    
    Set hReq = CreateObject("MSXML2.XMLHTTP")
    
    With hReq
        .Open "GET", strUrl, False
        .send
    End With

    GetODataCount = hReq.responseText

End Function



Function ODataReadUrl(ByVal strUrl As String) As MSXML2.DOMDocument60
    Dim objXmlHttp As MSXML2.XMLHTTP60
    Dim objResult As MSXML2.DOMDocument60
    Dim strText As String

    'Make a request for the URL.
    'AL - Note I tweaked the XMLHTTP TO XMLHTTP60
    Set objXmlHttp = New MSXML2.XMLHTTP60
    objXmlHttp.Open "GET", strUrl, False
    objXmlHttp.send

    If objXmlHttp.Status <> 200 Then
        Err.Raise ODataCannotReadUrlError, "ODataReadUrl", "Unable to get '" & strUrl & "' - status code: " & objXmlHttp.Status
    End If

    ' Get the result as text.
    strText = objXmlHttp.responseText
    Set objXmlHttp = Nothing

    ' Create a document from the text.
    Set objResult = New MSXML2.DOMDocument60
    objResult.LoadXML strText
    If objResult.parseError.ErrorCode <> 0 Then
        Err.Raise ODataParseError, "ODataReadUrl", "Unable to load '" & strUrl & "' - " & objResult.parseError.reason
    End If

    Set ODataReadUrl = objResult
End Function


Function ODataReadFeed(ByVal objFeed As MSXML2.IXMLDOMElement) As Collection
    Dim objResult As Collection
    Dim objChild As MSXML2.IXMLDOMNode
    
    Set objResult = New Collection
    
    Set objChild = objFeed.FirstChild
    While Not objChild Is Nothing
        If objChild.NodeType = NODE_ELEMENT And
            objChild.NamespaceURI = AtomNamespace And
            objChild.baseName = "entry" Then
            objResult.Add ODataReadEntry(objChild)
        End If
        Set objChild = objChild.NextSibling
    Wend
    
    Set ODataReadFeed = objResult
End Function





' Given an OData entry element, reads the properties into a dictionary.
Private Function ODataReadEntry(ByVal objEntry As MSXML2.IXMLDOMElement) As Scripting.Dictionary
    Dim objResult As Scripting.Dictionary
    Dim objChild As MSXML2.IXMLDOMNode
    Dim baseName As String
    
    Set objResult = New Scripting.Dictionary
    
    Set objChild = objEntry.FirstChild
    While Not objChild Is Nothing
        If objChild.NodeType = NODE_ELEMENT And
            objChild.NamespaceURI = AtomNamespace Then
            baseName = objChild.baseName
            If baseName = "id" Or baseName = "title" Or baseName = "updated" Then
                objResult.Add "odata_" & baseName, objChild.Text
            ElseIf baseName = "link" Then
                ' TODO: handle this element as necessary
            ElseIf baseName = "category" Then
                ' TODO: handle this element as necessary
            ElseIf baseName = "author" Then
                ' TODO: handle this element as necessary
            ElseIf baseName = "content" Then
                ODataReadContent objChild, objResult
            End If
        End If
        Set objChild = objChild.NextSibling
    Wend
    
    Set ODataReadEntry = objResult
End Function

Private Sub ODataReadContent(
        ByVal objContent As MSXML2.IXMLDOMElement,
        ByVal objEntryDictionary As Scripting.Dictionary)
    Dim objChild As MSXML2.IXMLDOMElement
    Dim objProperties As MSXML2.IXMLDOMElement

    ' Look for the m:properties element.
    Set objProperties = Nothing
    Set objChild = objContent.FirstChild
    While Not objChild Is Nothing
        If objChild.NodeType = NODE_ELEMENT And
            objChild.NamespaceURI = ODataMetadataNamespace And
            objChild.baseName = "properties" Then
            Set objProperties = objChild
        End If
        Set objChild = objChild.NextSibling
    Wend
    
    ' Read all properties from the m:properties element.
    If Not objProperties Is Nothing Then
        Set objChild = objProperties.FirstChild
        While Not objChild Is Nothing
            ' TODO: handle null properties and complex types
            If objChild.NodeType = NODE_ELEMENT And
                objChild.NamespaceURI = ODataNamespace Then
                objEntryDictionary.Add objChild.baseName, objChild.Text
            End If
            Set objChild = objChild.NextSibling
        Wend
    End If
End Sub


Function CreatePortfolio()
    ProjectName = ActiveProject.Name
    ProjectName2 = "PortfolioProject"

    FilterApply Name:="All Tasks"  
    'Configure the following parameters
    PWAURL = "https://clienttenancy.sharepoint.com/sites/pwa"
    ODataURL = PWAURL & "/_api/ProjectData/"
    proj_odataurl = ODataURL & "Projects?$select=ProjectId, ProjectName,,ProjectStartDate, ProjectFinishDate, ProjectWork, ProjectActualWork&$filter=ProjectName ne 'Timesheet Administrative Work Items' and ProjectName ne '" & ProjectName2 & "'&$orderby=ProjectName"
    projCount_odataurl = ODataURL & "Projects/$count/?$filter=ProjectName ne 'Timesheet Administrative Work Items' and ProjectName ne '" & ProjectName2 & "'"
    ' #######################################################
    ' #### Start fill of objEntries_Proj_All array ##########
    ' #######################################################
    lngProjCountOData = GetODataCount(projCount_odataurl)
    Debug.Print "Number or Projects: " & lngProjCountOData
    intPages = Round((lngProjCountOData / 200) + 0.5)
    intPage = 0
    lngSkip = 0

    Do
       Set objDocument_Proj = ODataReadUrl(proj_odataurl & "&$top=200&$skip=" & lngSkip)
       Set objEntries_Proj = ODataReadFeed(objDocument_Proj.DocumentElement)
       If intPage = 0 Then
            Set objEntries_Proj_All = objEntries_Proj
       Else
            For Each objEntry_Proj In objEntries_Proj
                objEntries_Proj_All.Add objEntry_Proj
            Next objEntry_Proj
        End If
        lngSkip = lngSkip + 200
        intPage = intPage + 1
    Loop While intPage < intPages
    ' CONTINUE THIS FUNCTION FROM HERE TO MEET YOUR REQUIREMENTS

End Function

Blog Posted In Blog Posted In: Blog, How to, Reporting
Blog Posted In