none
How to use Custom Task Field value in Custom Resource Field Formula? RRS feed

  • Question

  • Hi,

    I've posted this question in Stack Exchange and someone suggested that I should be asking it here. I'm looking for a way to calculate the workload of resources using Microsoft Project. The workload needs to be calculated based on the urgency and complexity of the tasks assigned to the resource. I've managed to create custom Task Fields named Urgency and Complexity. I've also managed to create a custom Resource Fields named Workload. However, I couldn't find a way to use the value of Urgency and Complexity to calculate the value of Workload. Is it possible to access the value of the Urgency and Complexity fields using a formula in the Workload field?

    Regards,
    Amir

    Edit:
    I'm using Microsoft Project 2007.

    • Edited by Amir Syafrudin Saturday, March 28, 2015 3:13 PM Added version information
    Saturday, March 28, 2015 3:12 PM

Answers

  • Amir,

    Unfortunately no. Custom fields can only have formulas for task and resource objects, not assignments objects. But you can do what you want with VBA.

    However, you say your Urgency and Complexity custom fields are unique for each task and using those you want to calculate a Workload value for each resource. Normally a resource will have multiple tasks so how does the Urgency and Complexity values of each of those tasks calculate a Workload factor for each resource? For example, let's say Resource A is assigned to tasks 1 and 2. Task 1 has a urgency of 5 and a complexity of 10 while task 2 has an urgency of 3 and a complexity of 1. How then do those values calculate the Workload for Resource A?

    I think what you really want to do is to calculate a Workload factor for each assignment of each resource. Using the example above, Resource A's assignment on task 1 will be one value of Workload and resource A's assignment on task 2 will be another value of Workload. You may or may not want to calculate a single weighted value of Workload for each resource (i.e. weighted Workload values of all assignments for each resource).

    Okay, given all that prelude, you might want to check out FAQ 37 on the MVP website at, http://project.mvps.org/faqs.htm#Custom%20Fields%20in%20Tables.

    Hope this helps.

    John

    Saturday, March 28, 2015 3:40 PM
  • Amir,

    That's right, you cannot use the rollup feature for custom fields since assignments cannot be customized with a formula, as I noted in my initial response. That applies for any version of Project, not just Project 2010 and 2013. However, the solution is very simple. Since you are already using VBA to calculate the assignment workload, you can also use VBA to do the rollup on the Resource Usage view.

    I don't know how you plan on weighting the assignment workload at resource level, but if it is a simple sum of all the assignment workload values, then the following macro will do the rollup. This code could be incorporated into the workload calculation macro or run separately, it doesn't matter. Edit it as needed if you are not using a simple sum or a different field. Note, you cannot sum us a text field, that's why the code uses an extra number field.

    Sub ResUseViewRollup()
    Dim r As Resource
    Dim a As Assignment
    For Each r In ActiveProject.Resources
        For Each a In r.Assignments
            r.Number1 = r.Number1 + a.Number1
        Next a
    Next r
    End Sub

    Hope this helps. If this answered your question, please mark my responses as the answer.

    John


    Monday, March 30, 2015 3:13 PM

All replies

  • Amir,

    Unfortunately no. Custom fields can only have formulas for task and resource objects, not assignments objects. But you can do what you want with VBA.

    However, you say your Urgency and Complexity custom fields are unique for each task and using those you want to calculate a Workload value for each resource. Normally a resource will have multiple tasks so how does the Urgency and Complexity values of each of those tasks calculate a Workload factor for each resource? For example, let's say Resource A is assigned to tasks 1 and 2. Task 1 has a urgency of 5 and a complexity of 10 while task 2 has an urgency of 3 and a complexity of 1. How then do those values calculate the Workload for Resource A?

    I think what you really want to do is to calculate a Workload factor for each assignment of each resource. Using the example above, Resource A's assignment on task 1 will be one value of Workload and resource A's assignment on task 2 will be another value of Workload. You may or may not want to calculate a single weighted value of Workload for each resource (i.e. weighted Workload values of all assignments for each resource).

    Okay, given all that prelude, you might want to check out FAQ 37 on the MVP website at, http://project.mvps.org/faqs.htm#Custom%20Fields%20in%20Tables.

    Hope this helps.

    John

    Saturday, March 28, 2015 3:40 PM
  • John,

    Thanks for the reply. The FAQ you pointed out really helped. At this point, I've managed to calculate the Workload for each assignment of each resource and show them in the Resource Usage view. However, the sum of Workload for each resource is not summed. How can I do this? I've tried setting the "Calculation for task and group summary rows" option to "Rollup" and set the value to "Sum", but it's not working.

    I look forward for more insights.

    Monday, March 30, 2015 3:24 AM
  • Amir,

    Unfortunately, the rollups in the Resource Usage don't work and continue not to work in 2010 and 2013. You would have to use Visual Reports to do the aggregation.

    Treb Gatte, Project MVP | Blog | Twitter | YouTube Channel

    Monday, March 30, 2015 5:52 AM
    Moderator
  • Amir,

    That's right, you cannot use the rollup feature for custom fields since assignments cannot be customized with a formula, as I noted in my initial response. That applies for any version of Project, not just Project 2010 and 2013. However, the solution is very simple. Since you are already using VBA to calculate the assignment workload, you can also use VBA to do the rollup on the Resource Usage view.

    I don't know how you plan on weighting the assignment workload at resource level, but if it is a simple sum of all the assignment workload values, then the following macro will do the rollup. This code could be incorporated into the workload calculation macro or run separately, it doesn't matter. Edit it as needed if you are not using a simple sum or a different field. Note, you cannot sum us a text field, that's why the code uses an extra number field.

    Sub ResUseViewRollup()
    Dim r As Resource
    Dim a As Assignment
    For Each r In ActiveProject.Resources
        For Each a In r.Assignments
            r.Number1 = r.Number1 + a.Number1
        Next a
    Next r
    End Sub

    Hope this helps. If this answered your question, please mark my responses as the answer.

    John


    Monday, March 30, 2015 3:13 PM
  • John,

    Thank you again for your assistance. Your VBA code really helped. There's just one tiny adjustment I'd like to suggest to your code:

    Sub ResUseViewRollup()
    Dim r As Resource
    Dim a As Assignment
    For Each r In ActiveProject.Resources
        r.Number1 = 0
        For Each a In r.Assignments
            r.Number1 = r.Number1 + a.Number1
        Next a
    Next r
    End Sub

    The "r.Number1 = 0" is used to reset the Number1 field of each resource to zero so that it won't accumulate the Workload from previous run.

    At this point, the only thing left to do is to make the code run automatically when the Task Usage or Resource Usage view is chosen. Any clues on this one? I'm sort of clueless when it comes to VBA.

    Tuesday, March 31, 2015 6:20 AM
  • Amir,

    You're welcome and thanks for the feedback.

    Your edit to the code is a good one. Normally I clear a field prior to calculating a value for it but I simply forgot to do that in this simple case.

    With regard to firing the macro upon selection of the Task or Resource Usage views, I can't see a way to do that. Normally having a macro fire automatically is done with an Event, (e.g. Change Event), but a quick look at all the available Events for Project yields nothing that occurs when selecting/changing a view. The best I can suggest is to create a new custom tab on the ribbon, (assuming you are using Project 2010 or later), with a button tied to the macro. That's how I initiate my most used macros. It requires a manual operation to select the tab and click on the icon, but that should be pretty easy to remember. If you need help setting that up, let me know.

    John

    Tuesday, March 31, 2015 3:19 PM
  • John,

    Thanks for the suggestion, but I think I'll skip on this one. Currently, I have a bigger issue. It seems that I can't run the macro after I synchronize my project to Project Server. The error message stated, "Error accessing file. Network connection may have been lost." Any hints to solve this issue?

    PS:
    I've already set the Macro Security level to Medium and clicked Enable Macros when prompted by Microsoft Project.

    • Edited by Amir Syafrudin Wednesday, April 1, 2015 2:14 AM Added error message
    Wednesday, April 1, 2015 1:17 AM
  • Amir,

    Sorry, I don't do Project Server so I can't help you with this issue. Hopefully someone else will jump in with a suggestion.

    John

    Wednesday, April 1, 2015 3:11 AM