Displaying Dates and Amounts of Resource Allocation

Home Forums Microsoft Project Discussion Forum Displaying Dates and Amounts of Resource Allocation

Tagged: ,

Viewing 4 reply threads
  • Author
    Posts
    • #5297
      Avatar photoCommunity
      Keymaster

      I am trying to display over allocated resources and the specific date and hours of the over allocation in a separate View.

      Listed below is a scenario to illustrate my inquiry…

      For Project A, with Tasks T1 and T2 and Resources RNOT and R2 (sorry couldn’t resist the pun) and a four day duration from 1/2 to 1/5 with the following schedule:

       

                                      1/2                         1/3                         1/4                           1/5

      T1   RNOT             7hrs                       6hrs                       6hrs                         5hrs
      R2                    6hrs                       4hrs                       8hrs                         5hrs

      T2   RNOT             0hrs                       3hrs                       2hrs                         5hrs
      R2                   2hrs                       4hrs                       4hrs                         3hrs

       

      What I would like to see is something along the lines of

       

      Resource             Date Over Allocated                       Hours Over Allocated

      RNOT                    1/3                                                         1hr
      1/5                                                         2hrs

      R2                           1/4                                                         4hrs

       

      Any assistance would be greatly appreciated.

      Have a blessed day –

      Michael               

    • #7048

      Hello Michael,

      I believe it is not possible to create such a custom view. I have once used the following VBA routine to list overallocated hours in an Excel sheet.

      Sub ListOverAllocated()

      Dim tsvs As TimeScaleValues

      Dim i As Integer
      Dim ResCount As Integer
      Dim Rows As Integer

      Dim XLApp As New Excel.Application
      Dim Wbook As Workbook

      Set Wbook = XLApp.Workbooks.Open(ActiveProject.Path + “OverAll.xls”)

      Wbook.Sheets(“Sheet1”).Cells(1, 1).Value = “Overallocated Resource Name”

      Wbook.Sheets(“Sheet1”).Cells(1, 2).Value = “Date Overallocated”

      Wbook.Sheets(“Sheet1”).Cells(1, 3).Value = “Hours Overallocated”

      Rows = 2
      For ResCount = 1 To ActiveProject.NumberOfResources

      If ActiveProject.Resources(ResCount).Overallocated Then

      Wbook.Sheets(“Sheet1”).Cells(Rows, 1).Value = ActiveProject.Resources(ResCount).Name
      Rows = Rows + 1

      Set tsvs = _
      ActiveProject.Resources(ResCount).TimeScaleData( _
      ActiveProject.ProjectStart, _
      ActiveProject.ProjectFinish, _
      pjResourceTimescaledOverallocation, _
      pjTimescaleDays, 1)

      For i = 1 To tsvs.Count
      If Not tsvs(i).Value = “” Then
      Wbook.Sheets(“Sheet1”).Cells(Rows, 2).Value = CDate(tsvs(i).StartDate)
      Wbook.Sheets(“Sheet1”).Cells(Rows, 3).Value = tsvs(i).Value / 60
      Wbook.Sheets(“Sheet1”).Cells(Rows, 4).Value = “hrs”
      Rows = Rows + 1
      End If
      Next
      End If
      Next
      Wbook.Close savechanges = yes
      XLApp.Quit
      End Sub

      You need to create an empty Excel sheet named “OverAll.xls” in the same directory as the mpp file before running the macro. Click Alt + F11 to open VBE and copy the code above into a module and make sure “Microsoft Excel 1x.0 Object Library” checkbox is turned on  ( [x] ) in References dialog box (Tools | References…). I did not have a chance to improve it. Pls consider testing it in a backup of your project plan and post for feedback. I hope it works.

      Regards.

      Ismet

      projectuser[at]fastmail.fm

    • #7049
      Avatar photoCommunity
      Keymaster

      Michael,

      Have you tried using the Overallocated usage detail as a means of indicating overallocation?  Go to the usage view and hide subtasks (just show resources).  Select the Overallocated usage style (you can right click the detail style column or choose overallocation from the format menu.  That will show you hours overallocated for each resource.

      Hope that helps…Larry

    • #7050
      Avatar photoCommunity
      Keymaster

      Or use the resource usage view, filter for overallocated resources, and in the timephased view add in Overallocated in the detail, removing work.

       

    • #7051

      Hello Michael,

      As an alternative, you can use the Resource Usage report. It will give you the totals as well.

      In the code above, you can replace pjTimescaleDays with one of the other unit enumerations: http://msdn.microsoft.com/en-us/library/office/bb223241(v=office.12).aspx. And note that both the code and the report can be customized for a date range and a period.

      Regards,

      Ismet | www.ismetkocaman.com

Viewing 4 reply threads
  • You must be logged in to reply to this topic.