none
project column indicating health based on tasks progress RRS feed

  • Question

  • Would like to have a project center view with a column showing project health, however we want that health indicator to not be manually changed, but be dynamic based on these requirements:

    • If all project tasks are on time = green
    • if 20% project tasks are late = yellow
    • if 40% project tasks are late = orange
    • if 60% project tasks are late = red

    How can I create this enterprise custom fields to accomodate this?  thanks!


    -Chad

    Thursday, September 27, 2012 7:00 PM

Answers

  • Chad, my apologies for jumping ahead in the process. I'm happy to help but didn't want to spend time on something that might have been (and probably will be) familiar territory.

    First off, I assume you're using Project Server 2010. It's actually good practice to do your formula development in MS Project desktop before transferring the final version to Project Server - it's much easier to test and debug that way. So open up Project on your desktop...

      • From the ribbon Project:Properties -> Custom fields. Make sure the Task radio button is selected and pick Number from the drop down list of field types.
      • Pick an unused field (e.g. Number 1) and rename it to Task Count. Select the radio button for formula. (At this point I would key the formula but there are selectable functions and fields that would help until you are familiar with it all.) Select Function->General->IIf
      • Highlight the 'expression' part, select the Field button->Flag->Summary. (Summary is a Project standard field automatically populated as Yes/No). Highlight the truepart of the formula and replace it with 0, Highlight the falsepart of the formula and replace it with 1. Hit OK - and again with the warning message. Your formula should look like this: IIf([Summary],0,1) 
      • On the Calculation for summary rows, select the Rollup radio button and Sum from the drop-down.
      • Hit OK to close the custom field dialog box. In the Gantt chart view, insert the column for Task Count. Hit F9 to recalculate just to be on the safe side.
      • All well so far? Let's elaborate the formula to exclude tasks that are completed. Reopen the formula dialog box and place the cursor immediately after [Summary] and before the comma.
      • Select OR from the row of operators below the formula pane, then Field->Number->%Complete. Select = from the row of operators and type 100. Your formula should now look like:  IIf([Summary] Or [% Complete]=100,0,1)
      • You could add another OR statement to ignore Field->Flag->[Milestone] if you felt like it.
      • Repeat the whole process to count the late tasks. Call the new task number field Late Task Count. Use the same basic formula as the Task Count field (you could copy and paste it). Edit the formula to include another OR condition. Select operators OR, then NOT, the select Field->Date->Finish Variance and type in > 480. It should look like this:   IIf([Summary] Or [% Complete]=100 Or Not [Finish Variance]>480,0,1)
      • Hit OK (twice) and set the rollup option to Sum. Exit custom field editing, display the Late Task Count column and hit F9. You can adjust the 480 value in the line above. Numerical values of Finish Variance (and others) are in work minutes, not days, so 480 represents 1 day late.
      • Create another number field (in Project you'll have to do this as a task field. In Project Server we'll do it as a Project level number field) for Late Task Ratio. The formula will look a bit like this: IIf([Number1]=0,0,[Number2]/[Number1]) where Number 1 and Number 2 represent Task Count and Late Task Count. For rollup, select Use Formula. Remember to display the Project summary task using File->Options->Advanced options
      • In the Custom Field definition, select Graphical Indicators and set the project summary criteria to be something like Less than or equal to 0.2 - Green, Greater than 0.6 - Red, Greater than 0.4 - orange, Greater than 0.2 - Yellow.

    The above should let you check that the formulae work as you want. Then it will be a question of importing them into Project Server.

    I hope this is all intelligible.

       Graham

    • Marked as answer by philldogger Tuesday, October 2, 2012 2:27 AM
    Sunday, September 30, 2012 2:55 PM

All replies

  • Hi Chad

    Please visit the folowing link.

    Using Formulas and Graphical Indicators with Custom Fields

    Hope that helps you.


    Manish

    Friday, September 28, 2012 6:32 AM
  • Chad, You'll need a couple of enterprise custom task number fields - one to count the tasks and one to count the tasks that are late - and one enterprise custom project number to calculate the percentage and show the indicator.

    Task count: iif([summary]=no,1,0)   Roll up using Sum. Make this more complicated if you want to ignore completed tasks, milestones, etc.

    Task late: iif ([summary]=no, iif ([finish variance]>0, 1,0),0)    Roll up using Sum. Make this more complicated if you want to ignore completed tasks, milestones, etc. Set the iif ([finish variance]>0 to be a more elegant [task late expression] to result in a logical Yes/No depending on your criteria for a task being late.

    Healthy project: iif ([task count]>0, [task late]/[task count],0)   Include a *100 factor if you want the tool tip to show a number between 0 and 100. Set the indicators according to the Healthy project value.

    And Bob's your uncle.

                Graham 

    Friday, September 28, 2012 7:33 PM
  • Thanks for the info Graham, however I just don't know how to create formulas at all, so when you say "make it more complicated to ignore completed tasks, milestones"....I get lost since I would like to but have no clue how to write that formula.  Also creating a more elegant task late expression would be ideal, but again, no clue.  That's why I'm posting to the forum to have someone really spell out the formula I need to achieve what my OP said.

    Your post was most helpful and I'm on the right track, just need help finishing it out.


    -Chad

    Sunday, September 30, 2012 3:15 AM
  • Chad, my apologies for jumping ahead in the process. I'm happy to help but didn't want to spend time on something that might have been (and probably will be) familiar territory.

    First off, I assume you're using Project Server 2010. It's actually good practice to do your formula development in MS Project desktop before transferring the final version to Project Server - it's much easier to test and debug that way. So open up Project on your desktop...

      • From the ribbon Project:Properties -> Custom fields. Make sure the Task radio button is selected and pick Number from the drop down list of field types.
      • Pick an unused field (e.g. Number 1) and rename it to Task Count. Select the radio button for formula. (At this point I would key the formula but there are selectable functions and fields that would help until you are familiar with it all.) Select Function->General->IIf
      • Highlight the 'expression' part, select the Field button->Flag->Summary. (Summary is a Project standard field automatically populated as Yes/No). Highlight the truepart of the formula and replace it with 0, Highlight the falsepart of the formula and replace it with 1. Hit OK - and again with the warning message. Your formula should look like this: IIf([Summary],0,1) 
      • On the Calculation for summary rows, select the Rollup radio button and Sum from the drop-down.
      • Hit OK to close the custom field dialog box. In the Gantt chart view, insert the column for Task Count. Hit F9 to recalculate just to be on the safe side.
      • All well so far? Let's elaborate the formula to exclude tasks that are completed. Reopen the formula dialog box and place the cursor immediately after [Summary] and before the comma.
      • Select OR from the row of operators below the formula pane, then Field->Number->%Complete. Select = from the row of operators and type 100. Your formula should now look like:  IIf([Summary] Or [% Complete]=100,0,1)
      • You could add another OR statement to ignore Field->Flag->[Milestone] if you felt like it.
      • Repeat the whole process to count the late tasks. Call the new task number field Late Task Count. Use the same basic formula as the Task Count field (you could copy and paste it). Edit the formula to include another OR condition. Select operators OR, then NOT, the select Field->Date->Finish Variance and type in > 480. It should look like this:   IIf([Summary] Or [% Complete]=100 Or Not [Finish Variance]>480,0,1)
      • Hit OK (twice) and set the rollup option to Sum. Exit custom field editing, display the Late Task Count column and hit F9. You can adjust the 480 value in the line above. Numerical values of Finish Variance (and others) are in work minutes, not days, so 480 represents 1 day late.
      • Create another number field (in Project you'll have to do this as a task field. In Project Server we'll do it as a Project level number field) for Late Task Ratio. The formula will look a bit like this: IIf([Number1]=0,0,[Number2]/[Number1]) where Number 1 and Number 2 represent Task Count and Late Task Count. For rollup, select Use Formula. Remember to display the Project summary task using File->Options->Advanced options
      • In the Custom Field definition, select Graphical Indicators and set the project summary criteria to be something like Less than or equal to 0.2 - Green, Greater than 0.6 - Red, Greater than 0.4 - orange, Greater than 0.2 - Yellow.

    The above should let you check that the formulae work as you want. Then it will be a question of importing them into Project Server.

    I hope this is all intelligible.

       Graham

    • Marked as answer by philldogger Tuesday, October 2, 2012 2:27 AM
    Sunday, September 30, 2012 2:55 PM
  • Well I thought this was working, but the graphical indicators either show green or red, no orange or yellow.  I did it exactly as you listed, but once a project has one late task, the project center view shows it as red.  I dont think the percentages are working right.

    Here's the project center view custom field formula:

    IIf([IT Task Count] = 0, 0, [IT Task Late] / [IT Task Count])

    and the indicators are set for Less than or equal to 0.2 - Green, Greater than 0.6 - Red, Greater than 0.4 - orange, Greater than 0.2 - Yellow


    -Chad

    Thursday, October 11, 2012 11:23 PM
  • When I add the IT Task Late as a column it's showing a 1 for all the tasks, late or ontime...completed however shows a zero which is good.  But I can see this must be the issue since IT Task Count and IT Task Late are both showing 1 for all the tasks, so when they divide in the project view formula...its always 1.


    -Chad

    Here is the IT Task Late formula:

    IIf([Summary] Or [% Complete] = 100 Or Not [Finish Variance] > 480, 0, 1)

    • Edited by philldogger Thursday, October 11, 2012 11:43 PM
    Thursday, October 11, 2012 11:40 PM
  • Hi Graham, 

    I saw this post and i actually implemented it in my ms project file. so thank you. But i was wondering if you could help me. 

    since it's such an old post i will wait till you reply to ask my question.

    thank you,

    Pamela

    Monday, July 2, 2018 2:01 PM