Forum Discussion
Late predecessors filter
I have a project file that is used to provide a current status of our annual budgeted projects. Tasks are limited to "preparation of submission" > "approval 1" > "approval 2" > "approval 3" > project execution. They are all linked and scheduled "late as possible" all working back from the completion date of "execution". I want to filter so that only the task that is holding each of the projects up is shown in a report or dashboard.
For example if the submission is complete but not approval 1, I don't want to show the late linked successors as well. My intent is to provide a report for each of the stakeholders showing projects that are waiting on them and holding others up and not their tasks that they cant do because of a late predecessor. Any help greatly appreciated
Cheers,
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
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?
John
