Sep 12 2022 09:05 PM
Sep 12 2022 09:05 PM
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
Sep 13 2022 09:04 AM
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.
Sep 14 2022 04:23 PM
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
Sep 14 2022 04:53 PM
Sep 14 2022 05:46 PM
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
Sep 14 2022 07:07 PM
Sep 14 2022 08:38 PM
Sep 15 2022 12:41 PM - edited Sep 15 2022 03:56 PM
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.
Sep 15 2022 04:05 PM
Sep 15 2022 04:36 PM
Sep 15 2022 04:39 PM
Sep 15 2022 04:45 PM - edited Sep 15 2022 04:54 PM
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.
Sep 15 2022 05:17 PM
Sep 17 2022 03:35 PMSolution
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?
Oh yeah, I almost forgot. And here's the macro that does it:
'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
Sep 18 2022 04:05 PM
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
Sep 18 2022 05:34 PM