Ask the Experts: Creating a Report with Task and Resource Data

Bob of OakBrook, IL asks: I would like to have a report that shows task name and resources assigned with their skills, department, and email addresses. How can I create this report?

Answer: This is a great idea and, yes, it can be done. When a resource assignment is made, only a few fields from the resource data is populated in the task data information. The fields that are updated per assignment (other than the assignment data) are:

  • Resource Group: frequently used for department but can be whatever the user needs.
  • Resource Name: the name of the resource.
  • Resource Initials: defaults to the first initial of the resource.
  • Resource Type: system used field.
  • Resource Phonetics: used in the Japanese version of Microsoft Project only.

The Resource Name field is the key field for resources and should remain this value. The Resource Type field is used by the system, and values can’t be entered in this column. The other three columns could be used as needed. However, look-up tables aren’t available for these fields and the consistency of the data will be up to the users. Each of the values may be sorted, filtered, and grouped. Here are some suggestions:

  • Resource Group: Most frequently used for a department in which the resource works. This could be any other value that could define a category or value for reporting. Microsoft Project has a task filter for Resource Group.
  • Resource Initials: Enter initials and substitute initials for names on reports. Another use might be skill or location. The system allows assignments to be created from this column but it’s not recommended and not the most efficient way of creating assignments.
  • Resource Phonetics. If you’re not running the Japanese version of Microsoft Project, this field may be used for any value you might need — the location where a resource resides, email address, phone number, onshore/offshore status, or any other piece of information that would add value.

Figure 1 shows an example of a resource sheet with these customized values added. To create this report, I inserted the columns into a task table and changed titles to reflect the new values. Resource Group is being used as department; Resource Phonetics is being used for email address; and Resource Initials is being used for skill.

Figure 1: A customized resource sheet.

Ask the Experts: Creating a Report with Task and Resource Data

To insert the columns and temporarily change the names for the table:

  1. Right click on a column to the right of where you would like to insert a column.
  2. Select Insert column.
  3. Select the column name from the pull down list for Field.
  4. Enter the new title in the Title field, as shown in Figure 2.
  5. Click OK.

Figure 2: Changing the name of a column after it has been inserted into a resource table.

Ask the Experts: Creating a Report with Task and Resource Data

The final report will look like the one shown in Figure 3.

Figure 3. The modified report.

Ask the Experts: Creating a Report with Task and Resource Data

The fields may then be sorted, filtered, or grouped as necessary to adjust the report. As the resource assignments change, the data in the columns will change also. To send an email to everyone assigned to a task, copy and paste the value of the email field into Outlook.

Written by Ellen Lehnert
Ellen Lehnert, PMP, Microsoft Project MVP, MCP, is a independent consultant and trainer on Microsoft Project and Project Server. She has taught Microsoft Project over 400 times and is the author of  MS Project 2010 and 2013 published courseware. Ellen is also a contributor and tech editor for many reference books, a developer for the Microsoft Project certification tests and is a frequent meeting speaker for Microsoft, MPUG and PMI. Contact Ellen at ellen@lehnertcs.com.
Share This Post
Have your say!
00
2 Comments
  1. I don’t think you can do this standardly. It would require VBA code to pull the data from a task field to a resource field.

  2. The task level roll up is for tasks. What you are looking for would have to come from the assignment data base and because of this, you will not be able to produce the report with MSP. You can get the total of work for a summary level but not broken down by resource total. Sorry.

Leave a Reply