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
This is a mockup of what I interpret your plan might look like.
If I further understand you want to display only task ID 3 in Project 1 and task ID 10 in Project 2 since they are the limiting tasks. Something like this.
The only way I know to create this is with a simple macro that identifies the relevant tasks and creates a filterable field.
If the above is not what you have or not what you want then please expand with some example screen shots.
John
This is the report that I include in a dashboard. I want to show a summary of late tasks for each resource but exclude any with a late predecessor. In other words, I only want to show who is holding the project up not all the dependent successors that are waiting. It is all simple finish-start so a dependent task cant start until the predecessor finished. What it does at the moment is provide a summary of all late tasks for each resource even on the tasks where they are waiting on someone else. Technically that is correct but as it is a dashboard I want to summarize to the resource the task is with. It is just getting too long the way I have it. It captures a year worth of projects and on average there would be 50 of them. Thank you so much for looking at this! Cheers, Mike
- Mike,
Sorry, I'm not quite following what you say. Remember, I not in your head so I can't see what you see (or what you're thinking). The screen shot you show looks like a grouped view (perhaps a customized report you created with Project's report features). It is showing what you want to see or what you do see?
Can you show me a marked up Resource Usage view?
JohnYes, it a customized dashboard report with resources grouped. I want each resource to see a summary of their late tasks. The resource usage view doesn't really provide any information that helps explain this. Below is a screenshot of a single project in the Gantt chart. B.T has 25% of the submission completed. The site approval cant start until the submission is complete, Exec. approval cant start until site approval complete and execution cant start until until Exec. approval complete. The submission is late therefore making the rest of the tasks late. On the "late tasks x resource" report I want to group the late tasks for each resource but filter out tasks where there are predecessors that are not 100% complete. In the example below B.T would be the only one on this project displayed as late in the report. When B.T is 100% complete he will be filtered out and it will then show G.L late in the report until he is 100% complete etc. If someone asked "who is currently holding the project up?" that is what the report should show. In this case B.T is the only one holding it up as the others cant do anything until he finishes.
Sorry for my poor explanation. Thanks, Mike
- Mike,
What defines an effort as being late? It sounds like any resource assignment that is not 100% complete is considered "late". It gives a new meaning to "holding up the project". I think a more appropriate definition is, "the ball is in xxx's court".
Potentially, if you can provide a concrete definition of "late", there might be a way to create a custom formula that tests for "late" and then sets a flag which provides the criteria for a filter.
The problem right now is that my day is done so our communication window is very narrow.
John
