By Dale Howard, Microsoft Project MVP
In the 2013 and 2016 versions of Microsoft Project, Microsoft has eliminated the old paper-based reports that had been included in the software for many years, and has replaced them with the Dashboard Reports feature. Although many of the paper-based reports were replaced with corresponding Dashboard Reports, not all of them were replaced. One of the missing reports is the Who Does What When report, which cannot be created as a Dashboard Report because it displays timephased Work data. If you are willing to do a little bit of work, you can create the Who Does What When report as an Excel Visual Report instead.
To create a Who Does What When report as an Excel Visual Report, complete the following steps in either Microsoft Project 2013 or 2016:
- Open a project and then click Report > Visual Reports.
- In the Visual Reports – Create Report dialog, click the Select level of usage data to include in the report pick list and select the Weeks item on the list.
- In the Visual Reports – Create Report dialog, click the New Template button, as shown in Figure 1.
Figure 1: Visual Reports – Create Report settings
- In the Visual Reports – New Template dialog, click the Select Data Type pick list and select the Assignment Usage item on the list, as shown in Figure 2.
Figure 2: Select the Assignment Usage item
- In the Visual Reports – New Template dialog, click the OK button. Microsoft Project will create a new visual report in Excel, containing a blank PivotTable on the left and a PivotTable Fields sidepane on the right, as shown in Figure 3.
Figure 3: Blank PivotTable and PivotTable Fields sidepane
- In the PivotTable Fields sidepane, scroll down the list of fields and then drag the Resources field to the FILTERS drop area. Doing this will allow you to filter the Visual Report by resources, if desired.
- In the PivotTable Fields sidepane, drag the Tasks field to the ROWS drop area.
- In the PivotTable Fields sidepane, drag the Weekly Calendar field to the COLUMNS drop area.
- In the PivotTable Fields sidepane, drag the Work total field to the VALUES drop area. Figure 4 shows the initial setup in the PivotTable Fields sidepane.
Figure 4: Initial setup of the report
- In the task area of PivotTable, click the Expand (+) button to the left of each task name to completely expand the tasks to the subtask (detailed task) level of data.
- In the time area of the PivotTable, click the Expand (+) button to the left of each year and quarter to expand the time data to the Weeks level of data.
- Click the Design tab to display the Design ribbon.
- In the Layout section of the ribbon, click the Subtotals pick list button and select the Do Not Show Subtotals item on the menu.
- In the Layout section of the ribbon, click the Report Layout pick list button and select the Show in Compact Form item on the menu. Your Excel Visual Report should be similar to the one shown in Figure 5.
Figure 5: Completed Excel Visual Report
- Click File > Export > Change File Type.
- In the Change File Type section of the Backstage, select the Template (*.xltx) item and click the Save As button, as shown in Figure 6.
Figure 6: Export the Excel workbook as a template
Note: Microsoft Excel should navigate to your default Templates folder. If the software does not automatically navigate to your default Templates folder, you will need to navigate to this folder manually at C:\Users\YourUserName\AppData\Roaming\Microsoft\Templates.
- Name the Excel template Who Does What When Report and click the Save button.
- When prompted in the confirmation dialog shown in Figure 7, click the Yes button.
Figure 7: Confirmation dialog
- You now have an Excel Visual Report template that you can use to quickly create your Who Does What When report and then to filter it by resource, if desired. The next time you want to create this new Excel Visual Report, you will find the report listed in the Visual Reports – Create Report dialog, such as shown in Figure 8.
Figure 8: New report shown in the dialog
- To filter the task list by resource, click the pick list button in the Resources section of the PivotTable (upper left corner of the PivotTable). Expand the All list to show every resource name, select the resource whose tasks you want to display, and then click the OK button. One limitation of this Excel Visual Report, of which there is nothing we can do, is that it is not possible to display dates in the Time column headers. The best we can do is to show Week 1, Week 2, etc. Figure 9 shows the completed Who Does What When report, filtered for the tasks assigned to Mickey Cobb.
Figure 9: Who Does What When report
About Dale Howard, Director of Education, Sensei Project Solutions
Dale Howard is a seasoned training professional who is approaching 30 years of technical training experience. He has taught students how to effectively use every version of Microsoft Project beginning with version 4.0 for Windows 95, and every version of the Microsoft EPM tool beginning with Project Central in the year 2000. Dale possesses the coveted Project MVP title and is one of only 64 Project MVPs in the entire world. He is the co-author of 20 books on Microsoft Project and Project Server. Dale is known for high-energy, highly interactive style of presenting and teaching. He was voted the “Best Presenter” by conference participants at the Project Conference in 2012.
About Sensei Project Solutions
Sensei Project Solutions, a Finalist for the 2017 Microsoft Project and Portfolio Management (PPM) Partner of the Year, focuses on bringing Instant Productivity to your team. Our mission is to help individuals and organizations be more productive so that they can achieve their greatest potential. As a Gold certified Microsoft Partner and Registered Education Provider (R.E.P.) with the Project Management Institute (PMI®), Sensei offers a complete set of services and products for a successful Microsoft PPM deployment. Our guiding principles for Proactive PPM follow best practices and industry standards aligned with the Project Management Institute (PMI) and Gartner, enabling organizations to manage resource demand, obtain business intelligence that facilitates better decision making, increase business effectiveness by easily connecting people, and become self-sufficient with PPM processes and solutions. In short, Sensei helps organizations achieve Instant Productivity.