Late predecessors filter

[MikeT67]

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

[#2]

@MikeT67 

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

[#3]

@John-project 

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

[#4]

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

[#5]

@John-project 

Yes, 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

[#6]

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

[#7]

Yep, the ball is in xxx's court is what I am after for late tasks. Late by definition of this report is that task should have started and actual % complete is less than planned % complete. Sorry to take up your time, I'm sure I will work it out. Thank you for your help.

Cheers,

Mike

[#8]

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

[#9]

Say a task has a duration of 4 weeks and the start date is 1st January by the 15th of January the planned completion is 50%. The resource assigned to the task reports on the 15th of January he is only 25% complete (actual completion). For the purpose of this report that is late. He may choose to do something to pick the time up so after 3 weeks he reports he is 80% complete which means he is no longer late.

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.

[#10]

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

[#11]

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.

[#12]

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

[#13]

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

[#14 · Accepted solution]

@MikeT67 

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 Single

For 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

[#15]

@John-project 

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

[#16]

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