MPUG
Back
Articles

Advanced Excel and PowerBI Reporting for MS Project

Often project data is requested for stakeholders who do not have MS Project installed or are not experienced enough to work with it. MS Project reporting to MS Excel or […]

3 min read
2.5K views
V

Often project data is requested for stakeholders who do not have MS Project installed or are not experienced enough to work with it. MS Project reporting to MS Excel or other visualization software is limited. An export to MS Excel allows for the generation of only plain tables of project tasks, resources, and assignments. This can be hard for users when they wish to analyze project progress, status, forecast, etc. Visual Reports on the Report tab are closer to analytical needs; however, their customization is not easy, and the resulting Pivot Tables in MS Excel lack full functionality (internal structures with initial ‘local cube’ data are required to make a MS Excel Pivot Tables work properly).

To address this problem, I’d like to bring to your attention the reporting engine of a Construction Project add-on (CP), which was designed to help MS Project users to get comfortable and ‘self-sufficient’ generating MS Excel and PowerBI reports. It is free and can be used for any project. CP reporting generates MS Excel and PowerBI files with customizable MS Excel Pivot Tables or PowerBI visualizations based on active project data.

CP’s reporting engine follows the below process:

  1. A user uploads selected MS Project fields as row data to hidden ‘RawData’ sheet of a specially designed MS Excel template.
  2. The filled MS Excel template generates a PowerPivot model to produce all required ‘good-looking’ dimensions and measures from row data loaded from active project.
  3. Dimensions and measures are available for selection from the PivotTable fields when you insert either a PivotTable or a PivotChart into the MS Excel worksheet.
  4. Default reports contain many of the measures and dimensions commonly used in projects (Tasks, Resources, Time, Costs, Work, Duration, %Complete, Baselines etc.), and it is also possible to add new ones based on any MS Project custom fields uploaded. There is no hidden code used; everything can be viewed and modified in the PowerPivot model.
  5. PowerBI reports are generated in a similar way, but do not pull data from MS Project directly (a MS Excel report is used). PowerBI reporting opens a PowerBI template which has been specified in report settings, changes the data source to the generated MS Excel report, and then refreshes data after opening.

To utilize CP reporting, you can easily install the add-on and load the ‘Demo Project’ on the ‘CP-Construction’ tab to setup default report templates. Open your project and generate a report based on the default template by clicking on the ‘Create’ button in the ‘CP-Tools’ tab and selecting ‘Report’ from list. After opening the MS Excel report, you can modify it according to your needs and use it as new template in the future. This is done by setting up a template in report configuration. Check details here.

To make it easy for you, I’ve included the links again below. I’d love to know what you think if you utilize this add-on.