Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

Listing Number of Task Assignments for Resources

 

Version 1, Published on October 22nd, 2015

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is related to all

standalone desktop versions of MS Project Professional/Standard supporting the custom field formulas feature.

 

In this article, we will define a resource usage view along with a resource table and group in order to display the number of task assignments for the resources in the simple schedule shown below:

 

  

The following is the Group Definition dialog box for the resource group used in the example:

 

 

 

The Text17 field is just a blank field with no data, which is used only to obtain the top group summary row. The Text15 field (which is is used as the second grouping criterion in the group definition) contains the formula below (select the option Use formula):

 

Switch( Instr( [Name],"Text15" ), "No. of Assignments for " & Mid( [Name], 9 ), Instr([Name],"Text17"),

"TOTAL ASSIGNMENTS (Resource Count: " & [Resource Count] & ")",True,[Name])

 

When the group is applied to the table, the formula above will return the expressions related to the group summary rows, otherwise it will return the resource names since the always-true part of the Switch function will be evaluated. The Table Definition dialog box below shows the resource table that we will use in this example:

 

 

In the table, the cells under the Title column for the Text15 and Number1 fields are not blank; instead they contain a space character entered by hitting the spacebar on the keyboard in order to hide the corresponding field names in the column headers of the NO_OF_ASSIGNMENTS table.

 

The Number1 field contains the value 1 in the formula box and the following options are selected in the related sections of the Custom Fields dialog box:

 

  • Calculation for task and group summary rows  - Rollup: Sum

  • Calculation for assignment rows - Roll down unless manually entered

 

We do not choose the “Rollup: Count All” option since we do not want to include the resources with no task assignments in the final sum. The following is the definition of the custom resource usage view displaying the number of assignments:

 

 

The view name is included in the list of views displayed in the right-click shortcut menu of the active view bar, as a result, we can quickly see the number of assignments in the schedule by just switching to the NO_OF_ASSIGNMENTS view. Also select Level 2 command in the Outline drop-down menu in the Data group of the VIEW tab will hide task assignment rows, so we will see only the group-summary rows, as shown below:

 

 

Note that the Unassigned (i.e., the symbolic resource name under which all the tasks with no resource assignments are listed) is not counted and the corresponding group summary row  “No. of Assignments for No Value” shows 0 even though we have a task with no assignment in the schedule. We will not see this line if the active project plan has no such tasks. And also note that the resources with no tasks assigned are neither counted nor displayed in this view. A comparison between the number of resources listed and the resource count value shown in the title will give the number of resources with no task assignments; here, we have 5 minus 4, that is, just one resource with no task assignments.

 

The active project plan will keep the outline setting but we need to set it again when we close and reopen the file; and also we need to switch the group off and on again in order for MS Project to recalculate and replace #ERRORs with data. While making changes in the formula for Text15 (or any formula also used for the group summary rows), it is better to delete the custom field first by using the Delete button and then reenter the whole formula modified to the formula box in the Custom Fields dialog box.

 

A more quick way to see the same results would be as follows: define the Number1 field the same as above, then apply AutoFilter group to the resource name field. Open the AutoFilter group definition dialog box; insert Text17 and select the checkbox Group assignments, not resources. And finally set the outline level to Level 2.

 

 

 


Revision History:

Version 1 - October 22nd, 2015 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.