none
Customizing Consolidated Schedule to roll up certain task info into project summaries RRS feed

  • Question

  •  Hello All,

    I am new to the forum and I would say an Intermediate user of MS project 2010 Pro. Most generally I am able to research and find what I need, I am however a bit perplexed with a certain issue. I am dealing with a manufacturing situation in a consolidated schedule with about 60 projects running at a any time. What I'm trying to accomplish is to roll up work, actual work, and work % complete for (4) processes; fab, assembly, ...etc. Can I use the auto columns and just change the name in the column field settings? How can I filter to roll up each tasks info into the proper column? Or is this a job for VBA? Any advise is appreciated.

    Thanks,

    Jeremy

    Thursday, July 11, 2013 2:35 PM

Answers

  • To those following this thread,

    Jeremy sent me a mock up of what he needed. As it turns out he could use simple custom fields with formulas to get the desired result. This is an excerpt of what I suggested:

    In your master file summary mock up I see each column has the units included with the data value (e.g. 0 hrs & 0%), so I assume you are using extra text fields for those. Although you can include the units in a text field, you cannot perform mathematical roll up of a text field, and that's what we want to do, at least for the work columns. I suggest you use extra number fields for the work and put the units in the column header (e.g. Fabrication Work (hrs)). Each entry in the data will then just be the raw value (i.e. 100 for 100 hrs). Now values can be rolled up. Leave the percent column as a text field as we do not need to mathematically roll that up.

    Here's the non-VBA approach. Let's assume you are using the Number1 field for Fabrication work, the Number2 field for Fab Actual Work, and the Text1 field for Fab % complete. Do a similar thing for the other processes (i.e. assembly, electrical and paint). In addition, set aside the Flag1, Flag2, Flag3, and Flag4 to "tag" fabrication, assembly, electrical, and paint tasks. In each subproject follow these steps:
    a. Rename the fields as desired
    b. Set the Flag1 field for all fabrication tasks you want rolled up, Flag2 for all assembly tasks, etc.
    c. Set all the Flag fields (i.e. 1,2,3 & 4) at the summary level
    c. Customize the Text1 field with the following formula:
    IIf([Flag1],([Number2]/[Number1]*100 & " %"),"")
    d. Set the "calculation for task and group summary rows" to be "sum" for the Number fields and select "use formula" for the text fields

    As data is entered into the work and actual work fields for their respective process, the values will roll up as you want and the percent will be calculated at the task and summary level.

    John

    • Marked as answer by jssdlr Monday, July 15, 2013 11:15 AM
    Friday, July 12, 2013 10:29 PM

All replies

  • jeremy,

    Welcome to the forum. I need to ask a few question in order to better understand what you need so that we may help you.

    First and foremost, do you have SP1 installed?

    When you say a consolidated schedule I assume you are referring to a dynamic master with 60 inserted subprojects. Is that correct? If not, what exactly is your "consolidated schedule"?

    The Work, Actual Work and % Work Complete fields are already automatically rolled up to summary level when viewed at master level. However, it sounds like you want to roll up those values for what I assume are specific summary groups down in your file (e.g. fab, assembly, etc.). You mention "auto columns" and changing the name in the column field settings. What are "auto columns" and what exactly do you mean by changing the column settings?

    Have you tried using Project's grouping feature?

    John

    Thursday, July 11, 2013 2:57 PM
  •  John,

     Thank you for your reply and I apologize for the incomplete info. Yes we do have SP1 installed. You are correct in your assumption of my consolidated schedule. In reference to the auto columns: Project already has the columns that I need, in the way of calculating the info I'm looking to track i.e. the work, actual work and %work complete. In effect can I insert (3) work columns and simply right click on the column and change the column heading to; Fab Work, Assembly work.. etc, and still get the correct calculations. What I would like to see happen is that each of the tasks( fab, assembly,..etc.) work, Actual work, and work% complete be calculated in each projects summary roll up, for each of the tasks. At project startup the hours are allocated (estimated) for each process, I would like to be able to track the progress of each process for each project and have it shown on the consolidated schedule, as there are many team members who would benefit from this status. Sorry for being so lengthy. I hope I relayed this correctly. Thank you for your patience.

    Jeremy

    Thursday, July 11, 2013 3:20 PM
  • Jeremy,

    Okay so by "auto column" I'm guessing you simply mean the columns showing Project's standard fields like Name, Duration, Start, FInish, etc. Right?

    And yes you can insert extra fields (e.g. Text1, Number!, etc.), as new a column and customize it with a formula which it kind of sounds like you're doing since you say you "still get the correct calculation". Can you expand upon what you have done in this regard?

    If you have created these custom fields for fab work, assembly work, etc. you can get them to roll up to summary level by selecting the option to calculate a sum for task and group summary rows in the custom fields window.

    You talk about how you want to track progress of each process's hours. What exactly do you mean by track progress, or should I say, how would you like to do that? Are you familiar with Project's baselines fields? They are set up to allow the user the ability to track current progress against the original plan, saved as a baseline. However, baseline fields do not apply to custom fields.

    Don't worry about being lengthy although succinct is best. The more you explain, the better we can understand.

    John

    Thursday, July 11, 2013 4:38 PM
  •  John,

    Yes, by "auto column" I mean the standard fields. My apologies. As my limited, yet expanding knowledge goes, I am not very well versed in formulas or VBA. I have customized columns for other purposes but they are all manual input. So any guidance in that arena is greatly appreciated. (e.g. any reference material or good books to study).

    I am familiar with baselines. I baseline all projects prior to starting. I only use one baseline for the overall project.

    I would like to track the progress so that the original duration auto rolls up and as the actual work is input it automatically reflects the % work complete just as the standard fields do, but for each individual process for each individual project.

    So as a visual...the projects are listed down the left; fab work, fab actual work, fab % work complete; assembly work, assembly actual work, assembly % work complete, etc...... would be the columns and shown for each project. Just unsure how to get each individual task to roll up to its respective column heading.

    My thought is that our manufacturing mngr. would be able to input the actual hours from his monitor so others would be able to see the updates as it progresses through the process daily.

    I would like to get this set up as in the future I was hoping to be able to communicate costs in a similar fashion. Thank you.

    Jeremy 

    Thursday, July 11, 2013 5:43 PM
  • Jeremy,

    As far as reading materials, you could check out the books by: MSProjectExperts.com but there is also a lot of useful information in forums, blogs and the like accessible via a carefully worded search on the internet.

    You mention duration rolling up. Duration doesn't roll up like work or cost. Duration is simply the span of working time between the start of a task and its finish. For a summary line, it is the start of the earliest task under the summary to the finish of the latest task under the summary.

    If you set up custom field to represent your functional processes (e.g. Number1=fab work, Number2=actual fab work, Number3=Number2/Number1*100 to give % fab work complete), then all you need to do to roll those up to summary level is to set the option to "sum" for task and group summary rows for the Number1 and Number2 fields. For the Number3 field, you just want to use the formula at summary line level. But, to make all this appear in the master file, the custom fields and their formulas must reside in each subproject file. However since the view parameters at master level are unique to the master, you will need to rename those same fields at master level so they will appear on the master level summary lines. After all that is set up, your manufacturing manager will be able to open the master and update the actual values at master level.

    Do we have it?

    Disclaimer: After all that good happy stuff, be advised that a dynamic master is prone to corruption. To lessen the possibility, all files should be kept in a single directory, ideally not on a network, files must not be renamed, moved or "saved off" as backup.

    John

    Thursday, July 11, 2013 6:45 PM
  • John,

    I have played with the formula and set up a mock project.

    The formula is working beautifully. But I think I'm misunderstanding how you attribute or connect "number1" (which when customizing is renamed Fab Work) to the actual task "fabrication" in the project.

    How does the customized column know to use the data from that task? I can input data into the custom columns and it will calculate the values, but it doesn't pull the data from the task. So, if I have 120hrs. for Fabrication(task) is there a way for "number1" to automatically pull the 120 hrs. from Fabrication(task)? It did roll up, however, each time I put in new data in a different task in the project it would roll the most recent data.

    Also do I need to incorporate a text type field in order to get the % sign to show up in a percent column? If so, how exactly?

     It is definitely further than I have been, and if I'm being honest, its pretty dang exciting! Please advise to where I may be going wrong. Thank You again!

    Jeremy

    Thursday, July 11, 2013 7:31 PM
  • Jeremy,

    Custom fields apply to each and every task line so the "association" is to that task line. I assumed you had a series of tasks that were all part of the fabrication function. Therefore each task had an individual contribution to the total fabrication process and summing them up gave the total fab data. If that is not correct, (i.e. if fabrication effort is embodied within only one or a select few tasks in your whole plan), then this method, (i.e. simple formulas in custom fields), will not work for you. However what will work is VBA and it's much more "dang exciting" than formulas in custom fields.

    How big is you mock project? If it's simple enough maybe you could take a screenshot and post to this forum, (you can post up to two images per message). Given some verbiage to go with the image we could likely get you started. Or, if you think it's a little more involved than that, you could send me your mock up and we could work offline.

    And just for reference, this is how you would use a formula in a custom text field to include the percent sign:

    Text1=[Number1]/[Number2]*100 & " %" 

    John

    Thursday, July 11, 2013 9:24 PM
  •  John,

    Good morning. I have been trying to send out a screenshot to show what I have so far, but I can't seem to be able to submit them, I keep getting a dialogue box saying that I'm not verified? I would be happy to send them to you through a different avenue, whatever your preference.

    As a side note our manufacturing is set-up in our schedule something like this:

                                Duration          Start            Finish

    Manufacturing

         Fabrication

         Assembly

         Electrical

         Paint and Prep 

    So if I can try to clarify from your last thread:  Manufacturing is a summary task, and fab, assy., elec., p&p each are subtasks of manufacturing. There are separate processes to complete the fabrication, but the hours are assigned as a whole. I assign the resources accordingly for the in-process activities. I apologize for not clarifying that much sooner.

    If I am reading correctly then, it appears that I will need to work on the VBA?

    Thank you for your time and knowledge.

    Jeremy

    Friday, July 12, 2013 12:52 PM
  • Jeremy,

    I don't know what it takes to be "verified". I thought if you had a valid Microsoft ID for posting, that you could use all the services (e.g. attach images). Obviously there is more to it.

    With regard to your file structure, what you have makes sense, a typical functionally based plan. I need to see your actual mock up in order to determine the best approach but right now it looks like some VBA is needed.

    You can send me your mock up file as long as it is not too large (i.e. less than 1M zipped) with an explanation of your end goal. I will ask some questions.

    John

    jensenljatatfastmaildotdotfm

    (remove obvious redundancies and the 7th character is a letter)

    Friday, July 12, 2013 2:44 PM
  •  John,

    Thank you. I will send something asap.

    Jeremy

    Friday, July 12, 2013 3:00 PM
  • John,

    I recently sent out an e-mail to you at the address you provided. I assumed that the 7th character was an l (L). If this is incorrect or you did not receive the message, please let me know. If you did receive the message please disregard this post and I will await your reply. Thank you.

    Jeremy

    Friday, July 12, 2013 5:09 PM
  • To those following this thread,

    Jeremy sent me a mock up of what he needed. As it turns out he could use simple custom fields with formulas to get the desired result. This is an excerpt of what I suggested:

    In your master file summary mock up I see each column has the units included with the data value (e.g. 0 hrs & 0%), so I assume you are using extra text fields for those. Although you can include the units in a text field, you cannot perform mathematical roll up of a text field, and that's what we want to do, at least for the work columns. I suggest you use extra number fields for the work and put the units in the column header (e.g. Fabrication Work (hrs)). Each entry in the data will then just be the raw value (i.e. 100 for 100 hrs). Now values can be rolled up. Leave the percent column as a text field as we do not need to mathematically roll that up.

    Here's the non-VBA approach. Let's assume you are using the Number1 field for Fabrication work, the Number2 field for Fab Actual Work, and the Text1 field for Fab % complete. Do a similar thing for the other processes (i.e. assembly, electrical and paint). In addition, set aside the Flag1, Flag2, Flag3, and Flag4 to "tag" fabrication, assembly, electrical, and paint tasks. In each subproject follow these steps:
    a. Rename the fields as desired
    b. Set the Flag1 field for all fabrication tasks you want rolled up, Flag2 for all assembly tasks, etc.
    c. Set all the Flag fields (i.e. 1,2,3 & 4) at the summary level
    c. Customize the Text1 field with the following formula:
    IIf([Flag1],([Number2]/[Number1]*100 & " %"),"")
    d. Set the "calculation for task and group summary rows" to be "sum" for the Number fields and select "use formula" for the text fields

    As data is entered into the work and actual work fields for their respective process, the values will roll up as you want and the percent will be calculated at the task and summary level.

    John

    • Marked as answer by jssdlr Monday, July 15, 2013 11:15 AM
    Friday, July 12, 2013 10:29 PM
  •  John,

    Thank you again for all of the assistance you provided me. Having people with the knowledge and the willingness to help really make these forums a great resource.

    Jeremy

    Monday, July 15, 2013 11:29 AM
  • Jeremy,

    You're welcome and thanks for the feedback.

    John

    Monday, July 15, 2013 2:44 PM