December 04, 2014

MS Project Reports Tutorial : How to create reports and dashboards

The Microsoft Project 2013 has some fantastic options for generating reports. There are a lot of default or template reports which you can generate at click. I will let you explore those on your own in this tutorial we will talk only about customization of reports.



MS Project Report
MS Project Report
MS Project Reports can seem to be complex when you look at it for the first time but once you are understand the concepts and get your basics right you will be able to use the reports a lot easily. To make the tutorial more effective it is divided into 3 steps.  First step - Creating the custom reports, will discuss how you can create reports and the basics about reporting. Second step - Configuration of the reports and step three - generation of the reports.

How to create a New Custom Report in MS Project

Clicking on the New Report option will give you a few choices. I prefer the option with the Chart as we do not plan to use any table today. You should see the view as per the image above. Notice the columns to the right. Please save the report as Sample. The fields option in Microsoft Project Reports is the most important setting as the setting in this column will drive your reports. You basically have two views - Tasks and Resources. It is very similar to pivot tables used in excel. 

MS Project Report Options
MS Project Report Options
Clicking on tasks will show you the attributes associated (task name, duration, work etc) with tasks and clicking on resources will show attributes (resource name, work, and remaining availability) related to a resource. There is a bit of overlap in the fields but you will not be able to get a full overlap. You can select a category of data you want to display. There are a few categories in both the views. One of the key category is time. The time option allows to report based on a time. So, you can display the effort spend over days, weeks, months, years etc in your report.

How to configure a report in MS Project

After you have got the basic template ready you can then add different graphs or tables to the report depending on your need. In this section we will look at adding a new chart, using the time option and formatting the graphs. You can add a graph by using Chart option from the design tab. If you don’t see the design tab then click on of the already build chart as the design tab is context sensitive. Microsoft Project 2013 gives you a vast range of graph templates which you can choose from - column, pie, line, bar, area, surface, radar and combo. It is pretty much everything that excel provides. I mostly use column or pie as it is easy for people to understand. As mentioned earlier the time option lets you select the duration of the time to be reported. The units is whether you want to see weeks, months of years etc. The date format you like to see. 

MS Project Report Timescale
MS Project Report Timescale
Count defines the count for each unit. If the units is weeks and count is 2 then the report will populate data at the interval of two weeks. Start and Finish are the start and end dates. You don't have to be exact when selecting dates. You can enter rough dates and MS Project 2013 will pick the nearest dates. Microsoft Project 2013 also provides a wide range of formatting capabilities in the design tab. The options allow to change the basic format like color, font, legends etc.  You can also change the chart type whenever you wish to see different views. You can change the chart background color. There are some default formats and report templates which are pretty savvy and can be used without any tutorials.  You can also add new chart elements like legends, titles and you can also apply filters to the data used . The options can be found in the design tab or are displayed when you hover your mouse over the chart.

How to run a custom report in MS Project

When running a project I like to know how much work is the project delivering, how much is each team member delivering, how much work are we doing in each phase and finally is there any excess capacity. And the good news is that Microsoft Project 2013 allows you to generate a all the reports in a click. We will now see how we can generate Team Workload Report, Work on Month by Month basis, Task Effort and Excess capacity.  We will create all of them on the sample report by adding 4 distinct graphs by the method described in step 2. 


MS Project Reports
MS Project Reports
You can generate the Team work load report by selecting the task view, category as "Name" and in the select fields column select work field from the work option. You can generate the team Work on Month-by-Month report basis by selecting the resource view, category as time (monthly interval) and work field from the work options. You can generate the Task Effort or Effort on Project Phases by selecting the tasks view, category as name and work field from the work options. You can generate the Excess Capacity Report by selecting the resources view and then selecting the time category. 

I prefer selecting weeks and have the total duration of roughly one month as I prefer to see the free capacity for next month. Select the Remaining Availability from work option. Finally, after creating all the above graphs by following the tutorial you can save the Sample. The advantage is that the next time you need these MS Project Reports you will have to go to Reports --> Custom Reports and select the one that you created and it will rendered instantly. You can also use the copy report option in the design tab to copy the output to clipboard and then to any other document or email you want. This option is handy when you want to paste from Microsoft Project to another application. If you like this tutorial then please share it with your friends and your colleagues.

MS Project Report Sample
MS Project Report Sample

Resource Management Dashboard  with MS Project Reports

We will assume we have a common resource pool and master project plan which includes all the three projects. It is recommended that you read the below articles before you proceed with creating the reports.  

Step 1: Create filters and groups for resources

This is the most important step as it will lay the foundation for the dashboard to be generated. You will need to create one filter each for the teams and a group on Resource Groups. You will need an IT Team, Legal Team, OPS Team and Services Team filter. Also, a group based on Resources Group and Resource Name. Please see the below articles on how to add filters and groups.

Step 2: Creating the dashboard reports

We will now look at how we can create 3 different sets of reports for resource management dashboard. Team Work Load Report - A report which will provide a view of the the team work load. Resource Allocation Report - This report will help in understanding how the resources are allocated. Resource Availability Report - This report will show if any resources are available.

Step 3: Team Work Load Report

To add the team workload dashboard click on Design and a add a new Chart. Select the type of report you need to use. And select the fields as shown to the image to the right to see the report which is based on resource groups. The report will be based on the group that you select. You can add formatting options by right clicking on the report. You can add legends, chart titles, data values etc.

Team Workload Report
Team Workload Report

Team Work Load Report
Report Config

Step 4: Resource Allocation Report - Entire Resource Pool


The resource allocation report on the dashboard will give a view of how much work each resource has been assigned. Two values are shown for each resource - Work and Remaining Work. Work is the work assigned and the remaining work is the work still remaining. This report will help a project manager in deciding if any of the resources are overloaded. Select the fields as shown in the image to the left. The key is to select the options right as that will decide what is displayed on the report.

Resource Workload Report
Resource Workload Report
Resource Allocation Report
Report Config

Step 5: Resource Allocation Report - Time phased Report for Individual Teams


The above report on the dashboard gives you a good idea about how all the resources are allocated. The report is a very high level report and does not tell you when the resource is free.  To find out when the resource is free we will use the Time option. Also remember that if you try to do this with the entire resource pool the report or the chart might get very big and become difficult to read. In this example I have used the IT Team filter and the group by ResourceName which will group the data by resource names.

Resource Allocation Report
Resource Allocation Report
First report on the dashboard below gives a picture of the availability of resource times. Second Report the Work Allocated gives an idea about how much work each resource has been allocated.  The second report is generated with all the same selections as field dialog expect the field to be displayed is the Work Assigned. Comparing both the graphs will give a project manager a clear picture about the resource allocations. One of the settings is the time phase. The time phase can be selected by clicking on Edit beside the in the Fields options box. Clicking on edit will open the Edit Timescale dialog. Units will be the duration, in this case it is months, count the number of units(2 months, week etc) then Start and Finish Date.
Time Phase Settings
Time Phase Settings

Report Config
Report Config

Step 6: Resource Availability Report

This is one of the key reports on the dashboard as most of the managers and senior management are interested in finding out which resources are available. We will look at two versions of the report - one at team level and one at resource pool level. The first report can be generated with the same options as above Resource Allocation report with the only difference being the display field selected as Remaining Availability. The second report can be generated by selecting the options as shown in the image to the right. The key difference would be to select All Resources and then group by Resource Group.

Resource Availability Report
Resource Availability Report

Resource Availability Report
Resource Availability Report


MS Project Report Config
MS Project Report Config

Multiple Project Dashboard with MS Project Reports

I understand that there are several tools available for Project Portfolio Management (PPM) which can do this task. But the focus of this article will be to enable someone using Microsoft Project to report on multiple projects. The Project Portfolio Dashboard will have 4 sections : Project Progress Card, Project Work Load, Budget Overview and Upcoming Milestones. This technique is based on the assumption that you have a Master Project Plan for all your projects. 

Multiple Project Dashboard
Multiple Project Dashboard
If you need to know how to setup a Master Project plan please read my article on How to create a Master Project plan in Microsoft Project. I would also recommend that you setup a resource pool with Master Project so that you can report on all your resource as well. Please see my article How to Create a Resource Pool with Microsoft Project to understand how you can create a resource pool. I am using a Master Project Plan with 3 projects - New Business, Project Template and New Product Launch. 

Microsoft Project allows customization of reports which you can use to suit your needs. Create a new custom report by going to the Reports Tab in Microsoft Project and click on New Report and then select a blank report. You will be asked a Report Name please enter a suitable report name.

Project Progress Report

Project Progress Report MS Project
Project Progress Report MS Project

Please click on the chart(Add Chart) from the ribbon and select a column chart. The project progress report will show the % complete. In the fields section please select only % Complete under Numbers.
Please delete all the other fields. In the filter drop down select All Tasks, select Level 2 in Outline Level 2. Check the Show Hierarchy option.  Please move this to the right side of the report.

Project Workload Report

Project Workload Report
Project Workload Report

Please click on the chart(Add Chart)  from the ribbon and select a column chart.  After the graph is created you should be able to see all the projects names present in the master project plan.  You can remove all the fields except Work and Remaining Work. Please move this graph to the right side of the report.

Budget Overview

Project Budget Overview
Project Budget Overview
The budget overview will give everyone an idea of the the project budgets. Please click on the Chart(Add Chart)  from the ribbon and select a column chart. The graph will have the budget total cost (cost) and remaining cost. I have used the column chart. You can select the report format under design tab. Select the Cost and Remaining Cost under the Cost option. Delete the other fields from the fields list.

Upcoming Milestones

Project Milestones
Project Milestones

The milestones table will display the milestones in all the projects within the master project plan. To add a table click on Table in the design tab. The key step is to select Milestones in the Filter drop down so that the table shows only the Milestone tasks. By default a few columns will be added. Please remove all the columns except Name and Finish Date. For Filter drop down select Milestones, Group should be No Group, Outline Level should be All Subtasks.  You can select the table format under Design Tab which appears under Table Design.

Generating the Multiple Project Status Dashboard

Generating the report is the easiest task. After adding all the above graphs and tables please save the report. The report will be available under the Custom Reports in the Reports tab. Whenever you need to generate the report you need click on the report name under Custom Reports and the report will be generated in a click.Using the Copy Report option under the Design Tab you can copy your report to your presentation/dashboards and any other document.