none
Custom Field Count for Task Group

    Question

  • Hi!

    I'm trying to create a Custom Field that will count the number of Task Groups based on a specific filter. 

    In my example (see screenshot), i am creating a subdivision project schedule. i want to be able to count the number of houses to be constructed. i have created a custom field for "Task Category" which would enable me to specify a Task Group as 1 House Count. I'm hoping to have the roll-up count the Sum of the Tasks Groups which have "Construction" as Task Category. However, as the value for Task Groups are roll-ups themselves, they count the value of their Sub Tasks, which are currently "0". In my example, i'm hoping to get a House Count of "4".

    Is there a sensible way for me to do this from within Microsoft Project?

    Thank you


    Tuesday, December 26, 2017 5:01 PM

Answers

  • As always, John's solution is a simple and reliable one. I may offer another one, it also works but a little cumbersome, see below:

    Task Sheet view:

    Steps:

    - Create a simple group on the Summary field, do not forget to tick the checkbox "Show summary tasks" in the Group Definition dialog box.

    - Apply it to the view and then apply AutoFilter to the text field containing the text "Construction" (it is Text10 here).

    - Next use a custom number field with the setting "Count All" as the rollup option in the Custom Fields dialog box (it is Number7). Number7 cell on the Summary:Yes line will show the number of construction summary lines.

    Creating a view with those elements will make it a lot easier. 

    Text12 is the bonus with the formula iif( [Group By Summary],"Constructions: " & [Number7],[Name]). This will work only with the "Use formula" option selected in the Custom Fields dialog box for Text12. 

    OK, this is what I offer as an alternative. Happy new year to all...


    Ismet Kocaman | eBook on Formulas

    Tuesday, December 26, 2017 7:55 PM

All replies

  • John,

    I'm sure there is a way to use VBA to compute such a thing. I can imagine that it's non-trivial and on Boxing Day I must admit that i will find it difficult to turn on the brain sufficiently to suggest VBA code to to do this.  Others here might. 

    That being said, I'm more than curious why you would want this and what value does it provide you or your bosses/clients?  What's the purpose? What are you trying to do?


    --rms www.rmschneider.com

    Tuesday, December 26, 2017 6:43 PM
  • John,

    There are various ways to achieve the house count. One is with a indirect way using formulas and another is with some VBA. I'll show the formula method. If you're interested in a VBA solution, let me know and I'll code that up.

    I notice that the first subtask under each Unit summary line is the "General Preliminaries". Assuming this is true for all Units, you can utilize this task line as the reference for each house. Carrying that through with the custom field and formulas shown below. You get the house count at summary level.

    Hope this helps.

    John



    Tuesday, December 26, 2017 7:02 PM
  • As always, John's solution is a simple and reliable one. I may offer another one, it also works but a little cumbersome, see below:

    Task Sheet view:

    Steps:

    - Create a simple group on the Summary field, do not forget to tick the checkbox "Show summary tasks" in the Group Definition dialog box.

    - Apply it to the view and then apply AutoFilter to the text field containing the text "Construction" (it is Text10 here).

    - Next use a custom number field with the setting "Count All" as the rollup option in the Custom Fields dialog box (it is Number7). Number7 cell on the Summary:Yes line will show the number of construction summary lines.

    Creating a view with those elements will make it a lot easier. 

    Text12 is the bonus with the formula iif( [Group By Summary],"Constructions: " & [Number7],[Name]). This will work only with the "Use formula" option selected in the Custom Fields dialog box for Text12. 

    OK, this is what I offer as an alternative. Happy new year to all...


    Ismet Kocaman | eBook on Formulas

    Tuesday, December 26, 2017 7:55 PM
  • Hello! Thank you very much to everyone for the generous responses. I apologize for replying only now -- I wanted to respond once I've already identified the solution. So after some time.. :)

    My main objective was to get the "Housing Equivalent" for each construction task and for overall based on a specific time period. I was trying to get the count for the Construction tasks so that I can use it to divide the overall project cost in order to get the "Average House Cost". 

    Thanks the suggestions I received here, I was able to get the count of the Construction tasks, but I still encountered the following limitations in MS Project reporting. Let me go thru the solutions I went thru:

    1. Project BI Reporting

    -This would have been ideal as it is already available out-of-the-box and I just had to do some drag-n-drop of fields. The main issue was the 100 row count limitation output in the report. The subdivision projects we are working on had 300+ houses to be constructed and had to be displayed row by row in the report. 

    2. Project Visual Reports (Export to Excel)

    -I did not have the 100 row limitation here anymore. However, I found out that I am not able to use calculated custom fields. This was another dead end. 

    3. Power BI

    -The last thing I wanted was to use a separate application just for reporting. But it seemed I had no other choice, so I decided to bite the bullet. As with anything new, it was a bit challenging at first but I eventually found out that the learning curve was not too steep. I really like the ability to access Project Online timephased data. This enabled us to generate the report exactly how we needed it to be.

    To filter Tasks which are specific for construction, I first created a filter for [ParentTaskName] = "CONSTRUCTION"

    Average House Cost = AVERAGE[TaskCost]

    House Equivalent = DIVIDE ([TaskCost],[Average House Cost]]

    Sharing a sample screenshot of the report we have now --

    Thanks again!


    **I am ticking Ismet's response as the "answer" as it addressed my original query of counting the Task Groups without relying on a specific name for the subtasks
    Sunday, March 11, 2018 12:48 AM
  • John,

    Thank you for the feedback and marking as answer. And thank you for sharing your solution.

    Ismet

    Sunday, March 11, 2018 5:55 PM