Loading...
Quick Links

Displaying Dates and Amounts of Resource Allocation

Home Forums Discussion Displaying Dates and Amounts of Resource Allocation

Tagged: ,

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #5297 Reply
    CommunityCommunity
    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 Reply

    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 Reply
    CommunityCommunity
    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 Reply
    CommunityCommunity
    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 Reply

    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 5 posts - 1 through 5 (of 5 total)
Reply To: Displaying Dates and Amounts of Resource Allocation
Your information:




85 − 82 =