Forum Discussion
Late predecessors filter
I think it's interesting that the subject of your original post has nothing to do with what you really wanted or the final solution. But, I think we finally got to the bottom of it all, (or maybe the top), and here's what I have for you.
This is the sample test file with progress on some tasks, some complete and a couple in progress. The Status Date is set to 9/9/22.
This is what the Resource Usage view shows after the macro is run to find the "late" resources. A "late resource" is defined as reported % Complete less than "expected" percent complete and "expected" percent complete is defined as linear cumulative progress from task start to the status date expressed as a percent of total task duration. For reference, the resource assignment Flag1 field is renamed as "Exp % Complete".
With a custom filter set to look for resource Flag1 and showing related summary (i.e. resource) rows, this is the result.
Is this awesome or what?
John
Oh yeah, I almost forgot. And here's the macro that does it:
Option Explicit
Sub MikT67Special()
'Macro written by John-Project for Aussie Mike
'9/16/22 4:00 PM MST
'This macro examines all resource assignments and sets the resource assignment Flag1 true
' if the task is on-going (i.e. not complete) and reported progresss (i.e. % Complete) is less
' then expected percent complete. Excpected percent complete is defined as linear cumulative duration
' from assignment start to Status Date.
Dim r As Resource
Dim a As Assignment
Dim DtDif As Single
Dim PlanPer As SingleFor Each r In ActiveProject.Resources
If Not r Is Nothing Then
For Each a In r.Assignments
'reset tresource assignment flag from previous run
a.Flag1 = False
'only look at on-going assignments
If a.ActualStart <> "NA" Then
DtDif = Application.DateDifference(a.ActualStart, ActiveProject.StatusDate)
'check if assign should have finished and if so, normalize planned percentage
If a.Finish < ActiveProject.StatusDate Then DtDif = a.Task.Duration
PlanPer = DtDif / a.Task.Duration * 100
'write expected completion to assignment Text1 for reference
a.Text1 = PlanPer & " %"
'flag task that is "late" per expected completion as of status date
If a.PercentWorkComplete < 100 And a.PercentWorkComplete < PlanPer Then a.Flag1 = True
End If
Next a
End If
Next r
End Sub
Mike,
Okay, wowzer! I think we may finally be getting someplace. Not sure where but...
Let me take all this new and revealing info you gave and see if I can come up with something, but, it may not be until tomorrow. My day is ending.
John
- Mike,
You're welcome and thanks for the feedback.
It looks like you've got a bunch of guys that are "lagging" in their response. You tried to incentivize me with a beer, maybe that's what is needed for the "laggers" 🙂
About that beer, you're off the hook because I don't drink. Cheers mate!
John Great work John that is so very cool!! Appreciate you efforts, patience and thank you very much. That will tidy my dashboard up nicely. Looking at how well that worked I think I may have to do some extra learning to write macros. Worked with them a bit in excel but never MSP.
Cheers and thanks again
Mike
I think it's interesting that the subject of your original post has nothing to do with what you really wanted or the final solution. But, I think we finally got to the bottom of it all, (or maybe the top), and here's what I have for you.
This is the sample test file with progress on some tasks, some complete and a couple in progress. The Status Date is set to 9/9/22.
This is what the Resource Usage view shows after the macro is run to find the "late" resources. A "late resource" is defined as reported % Complete less than "expected" percent complete and "expected" percent complete is defined as linear cumulative progress from task start to the status date expressed as a percent of total task duration. For reference, the resource assignment Flag1 field is renamed as "Exp % Complete".
With a custom filter set to look for resource Flag1 and showing related summary (i.e. resource) rows, this is the result.
Is this awesome or what?
John
Oh yeah, I almost forgot. And here's the macro that does it:
Option Explicit
Sub MikT67Special()
'Macro written by John-Project for Aussie Mike
'9/16/22 4:00 PM MST
'This macro examines all resource assignments and sets the resource assignment Flag1 true
' if the task is on-going (i.e. not complete) and reported progresss (i.e. % Complete) is less
' then expected percent complete. Excpected percent complete is defined as linear cumulative duration
' from assignment start to Status Date.
Dim r As Resource
Dim a As Assignment
Dim DtDif As Single
Dim PlanPer As SingleFor Each r In ActiveProject.Resources
If Not r Is Nothing Then
For Each a In r.Assignments
'reset tresource assignment flag from previous run
a.Flag1 = False
'only look at on-going assignments
If a.ActualStart <> "NA" Then
DtDif = Application.DateDifference(a.ActualStart, ActiveProject.StatusDate)
'check if assign should have finished and if so, normalize planned percentage
If a.Finish < ActiveProject.StatusDate Then DtDif = a.Task.Duration
PlanPer = DtDif / a.Task.Duration * 100
'write expected completion to assignment Text1 for reference
a.Text1 = PlanPer & " %"
'flag task that is "late" per expected completion as of status date
If a.PercentWorkComplete < 100 And a.PercentWorkComplete < PlanPer Then a.Flag1 = True
End If
Next a
End If
Next r
End Sub- I appreciate it John, but don't go to too much trouble mate. I am assuming you are in US so bit hard for me to buy you a beer for your efforts from downunder.
Cheers,
Mike
