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, now some relevant information is being offered (i.e. definition of "late"). But now I have to ask, what is your definition of "actual % complete" (e.g. is it the entry in the % Complete field)? And, what is your definition of "planned % complete"?
Given full disclosure of the above it should be possible to create a custom field formula to set a flag.....maybe.
John
The intent of the report is simply to provide a prompt to the resource that they are not where they should be. The report is going to the resources as a PDF that don't use MSP and I am careful not to complicate it by stating the full range of definitions used in it which they would not necessarily be familiar with. If I was sharing with other MSP users I would reflect the above example as slipping unless the finish date had passed and completion not 100% in which case it would be late. Basically I am referring to slipping as late and appreciate it has a different meaning in project terms but as a dashboard report for this audience I am trying to keep it as simple as possible and don't want to have slipping tasks and late tasks separated out for each resource. Yep, it is something I should have explained up front.
- 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 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- Yes, I update the status date to the day I generate the report. I enter all the actual % complete then and send report out to resources.
- Mike,
Yeah, you should have.
Are you setting a Status Date? In other words, is there some reference point that can be used to determine where the task should be at a particular time?
John
