none
How can we select "children" tasks that belong to a specified "parent" task? RRS feed

  • Question

  • I'm seeking a means to categorize tasks across a production portfolio dynamically into multiple groups (likely three). One purpose is exporting selected tasks into other PS 2010 instances as they progress through defined stage gates. 

    At a given time, tasks will take on one of three mutually exclusive planning values (e.g., Current, Upcoming, Eventual). Summary tasks near the end of a long-term project that do not cros an appropriate stage gate may never undergo detailed scheduling or resource estimation in functional-area PS 2010 instances.

    An enterprise field formula will calculate and display one of three values based upon overall criteria that includes planned start dates, duration.

    Beyond parsing the WBS code, is there any way to use a custom enterprise field formula to select the children tasks (or child resources) of a given parent task (or parent resouce) identifier?

    For example, if summary task G is the parent of tasks 301, 302....309, the function I seek would be:

     from([G id])

    This function would return task rows G, 301 through 309.

    I cannot find any Project 2010 or 2007 defined formula function for returning hierachical membership, such as children, belongs to or from.

    I'm reluctant to settle for exporting rows based only upon date and duration criteria. That could miss some work packages. Having a field that specifies planning responsibility for each task is also important.

    Would you please offer suggestions for accomplishing this functionality in PS 2010?

    Thanks kindly.

    --John

    Wednesday, October 6, 2010 8:46 PM

Answers

  • Hi John,

    ad 1: seems that you want this to do a lot more. You want to have a kind of inheritance from summary lavel to child level for fields. Project only gives you a roll-up, no roll-down (for tasks, it does for assignments). I think you are correct, this complexity will exceed limits of custom fields. I think you will need to go for the macro solution. Regarding your question to Jack (From a programming perspective, can one use VBA to calculate a custom enterprise task field and then place a timestamp in a custom project field (whether project or enterprise)?) - yes, it is. Moreover, you could put a lot of your logic into this macro and would not need to work with several additional filters.

    ad 2: I am not sure if a selective update is possible. I can't remeber that I have ever read about something like that.

    Regards
    Barbara

    Thursday, October 7, 2010 7:52 PM
    Moderator

All replies

  • John:

    What you're asking will require custom development so I'm moving this thread to that forum.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG
    Wednesday, October 6, 2010 9:13 PM
    Moderator
  • Hi John,

    does this formula help?

    IIf([WBS]=0;"PST";
    IIf([WBS]=1;0;
    IIf(Mid([WBS];len([WBS])-1;1)=".";left([WBS];len([WBS])-2);
    IIf(Mid([WBS];len([WBS])-2;1)=".";left([WBS];len([WBS])-3);
    IIf(Mid([WBS];len([WBS])-3;1)=".";left([WBS];len([WBS])-4);
    IIf(Mid([WBS];len([WBS])-4;1)=".";left([WBS];len([WBS])-5);
    IIf(Mid([WBS];len([WBS])-5;1)=".";left([WBS];len([WBS])-6);
    IIf(Mid([WBS];len([WBS])-6;1)=".";left([WBS];len([WBS])-7);
    IIf(Mid([WBS];len([WBS])-7;1)=".";left([WBS];len([WBS])-8))))))))))
    Calculation for task and group summary rows need to be set to "Use Formula". This formula allows max 99999 children for one parent task.

    Another way could be a macro with Task.OutlineParent Property.

    Regards
    Barbara

    Ps: I think you will need to replace ";" by "," due to different regional settings.

    Thursday, October 7, 2010 8:22 AM
    Moderator
  • Hi John,

    does this formula help?

    IIf([WBS]=0;"PST";
    IIf([WBS]=1;0;
    IIf(Mid([WBS];len([WBS])-1;1)=".";left([WBS];len([WBS])-2);
    IIf(Mid([WBS];len([WBS])-2;1)=".";left([WBS];len([WBS])-3);
    IIf(Mid([WBS];len([WBS])-3;1)=".";left([WBS];len([WBS])-4);
    IIf(Mid([WBS];len([WBS])-4;1)=".";left([WBS];len([WBS])-5);
    IIf(Mid([WBS];len([WBS])-5;1)=".";left([WBS];len([WBS])-6);
    IIf(Mid([WBS];len([WBS])-6;1)=".";left([WBS];len([WBS])-7);
    IIf(Mid([WBS];len([WBS])-7;1)=".";left([WBS];len([WBS])-8))))))))))
    Calculation for task and group summary rows need to be set to "Use Formula". This formula allows max 99999 children for one parent task.

    Another way could be a macro with Task.OutlineParent Property.

    Regards
    Barbara

    Ps: I think you will need to replace ";" by "," due to different regional settings.


    That formula probably won't work for most English language users of project without replacing the semicolon ; with a comma ,

    With a simple macro you could probably set this up:

    Something like

    dim t as task

    dim tchild as task

    set t = activeselection.tasks(1)

    for each tchild in t.outlinechildren

    'set a flag so you can filter

    tchild.flag1 = Yes

    next tchild

    'filter on that flag

    end sub

     


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Thursday, October 7, 2010 3:01 PM
    Moderator
  • Barbara,

    In Project 2003 and 2007, the formula you kindly supplied identifies the parent for the summary tasks (e.g., 6 for summary task 6.1)--after conversion of semicolons to commas, of course. Other rows are returning #ERROR, possibly because the period (.) separator is evaluating blanks or periods where not expected.

    If I have a wbs parent 6.1, then I want to identify via formula the 6.1 row and all children (say 6.1.1 through 6.1.1.1.19) that comprise this "6.1" family with a 6.1 value in this enterprise field. Then I could use a separate filter formula, as necessary with date and duration elements, to call this family "Upcoming" for identifying responsibility for resource estimation and export them to the appropriate functional area planning group's PS 2010 instance.

    BTW, I'm somewhat surprised that Project Pro 2003 accepted your formula, as it is well beyond 256 characters.

    Thanks again for your suggestion, Barbara.

    --John

    Thursday, October 7, 2010 3:45 PM
  • Jack,

    I like the relative simplicity of your macro design for a single project.

    1. If project managers do not want to run such a macro manually on each project periodically, would one have to set up a PowerShell script to run this "resourcing responsibility" macro across 1500 projects in the portfolio (and run when the production instance is offline)?

    2. If this macro update would be a manual activity at project manager discretion, then we would need to know when the last run occurred on each project. Downside is that would add another custom field to the production instance. Is this "best practice" to add a project field to track when a macro last ran in each project?

    3. Would you please comment on the relative performance (including save time) of using field formula for auto-update versus adding an automated macro to each project that runs prior to saving?

    Thanks kindly.

    --John

     

    Thursday, October 7, 2010 4:10 PM
  • Jack,

    I like the simplicity of a your macro design for a single project.

    1. If project managers do not want to run such a macro manually on each project periodically, would one have to set up a PowerShell script to run this "resourcing responsibility" macro across 1500 projects in the portfolio (and run when the production instance is offline)?

    If this macro update would be a manual activity at project manager discretion, then we would need to know when the last run occurred on each project.

    2. Would you please comment on the relative performance (including save time) of using field formula for autoupdate versus adding an automated macro to each project that runs prior to saving?

    Thanks kindly.

    --John

     


    1) Implementing something which saves across a bunch of projects would take a fair amount of extra work. It is possible, but would require synchronization with projects which have been checked out etc. It is possible to put in a macro which runs on open (or close) which could do this, but it is easily bypassed by having macros security set to high. Therefore, I think if the project managers need or want this information, you should have them run it on demand. And if they do not want or need this information, they should not have to do anything.

    2) Field formulas calculate in realtime - so they get recalculated on every change. This can add up if there are many fields or many tasks. Macros run quickly, but they require the step of choosing to run the macro. So the question is whether it is preferred to have many small delays or one bigger delay. Choosing between the two would depend on how important the information is and how often it changes. I'm personally in favor of not automating the process. Leave it to the users. I'm also not happy with things which occur when I am trying to leave an application. I'd rather wait on start-up instead of have to wait for the application when I want to close it.

     

     


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Thursday, October 7, 2010 4:20 PM
    Moderator
  • Jack,

    Confronted with checked-out projects plus synchronizing published, draft, archived, reporting, and SharePoint databases, I can appreciate why you bring up the extra work to automate this "resourcing responsibility" task field with a macro that works on projects across a large portfolio.

    Giving a project-level macro to project managers to perform on demand could be feasible if there were a ready means to timestamp the last time a that macro ran in that project. (I understand that PS 2010 and 2007 do not track changes outside of Time Sheet.)

    From a programming perspective, can one use VBA to calculate a custom enterprise task field and then place a timestamp in a custom project field (whether project or enterprise)?

    I'd prefer to craft a task formula and avoid compromising data confidence by not knowing whether data in a task field that is essential for supporting business process is current or outdated.

    Thanks again, Jack, for your insightful comments.

    --John

     

    Thursday, October 7, 2010 5:43 PM
  • Hi John,

    sorry, I miss unterstood your question. I thougt you wanted to identify the direct children. If you want to find all of them, you can do that without any field or macro, but with two additinal mouse clicks:

    • activate auto filter
    • select the parent task you are interessted in
    • click on the "-" before task name to get a "+"
    • click on the "+"

    Seems that is what you want?

    Regarding "#ERROR": Have you customized your WBS in any way? I used the default and it is working on the lowest level without error.

    Regards
    Barbara

    Thursday, October 7, 2010 5:59 PM
    Moderator
  • Hi, Barbara,

    1. What I seek is a formula "concept"  that works on sets of tasks from each project in the portfolio rather than a manually applied auto-filter for a given task.

    One element in the filter would identify children, grandchildren, etc., based upon the criteria of the summary (parent) task. When the parent tasks fulfill criteria, then all tasks belonging to the family take on the same value as the parent. As each parent changes (because of a relationship between today's date and the start date of that parent; there may be other criteria, too), then its children get the same respective value. The complexity may not be doable in PS 2010 enterprise field formulas. What do you think?

    2. I'm also not clear as to whether PS 2010 (or 2007) supports selective exports of rows for each project meeting specific criteria across the portfolio to other PS 2010 instances. Situation is that source and receiving PS 2010 instances have similar project portfolios, yet the receiving PS 2010 instances will gain greater task details to support resource estimations as each project progresses through stage gates.

    A "dump" of all projects as compete backup and restore is doable. Can PS 2010 support one-way selective updates (modifications) to a receiving PS 2010 instance while adding tasks from the source instance that do not yet exist in receiving instance(s)? I see this as a form of non-realtime, portfolio-to-portfolio synchronization.)

    Thanks again, Barbara.

    --John

    Thursday, October 7, 2010 7:31 PM
  • Hi John,

    ad 1: seems that you want this to do a lot more. You want to have a kind of inheritance from summary lavel to child level for fields. Project only gives you a roll-up, no roll-down (for tasks, it does for assignments). I think you are correct, this complexity will exceed limits of custom fields. I think you will need to go for the macro solution. Regarding your question to Jack (From a programming perspective, can one use VBA to calculate a custom enterprise task field and then place a timestamp in a custom project field (whether project or enterprise)?) - yes, it is. Moreover, you could put a lot of your logic into this macro and would not need to work with several additional filters.

    ad 2: I am not sure if a selective update is possible. I can't remeber that I have ever read about something like that.

    Regards
    Barbara

    Thursday, October 7, 2010 7:52 PM
    Moderator
  • Barbara,

    Thanks for your detailed explanation. Functions that tap inheritance in breakdown structures, such as belongs and from, would empower business users to create solutions without being dependent on programmers.

    Selective updates of existing tasks in another instance could be somewhat feasible with creative field-by-field value updates. 

    On the other hand, here is a more realistic scenario:  At least every month, the resourcing responsibility field changes some parts of WBS tasks in various projects from "eventual" to "upcoming" tasks. As each project  in a master portfolio progresses through stage gates, PMs define more detailed work package tasks in that PS 2010 instance.  These new tasks, labelled "upcoming," would not be yet present in functional area PS 2010 instances charged with individual resourcing.

    Thus, PS 2010 needs to transfer periodically new "upcoming" tasks from the PM's PS 2010 master project instance to several receiving functional-area PS 2010 instances. 

    My concern that deploying this seemingly simple high-level business process through custom programming is not feasible for a portfolio of 1500-2000 long-term projects, as it would need intensive, ongoing IT support. 

    An alternative is having functional area resource schedulers enter master instance changes by hand across the portfolio into their functional area PS 2010 instances.

    A third possibility is doing resource planning in the PM master database. Portfolio size, long task duratinos, high number of assigned resources, concurrent useage, and large aggregate number of enterprise fields, especially formulas, would likely strain PS 2010 performance. Moreover, scheduling simplicity for project managers has high priority.

    I value your comments, Barbara, as I may be missing something fairly obvious concerning PS 2010 suitability (over which I have no control), even with a decent-size server farm.

    --John

     

    Friday, October 8, 2010 3:37 PM
  • Hi John,

    I haven't attended any of your meetings, so I think I miss some important aspects. Why do you want to go for several Project Server instances? With your third possibilty you are talking of only one instance, aren't you? If there are not any strong restrictions, I would go for that.

    In 2010, you have such a lot possibilties to make a high level long term planning and a detailed planning for the next weeks:

    • You have inactive tasks
    • You can use manually schedule tasks (e.g, for high level long term)
    • You can use resource booking types on project level
    • You can make use of workflow capabilities for moving between your stages (if I got them correctly)
    • You have generic ressources
    • You have as many enterprise custom fields as you want to (as long as you are not have too many with formulas on task level)
    • You can split your portfolio using departments, to make it easier tohandle
    • ......... and a lot more!

    I can even imagine, that scheduling will be easier for PMs if they have only one instance. In this instance, they can increase granularity of their planning periodically. Seems easier to me rather than change some fields to make sure that these tasks are transferred to another instance, where they have to move on with detailled plannig (or did I understand your approach wrong) ....

    If you have a simple macro as Jack suggested to fill Enterprise fields manually or onsave, you will reduce your performance issue dramatically and can implement an inhertitance with any logic you need.

    Regarding "Portfolio size, long task duratinos, high number of assigned resources, concurrent useage, and large aggregate number of enterprise fields, especially formulas, would likely strain PS 2010 performance": if you don't have too many enterprise task fields with formulas, this should not be an issue. One Project Server instance should work without performance issues if it may use all HW you need to run more than one instance - but I am no expert on that, perhaps someone else can have a comment?

    Regards
    Barbara

    Friday, October 8, 2010 5:55 PM
    Moderator
  • Barbara,

    I agree with a single project database whenever feasible, and that's the way I have stewarded large project databases over the past decade.

    Yes, I also concur that a custom VBA macro (with date stamp at the project level) can work adequately--with some email prompting to assure that the resourcing responsibility macro runs comprehensively.

    What is driving multiple instances of PS 2010 (beyond promised simplicity of scheduling)?

    Division of responsibility and avoiding double counting in  resource planning results in one group handling intermediate and long-term resource planning (along with project resource consolidation reporting) while functional areas deal with "current" resource estimates.

    High-level business process decisions have firmly moved towards designing multiple functional-area related PS project server instances. One major goal is to simplify first and deal with integration later. (It's always possible to cut and paste resource estimates into Excel worksheets for integration reporting.)

    Active and inactive projects don't apply to a long-term R&D portfolio in the way you described. (I'm excluding projects that are discontinued because they did not progress through stage gates.)

    Across 1500-2000 projects in a portfolio, each 15-20 year project has most detail in the earlier stages ("current"), less detail for upcoming planning in the near term ("ongoing"), and least detail for late stages("eventual"). From a resource forecasting perspective, the entire project remains in scope.

    Given the threshold recommendations in Microsoft documentation and presentations for PS 2010 on limiting custom task-type formulas to tens (not hundreds), short durations for tasks, and modest resource assignments (I currently work with over 1.4 million resource assignments), the technology case for deploying a single PS 2010 instance has not been compelling.)

    Perhaps the assumption of having individual PS 2010 portfolio instances that mirror the PM master database with only needful-for-the-functional-area data elements will result in less stringent technical requirements, particularly by reducing the number of task field formulas in any single PS instance.

    In PS 2010, one can reduce apparent complexity by displayng less choices for a given user set. On the other hand, a high overall number of custom enterprise attributes can still impact performance in PS 2010.

    I look forward to your comments, Barbara, in seeing ways to make PS 2010 work in this environment.

    Thanks kindly.

    --John 

     

    Friday, October 8, 2010 8:37 PM