Microsoft Project - Grouping a "Resource View" using a "Task" Field

Question

Scenario

• A custom "Task" Field has been populated in a "Task" View (could be Text or Outline or alike)
• Resource load the IMS using the "Resource Sheet"
• "Resource" View displays aggregates Task Effort by Resource

Need

• "Group" the Resource View using a "Task" Field

Constraint

• As will be well known to this Community, "Task" fields are not available to include as columns in a "Resource" View

Help Needed Question

• Does anyone have "MacGyver" approach to achieving this?

My Arduous Approach (you've been warned)

• Since "Task Name" is a field available in both Task and Resource Views, the Task Name is appended with the custom Task field to group on
  • For example
    • Task Name = "Build the Widget Complete"
    • Modified Task Name = "Build the Widget Complete^^lefthanded^^"
    • "^^" are used as unique delimiters to use to parse out from the modified Task Name in the Resource View
    • The custom Task field us used to separate 'lefthanded' widgets from 'righthanded' widgets
• Copy-Paste to Excel
• Use Power Query to reconstitute columns for original "Task Name" and customer "Task" field
• Perform Pivot to achieve the Grouping
• Provide Resourcing analysis in Excel

Answer 1

ScottBrunton,

MacGyver approach? Yeah, I've got a MacGyver fix fer ya, unless of course you really love your arduous approach.

This is how I understand your issue. A task Text field is populated with data. You would like to group on that data by having the Task Text field appear on the Assignment Text field of the Resource Usage view. Correct?

Okay, here's what you do. Use the following VBA code to translate Task Text1 field data to both the Task and Resource Assignment Text1 fields. This code was written by fellow MVP Jan Demesmaeker as one of our Project MVP FAQs way back when.

Sub Task_CF_To_Resource_Usage()
Dim Reso As Resource
Dim Task_As As Assignment
Dim Reso_As As Assignment
Dim Job As Task
For Each Job In ActiveProject.Tasks
    If Not Job Is Nothing Then
        For Each Task_As In Job.Assignments
            Task_As.Text1 = Job.Text1
            Set Reso = ActiveProject.Resources(Task_As.ResourceID)
            For Each Reso_As In Reso.Assignments
                If Reso_As.TaskID = Job.ID Then
                    Reso_As.Text1 = Task_As.Text1
                End If 'TaskID
            Next Reso_As
        Next Task_As
    End If 'Nothing
Next Job
End Sub

Once that is in place, you can now group on the Assignment Text1 field of the Resource Usage view

That should do it.

Please be advised that this forum is shutting down for active input on July 16th, so if you need further assistance on this, respond quickly

John

Answer 2

Scott,

You're welcome and thanks for the feedback.

It sounds like you've followed the escapades of our former MVP group from way back when. The only MVP colleagues left are Dale and Paul, the rest of us have either retired, left the "herd" or been dumped from the chosen few (yours truly). One of the last things we did was to convert a few of the original MVP FAQs to Wiki articles. I wrote a few of them, Julie wrote a couple more, then one day they all got archived. I had to dig a few out using the wayback machine so I could have them "on the ready" for questions just like yours.

I followed MacGyver for years and then on to Stargate until it finally ended. Now who's going to save the universe?

As is often the case, Microsoft (the owner of this forum) decided to fix something that wasn't broken so yes, this Answers forum is shutting down. As an alternative, you can post to this section of the Microsoft Q & A forum: https://learn.microsoft.com/en-us/answers/tags/804/m365-office-office-project-business-platform-windows You can also post Project questions to the Project Tech Community forum at:

https://techcommunity.microsoft.com/category/project/discussions/project

There are a couple sections of the StackOverflow forums, one for VBA type questions and the other for Project Management. I personally don't like the format of StackOverflow, particularly the stupid CommunityBot that keeps bringing up old questions over and over and over . . .

For Project VBA: https://stackoverflow.com/questions/tagged/ms-project

For Project Management: https://pm.stackexchange.com/

John

Answer 3

One more to add to the list: reddit.com/r/msproject

LinkedIn also has many groups.

Answer 4

Hi, John,

My appreciation to you for the reply and for jumping into Jan's way-back-machine to pull this rabbit out of the hat.

Jan De Messemaeker is a familiar name as a great contributor and MVP to the Project community (he along with a small handful of other recognized MVPs), so thank you for the research on my question.

The 'bones' of the code provided was exactly what was needed, which (I will admit) hurt my brain when having to dive that far down the Object Model tree into the realm of Task level Resource assignments.

Thanks to you help, I can considered MacGyver retired!

More importantly, I am mildly panicked at your comment that "this forum is shutting down for active input on July 16th" ... is there an alternative forum solution for the Project Community? Having only made a couple of requests here, it has shown to be a valuable resource.

Thank you for the quick and authoritative reply

Answer 5

One of the example detail steps is getting cut-off by this forum engine, so I modified it to use ^^ characters.