Custom Field Indicators RRS feed

  • Question

  • I'm trying to create a custom field that would show:

    Green <= 10% Resource Name is Null
    Yellow = 11-19% Resource Name is Null
    Red = 20% Resource Name is Null

    I'm a newbie and not sure what my formula would be to show my indicators.

    Monday, July 3, 2017 11:28 PM

All replies

  • Hi,

    What are you refering to for the percentages? 10% of what? Are you talking about a task, project or resource custom field?

    Do you mean: if 10% of the resource name is null, then green? What does that mean? A resource MUST have a name, so resource name is null cannot occur.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller

    Tuesday, July 4, 2017 10:08 AM
  • What was given to me was:

    For any Task (that is not a milestone):

    [Resource Name]

    • Green: <= 10 % have Resource Name NULL
    • Yellow: 11-19[BB1] % have Resource Name NULL
    • Red: >=20% have Resource Name NULL

    [Hours] (Should this be hours or work[BB2] ?)

    • Green: <= 10 % have Hours = 0
    • Yellow: 11-19% have Hours = 0
    • Red: >=20% have Hours = 0

    [Task Due Date]

    • Green: <= 10 % have Due Date < today
    • Yellow: 11-19[BB3] % have Due Date < today
    • Red: >=20% have Due Date < today


    For any Critical Task (that is not a milestone):

    [Resource Name]

    • Green: <= 2 have Resource Name NULL
    • Yellow: 3-4 have Resource Name NULL
    • Red: >=5 have Resource Name NULL

    [Hours] (Should this be hours or work[BB4] ?)

    • Green: <= 2 have Hours = 0
    • Yellow: 3-4 have Hours = 0
    • Red: >=5 have Hours = 0

    [Task Due Date]

    • Green: <= 2 have Due Date < today
    • Yellow: 3-4 have Due Date < today
    • Red: >=5 have Due Date < today


    For any Milestone:

    [Task Due Date]

    • Green: 0 have Due Date < today
    • Yellow: 1 has Due Date < today
    • Red: >=2 have Due Date < today

    Wednesday, July 26, 2017 3:38 PM
  • Kwilliams1130,

    Sorry but you still didn't answer Guillaume's basic questions. Percent of what? What does "NULL" mean with respect to a resource? Also what is your definition of "due date"? Is it the scheduled finish, baseline finish or what?

    If what you just posted is what someone gave you, then you need to go back and ask them these questions.


    Wednesday, July 26, 2017 5:33 PM
  • NULL I am thinking means if there is no resources assigned.  NULL = Blank.

    Due Date:  They have a field for when the task is due

    I will also clarify with them.



    Thursday, July 27, 2017 1:52 PM
  • Kim,

    To be honest, it sounds like you just guessing yourself and unfortunately none of it makes any sense. For example, take the first criteria

    For any Task (that is not a milestone):

    [Resource Name]

    • Green: <= 10 % have Resource Name NULL

    This is at a task level so if that task has no resources assigned (i.e. NULL) then what is the 10% measured against? And for that matter, what exactly is supposed to be green, the whole task line, a custom indicator field, or what?

    The one interpretation of the above that might make sense is to analyze the whole plan and for those non-milestone tasks that do not have any resources assigned, set a flag at the Project Summary Task to the color indicated by the percentage of tasks. For example, if less than 10% of the performance tasks have a resource assigned, set the flag to green.

    As far as the "due date", yes hopefully that is a Project field but which one?

    Please don't misunderstand, we are not trying to give you a hard time. I'm guessing you were given the task of implementing something in Project by someone who failed to clarify or explain what they were thinking. We'll be more than happy to help you but in order to do that we need clarification.


    Thursday, July 27, 2017 2:45 PM
  • This is the response I received from the requester:  "All tasks (that are not headings or milestones) must have resource(s) assigned to them, so they are wanting an indicator on the <=10% that do not, etc.

    It's been a long time since I've worked with PWA and when I did, I never had to do any Health Indicators.

    No, I don't think you are giving me a hard time, I appreciate any help and even if that has me pushing back at them for more clarification.

    I appreciate the help and response. :)

    Friday, July 28, 2017 6:09 PM
  • Kim,

    You mention PWA so that tells me you are working in a Project Server environment. I don't do Project Server so I don't know what metrics might be available at enterprise level. However, at client level (i.e. Project itself) the metrics you are seeking are not something that can be done with formulas in a custom field. Since the metrics are for the whole project, a macro is required to look at all tasks for the various criteria and then create the green, yellow and red indicators for 7 custom fields; three each for non-milestone tasks (resource, work and due date), three each for non-milestone non-critical tasks(resource, work and due date), and one for milestone tasks (due date).

    I can write that macro for you but I still need to know what you are defining as the "due date". Is it the scheduled finish date, the baseline finish date, or some other field you have designated as the "due date"? If it's another field, which field exactly?

    Also in the criteria list some of the items have a "BBX" in brackets. What are those?

    Further, it's a little difficult to imagine any kind of working plan that has missing resources or zero work for any performance task (i.e. non-summary line or non-milestone), so I'm a little confused as to the value of this set of metrics. If the metrics are for a plan that is in development then the metrics for due dates is meaningless since the plan is not yet active. Perhaps you could enlighten me on the intended value of these metrics.


    Friday, July 28, 2017 8:09 PM
  • The only place I can find a Due Date is at the Project Tasks level.

    The "BBX" in brackets got copied over from the word document, they are comments in Word (which also don't make sense).

    From what I understand, is they are wanting to be able to go to the Project Center and see the status (stoplight health) of these metrics.  They may not know exactly what they want or what can be done, but they want to be able to see some type of status on the Project Center page.

    FYI...the person that was responsible for the Project Server was let go after the merger, so they assigned this task to me.  I'm trying to figure it out, but I don't have a local copy of MS Project, I only have access to the Project sites and the PWA.  I've watched a lot of youtube videos and to me it seems like I'm missing something, like having MS Project, maybe.

    I really appreciate you taking the time to help me.

    Saturday, July 29, 2017 3:55 PM
  • Kim,

    As I noted earlier I don't do Project Server, so this one is out of my range of expertise. I'll see if I can get Guillaume engaged in this thread again and hopefully he can help since he does have Project Server expertise.


    Saturday, July 29, 2017 4:57 PM
  • Thank you, much appreciated!
    Sunday, July 30, 2017 11:19 PM
  • Hi,

    I jump back in upon John request. This represents quite an effort to set those KPIs and I won't have time to fully test the setup which will need to be implemented first on a test environment to ensure that all steps work as expected. Here is the general steps you could follow.

    Basically, I'd create task custom fields to calculate your percentages. The 3 first would be the 3 denominators for the caculations. The objective of those 3 fields are to calculate the number of tasks, critical tasks and milestones so you can use them to calculate the %. Setup the 3 fields with the formulas below and configure the rollup as a sum.

    • number1: normal task excluding milestones: iif([is milestone]=NO;1;0)
    • number2: critical task excluding milestones: iif([critical]=YES and [is milestone]=NO;1;0)
    • number3: milestones: iif([is milestone]=YES;1;0)

    Then for the numerator, same principe, same rollup.

    • number4: resource on task: IIf([Resource Names]="";1;0)
    • number5: work on task: IIf([work]>0;1;0)
    • number6: due date: IIf([DUE DATE]="NA";0;1) assuming DUE DATE is another existing date custom field

    In PWA, since those 6 custom fields are at task level, you'll need to create the 6 equivalent custom fields, but at project level to roll up the 6 numbers. It will be: number1 (project)=number1 (task), number2 (project)=number2 (task), etc... Not that you'll need to give relevant names to your custom fields.

    Then create 3 project custom fields associated with graphical indicators. Note that you'll have to add those fields to your project center relevant views. Note also that you'll have to set the custom field format as percentage. You could for example use text custom fields with cstr(FORMULA * 100) & " % ", FORMULA being the formulas below.

    For tasks (not a milestones)

    • resource KPI= number4/number1
    • hours KPI= number5/number1
    • due date KPI= number6/number1

    For critical tasks (not a milestones)

    • resource KPI= number4/number2
    • hours KPI= number5/number2
    • due date KPI= number6/number2

    For milestones

    • resource KPI= number4/number3
    • hours KPI= number5/number3
    • due date KPI= number6/number3

    Here are some links for setting up graphical indicators:

    Once again, I didn't test the proposition above so you might need to tweake it and test it. It also requires to have some knowledge about Project Server, views, custom fields, formulas, rollup, graphical indicators. Note also that too many custom fields (task or project) might impact your instance performance due to extended calculations.

    For final comments, note that you might see that project custom fields are not always correctly caclulated in the project center views and might require to set up an autopublish jobs to recalculate formulas. See my post about this free app.

    John, what do you think? Maybe it could help to have the project level calculations in VBA if needed. Does it make sense to you on the overall process? You might want to keep following thiçs thread with me, you are quite experienced with formulas in Project Pro.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller

    Monday, July 31, 2017 7:25 AM
  • Guillaume,

    Wow, I think Kim's head is spinning because mine sure is :-)

    Although this can probably be done with custom fields, if my interpretation of the criteria is correct, it will take not 6 number fields but 10 number fields and 7 text fields to capture all the KPIs. As you noted, each of the number fields need to be rolled up as a "sum" and each of the text fields need to have the formula applied at summary level. That's one reason I'd do all this in VBA. Seven text fields are needed to present the KPIs whether as data or as a graphical indicator but all the number field data and calculations would be embedded in the macro code. However, here are the custom fields if done with custom field formulas:


    Number2=IIf([Milestone]=False And [Critical]=True,1,0)


    Number4=IIf([Milestone]=False And [Resource Names]="",1,0)

    Number5=IIf([Milestone]=False And [Work]=0,1,0)

    Number6=IIf([Milestone]=False And [Finish]<[Date1],1,0)

    Number7=IIf([Milestone]=False And [Critical]=True And [Resource Names]="",1,0)

    Number8=IIf([Milestone]=False And [Critical]=True And [Work]=0,1,0)

    Number9=IIf([Milestone]=False And [Critical]=True And [Finish]<[Date1],1,0)

    Number10=IIf([Milestone]=True And [Finish]<[Date1],1,0)

    And then to calculate the percentages:








    Then of course, each text field needs to be set up for the correct green, yellow and red graphical indicators as you indicated.

    As far as your suggested process to integrate this into the Project Server environment, I can't comment since all that is beyond my level of expertise.

    Hope this helps.


    Monday, July 31, 2017 3:43 PM
  • Indeed John, this is a lot of custom fields (thanks for correcting me on the number of custom fields required). I do agree the task custom fields could be managed in an easier way with some VBA. Then Kim will just have to configure the text custom fields in PWA for calculating % and showing graphical indicators.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller

    Tuesday, August 1, 2017 6:52 AM