Project Management

Use this macro to filter your Microsoft Project reports

Microsoft Project features a powerful reporting capability, but you need information on resources assigned to specific sets of tasks. This macro will help you get the job done.


This article originally appeared on Builder.com’s sister site, TechRepublic.com.

One of Microsoft Project’s best features is its powerful reporting capability. For example, running the Resource Usage report will show how many hours per day or week that a resource is being used and on which tasks. This is useful information when you’re trying to figure out who is overbooked or who has more time.

In very large projects, however, it can be a pain to go through all the resources. It’s likely you want to think about just a few specific resources. Microsoft Project lets you create filters for reports to show data for just a few resources.

But what about those times when you want to see information about only those resources that are assigned to specific sets of tasks? Project’s built-in filtering doesn’t let you use filters that look at both resource and task data. So you’re stuck, right? Not entirely. The macro in Listing A  allows you to mark certain Summary tasks in your project, and it will mark all the resources that are assigned to any of the subtasks to those marked summary tasks. Then it will run a report that you specify showing only those marked resources. With this, you can see time-scaled information about all the resources that are working on a specific set of tasks.

Create the filter
The first step is to create the filter your report will use. Open the Filter dialog box by clicking Project | Filtered For and then picking More Filters from the menu. Now, click the Resource radio button, and then click the New button. This brings us to the dialog box pictured in Figure A. Simply enter the information as you see it in Figure A. This will create a filter that shows only the resources where the Flag1 field is set to Yes. Make sure you enter the name of the filter as ResourceWBSFilter.

Figure A
Filter definition


When you’re finished creating your filter, click OK, and then click Cancel on the More Filters dialog box.

Edit the report
Next you must edit a report to use your filter. Click View | Reports to bring up the Reports dialog box. Then double-click on the More category. You’ll see the Custom Reports dialog box. For this example, you’ll make a copy of the Resource Usage Report by selecting it in the list of reports and clicking the Copy button. This will open a Crosstab Report dialog box for a report called Copy of Resource Usage. You can now rename this report and set it up to use your new ResourceWBSFilter. When you’re done editing the report, your dialog box should look like the one in Figure B. Change your report's name to Resource WBS Usage Report for this example. Then, set the filter field of the report to use the ResourceWBSFilter you just created above. For this example, you’ll also check the Highlight option. This will give a report that shows all the resources, but it will highlight the ones that are assigned to your marked tasks.

Figure B
Crosstab Report dialog box


Once you’ve edited the report, click OK to close the dialog box. Then click Close to exit the Custom Reports dialog box. Click Close again to get out of the Reports dialog box.

Back to the macro
This macro is deceptively simple. The first For…Next loop simply looks through every task in the project to see if the Flag1 field is set to True. This is the flag field you would use to mark your summary tasks. Notice that the first If…Then condition you evaluate in this first loop is Not (T Is Nothing). This is always the first thing you want to check when looping through the task objects in the Tasks collection. This is because blank lines in the Gantt chart view are seen as objects, but since all of their values are null, you’ll get an error if you try to evaluate them. Checking here to see if the task object is Not Nothing will let you get around this problem. So after you make sure that the task object is really a task, look to see if the Flag1 field is True. You do this with the line that reads If T.Flag1 = True Then. If this condition is found to be true, start a second loop through a Tasks collection, but this time it is the collection of tasks that is the OutlineChildren of the current T task object (for each TT in T.OutlineChildren). In this way, you tell the macro to look at all the subtasks of the task object represented by the variable T.

Evaluate the task object again to make sure it’s not Nothing. If it’s a real task, start another loop. This time the loop is going to go through every resource object assigned to the task represented by the variable TT. It’s done like this: For each R in TT.Resources. You’ve declared a variable R of type Resource, and so now with this loop, you make R represent each successive resource in the collection of resources assigned to TT. For each of these resources, make the Flag1 field True.

Once this process is completed for every task, subtask, and applicable resource, you can run the report you created at the beginning of the article. Run the report with this line: ReportPrintPreview Name:=ReportName. Set the value of the variable ReportName to equal Resource WBS Usage Report at the beginning of the macro so this line runs the report.

After the report is run, the macro cleans up by resetting the Flag1 field for every resource back to False. The last thing the macro contains is a simple error-handling routine that will bring up a friendly message if the report you specify doesn’t exist in the project file.

You’ve created a useful filtered report that shows the resource usage information for a specific set of tasks and learned a bit about ways to access task and resource objects with VBA.

Microsoft Project tricks
Want to share a Microsoft Project tip? Post it below or e-mail it to us.

 

Editor's Picks

Free Newsletters, In your Inbox