none
Can I get project/tasks costs split by resource group? RRS feed

  • Question

  • We have resources that belong  different groups and locations and we have assigned them different Resource Groups in the resource sheet. What we need is a way to see not only the total costs/work for a task, but also breakdown by different resource groups.

     

    Based on what I've read, it seems like I might have to write a macro to export costs from assignments table into Cost1, Cost2.... etc. representing each distinct resource group? That would limit us a little as we have employment categories as well as location but much better than trying to export to Excel etc..

    EXAMPLE

    Resource             Resource Group

    John Doe             NY
    Jane Doe             DC
    Mary Jane            INDIA

    .....

     

    TASKS REPORT

    TASK             COST    COST_NY      COST_DC    COST_INDIA

    Task 1          105       60                20              25

        1.1              60      40                 20

        1.2              40      20                                  20

        1.3              5                                              5  

     

    Task 2          200                           50              150

        2.1              50                          20                30

        2.2              50                                              50

        2.3             100                          30                70  


     

     

     

     

     

     

    Tuesday, December 27, 2011 2:49 PM

Answers

  • dctech1000,

    Well, the necessary code is a little more involved than what you show. The macro below should do it. The option to compare text make the string comparisons case insensitive, so whether its "India" or "INDIA" doesn't matter.

    Option Compare Text

    Sub CostByRGroup()

    Dim t As Task

    Dim a As Assignment

    Dim r As Resources

    Dim Grp As String

    Dim Cst As Single

    Set r = ActiveProject.Resources

    For Each t In ActiveProject.Tasks

        If Not t Is Nothing Then

            t.Cost1 = 0: t.Cost2 = 0: t.Cost3 = 0

            For Each a In t.Assignments

                Grp = r(a.ResourceName).Group

                Cst = a.Cost

                If Grp = "DC" Then t.Cost1 = t.Cost1 + Cst

                If Grp = "NY" Then t.Cost2 = t.Cost2 + Cst

                If Grp = "India" Then t.Cost3 = t.Cost3 + Cst

            Next a

        End If

    Next t           

    End Sub

    John

    Wednesday, December 28, 2011 2:34 AM

All replies

  • dtech1000,

    Personally I would use VBA to export the data and format it as I wanted, but there may be an easier way. Try using the grouping feature of Project. On the Resource Usage view, first group by "Group", then group by name but set the name field as an assignment type (check the option to group by assignment not resource).

    You won't get the exact format, but the data should be there.

    Hope this helps.

    John

    Tuesday, December 27, 2011 5:03 PM
  • Hi dctech1000,

    To get the layout you set, yes -- I believe the only option is to use code to separate the costs by resource group and copy that data into Cost1, Cost2, etc.  You can however, get the costs separated by task without code by using a group in the Task Usage view.

    I created resources with different Resource Groups and assigned them to different tasks.  In the Task Usage view I created a custom group to group first by Task ID (ID) and then group the assignments by Resource Group.  Add the cost field for the following:

     

    I hope this helps.

     

    Julie


    Tuesday, December 27, 2011 5:04 PM
    Moderator
  • I tried what you suggested and as you say, its almost there.

    I will try your idea of using VBA to copy costs into cost1, cost2 etc. so they can be viewed in the Gant chart as well.

    I take it this would be something like the following....

    For each task t

        for each resource_assignemnt ra

     if      ra.resource_group = "DC"        then ra.cost1  = ra.cost

            else  ra.resource_group = "NY"        then ra.cost2   = ra.cost

            else  ra.resource_group = "INDIA"   then ra.cost3  = ra.cost

           .....

     

    Thanks for your help

     

    Tuesday, December 27, 2011 8:38 PM
  • Thanks Julie... John had proposed something similar and I think it is almost there.

    I will take a crack at the VBA options. Any inputs are welcome.

    Tuesday, December 27, 2011 8:39 PM
  • dctech1000,

    Well, the necessary code is a little more involved than what you show. The macro below should do it. The option to compare text make the string comparisons case insensitive, so whether its "India" or "INDIA" doesn't matter.

    Option Compare Text

    Sub CostByRGroup()

    Dim t As Task

    Dim a As Assignment

    Dim r As Resources

    Dim Grp As String

    Dim Cst As Single

    Set r = ActiveProject.Resources

    For Each t In ActiveProject.Tasks

        If Not t Is Nothing Then

            t.Cost1 = 0: t.Cost2 = 0: t.Cost3 = 0

            For Each a In t.Assignments

                Grp = r(a.ResourceName).Group

                Cst = a.Cost

                If Grp = "DC" Then t.Cost1 = t.Cost1 + Cst

                If Grp = "NY" Then t.Cost2 = t.Cost2 + Cst

                If Grp = "India" Then t.Cost3 = t.Cost3 + Cst

            Next a

        End If

    Next t           

    End Sub

    John

    Wednesday, December 28, 2011 2:34 AM
  • It looks like John has saved the day again :-).

    Wednesday, December 28, 2011 8:21 PM
    Moderator
  • dctech1000 --

    Allow me to add a different potential solution to your reporting problem. Try this:

    1.  Open the project in question and apply the Resource Usage view.
    2.  Collapse the Unassigned resource.
    3.  Right click on the Work column header, select Insert Column on the shortcut menu, and select the Cost column.
    4.  Drag the split bar to the right edge of the Work column so you can see both the Cost and Work columns.
    5.  Right-click anywhere in the timephased grid (timesheet-like grid on the right) and select the Cost item on the shortcut menu.
    6.  Click the View tab to display the View ribbon.
    7.  In the Data section of the View ribbon, click the Group By pick list and select the Resource Group grouping.

    Using the Resource Group grouping, you can see the Cost and Work totals for every task, every resource, and the Resource Groups to which each resource belongs.  Although the format does not meet your exact criteria, I think it might give you the data you want.  Just a thought.  Hope this helps.


    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

    Wednesday, December 28, 2011 8:22 PM
    Moderator