none
rollup specific milestones to Project Summary row RRS feed

  • Question

  • hi

    i have a master project with sub-projects

    al subprojects have 5 milestone,s

    is there a solution to rollup only  all 5 milestones with a indicator ( yellow/red/green ) to (Sub) Projectsummary

    using this formule:

    IIf([Baseline Finish]=ProjDateValue("NA");"No Baseline";IIf([Finish Variance]>0 And [Critical];"Critical Finish Variance";IIf([Finish Variance]>0 And Not [Critical];"Non-critical Finish Variance";"No Variance")))

    so i have one view for al projects

    hope somebody helps me

    rein

    Friday, March 18, 2016 12:28 PM

Answers

  • Rein,

    You're welcome and thanks for the feedback. If you question was answered please mark whichever of my responses you feel was the answer.

    John

    • Marked as answer by Rein Post Wednesday, March 30, 2016 6:08 AM
    Tuesday, March 29, 2016 3:15 PM

All replies

  • Rein,

    Your post is a little confusing as to what exactly you are trying to achieve. Are you trying to roll up a custom field (e.g. Text1) using a formula to the master Project Summary Task, or the Project Summary task of each subproject?

    Be advised that in a dynamic master (i.e. linked master), the subprojects are not actually a part of the master, rather the master only contains pointers to each subproject. If you create a custom field with a formula at master level it will only apply to tasks that are part of the master itself. And, if you create a custom field with a formula in each subproject, that formula will only apply to tasks that are part of the subproject. So, if you want to roll up a custom field from selected tasks in each subproject (e.g. milestones) to the master level, you will need some VBA, it can't be done with a simple custom field formula.

    I can provide you with a macro to do what you want but I need more detail. First, answer the question above. If you do want the roll up to the master Project Summary Task, which subproject milestone takes priority. For example, let's say a milestone in the first subproject would result in an output of "no baseline" but a milestone in the second subproject would result in an output of "no variance". What should appear in the custom field of the master file Project Summary Task?

    Hope this helps.

    John


    Friday, March 18, 2016 4:12 PM
  • hi John

    thanks for quick reply and helping

    sorry, i mean indeed rollup a selected milestone to the Project Summary task of each subproject

    so i can see in de masterfile the overview of al subprojects

    thanks in advanche

    Rein

    Monday, March 21, 2016 10:17 AM
  • hi John

     this is indeed the case:

     So, if you want to roll up a custom field from selected tasks in each subproject (e.g. milestones) to the master level, you will need some VBA, it can't be done with a simple custom field formula.

    greets

    Rein

    Monday, March 21, 2016 10:24 AM
  • Rein,

    Let me address both of your replies.

    For clarification, when you view a dynamic master the top level summary rows for each subproject may look like the Project Summary Task of each subproject but they are not. The Project Summary Task of a subproject will NOT show up at master level. The summary row you see at master level is in fact a "task" that is part of the master itself and is generated by Project to represent the Project Summary Task of each subproject but again, it is not the Project Summary Task of the subproject. Therefore if you have a custom field within each subproject and the custom field is "rolled up" to that subproject's Project Summary Task it will NOT show up in the master. However, with VBA you CAN roll up any subproject data to master level.

    But, let me offer another suggestion. Instead of using a dynamic master, which is a linked structure prone to corruption, why not use a static master? A static master is created by unchecking the "link to project" option in the lower right corner of the Insert Project window. The static master is a snapshot in time of each of the subprojects and is a single independent file. It will have to be created anytime you want to update the current information in the subprojects but it is not subject to corruption and you can roll up your milestones with a custom field formula.

    Hope this helps.

    John


    • Edited by John - Project Monday, March 21, 2016 4:10 PM alternate approach
    Monday, March 21, 2016 3:14 PM
  • hi John

    thanks for the explanation, but i have to use a Dynamic master, because these subprojects are made by a other group off People, i only need to make a different view for all these subprojects together,.

    so i can't use a static master for this kind of subprojects

    The only way i can do this is by VBA like you suggested .(However, with VBA you CAN roll up any subproject data to master level )

    So the real question is , how does  this VBA looks like?

    thanks in advanche

    Rein

    Tuesday, March 22, 2016 9:22 AM
  • Rein,

    You say the subprojects are made by other people. If those people are remote from your location then using a dynamic master is like playing with fire, you will get corruption, eventually. The best practice rules for linked structures such as a dynamic master are these: Never rename, move, overwrite, or save off any of the files in the structure to another location. Do no operate the structure over a network. All files should reside in a single folder on a local drive.

    Now, given that warning, do you still feel you need to maintain a dynamic structure? From what you have told me, you only need to make a special view and you do not need any type of interaction with the subproject files. Therefore, a static master is exactly what you should use.

    Nonetheless, if you really want to take a chance with the dynamic master you will need a macro to do what you want. I can write that macro for you but I need some additional information as I stated in my original response on March 19. You indicated you want specific milestones from each subproject rolled up to the subproject summary task of each inserted subproject (see screenshot). How many subprojects? How many milestones per subproject? What field do you want rolled up?

    John

    I've mocked up your formula to show what it might look like at master level. I renamed the Text1 field as "milestone rollup" with rolled up values circled in green.



    • Edited by John - Project Tuesday, March 22, 2016 4:09 PM screen shot
    Tuesday, March 22, 2016 3:39 PM
  • hi John

    the subprojects are on the same server as master, but are changing by more people, every department is changing is own piece on subprojects, our "Boss" is working with master, and is changing the subprojects from there, you know, one men at the time can working at the same project, and i have to send a view on Friday afternoon, subprojects that are ready , we remove from master ( i dont know of this way of project management is okay, but this was the choice of that moment 6 years ago )

    if i use a static, i have a lot of work to do, to make a new static on every Friday afternoon ( thats what i think? )

    so really I want to take a chance with the dynamic master :-)

    the follow milestones are used:

    1  OSF

    2  Eng Ready

    3  Pur Ready

    4  Preship Ready

    5  Leave Vaassen

    subprojects are changing, but i think enough for 100 subprojects

    you can use Text10/11/12/13/14

    please let me know

    Rein

    note: i still know what you mean by using a static master, i use it for myself also, but i am the only one working in my static master and i unlink the subs. then its not a problem

    Tuesday, March 22, 2016 6:45 PM
  • John, your screenshot is exactly what i want:-)

    greets

    rein

    Tuesday, March 22, 2016 8:09 PM
  • the 5 milestones are used for each subproject

    greets

    rein

    Tuesday, March 22, 2016 9:19 PM
  • Rein,

    I'm concerned about two things. You mention that all the files, subproject and master, are on the same server and that suggests it's operated over a network. Any glitch in the network and the structure is corrupt. You also indicate that new subprojects are added and deleted from the master. If that is not done properly, then you will end up with partial links which also leads to corruption. The fact that the decision to do this was made several years ago doesn't mean it was a wise decision.

    Building a new static master each week is not difficult, regardless of how many subprojects you have. All you need to do is to record a macro of you doing it the first time and then just run that recorded macro each week.

    So what are the five items you listed? Are they the task names of the milestones? Does each of the subprojects have these same five milestones?

    John

    Tuesday, March 22, 2016 9:21 PM
  • hi John

    i understand about your concerning, but i can't change the situation by now

    your  questions; indeed this 5 items to listed, yes, they are the task names of the milestones, and yes all subbrojects have these 5 milestones

    thanks in advanche:-)

    Rein

    Tuesday, March 22, 2016 9:32 PM
  • Rein,

    Okay, give this a try. Let me know if it does what you need. The macro needs to be run at macro level.

    John

    Option Compare Text
    Public sp As Subproject
    Public t As Task
    Public Str As String
    Sub DMstrMilestoneRollup()
    'This macro rolls up specific milestones of subprojects in a dynamic master to
    ' the subproject insertion point summary line in the master.
    'Macro written by John Project for Rein Post
    'March 23, 2016 7:00 AM
    For Each sp In ActiveProject.Subprojects
        For Each t In sp.SourceProject.Tasks
            Select Case t.Name
                Case "OSF"
                    TstCriteria
                    sp.InsertedProjectSummary.Text10 = Str
                Case "Eng Ready"
                    TstCriteria
                    sp.InsertedProjectSummary.Text11 = Str
                Case "Pur Ready"
                    TstCriteria
                    sp.InsertedProjectSummary.Text12 = Str
                Case "Preship Ready"
                    TstCriteria
                    sp.InsertedProjectSummary.Text13 = Str
                Case "Leave Vaassen"
                    TstCriteria
                    sp.InsertedProjectSummary.Text14 = Str
            End Select
        Next t
    Next sp
    End Sub
    Sub TstCriteria()
    'This sub determines the message to rollup
    If t.BaselineFinish > 50000 Then
        Str = "No Baseline"
    ElseIf t.FinishVariance > 0 And t.Critical = True Then
        Str = "Critical Finish Variance"
    ElseIf t.FinishVariance > 0 And t.Critical = False Then
        Str = "Non-Critical Finish Vaiance"
    Else
        Str = "No Vaiance"
    End If

    End Sub

    Wednesday, March 23, 2016 2:10 PM
  • hi John

    thanks for the macro, i will try tomorrow morning!

    Rein

    Wednesday, March 23, 2016 9:03 PM
  • hi John

    macro is working fine, thanks for sharing and thanks for yout time

    greets

    Rein

    Tuesday, March 29, 2016 5:16 AM
  • Rein,

    You're welcome and thanks for the feedback. If you question was answered please mark whichever of my responses you feel was the answer.

    John

    • Marked as answer by Rein Post Wednesday, March 30, 2016 6:08 AM
    Tuesday, March 29, 2016 3:15 PM