none
Pull data from another list based on user selection and display KPI using calculated columns.

    Question

  • Hi,

    I want to create a two list:

    List A has a list of issues and its SLAs. For example:

    Issue

    SLA (in min)

    PC down

    30

    Server down

    20

    Internet down

    10

    List B is modified task list. When user selects the issue, the field SLA must be populated automatically (from list A).  Then I need to calculate user effectiveness using a calculated column to determine if the user completed the task in time (within its SLA).

    Finally, I need to display a custom icon (KPI) in a second calculated column based on the “Effectiveness” value:

    • If Effectiveness=100 display the green icon 
    • If Effectiveness >= 80 AND <=99 display the yellow icon 
    • If Effectiveness >= 0 AND <=80 display the red icon 

    I’m open to ideas and suggestions. Maybe one of you have a better logic to do it.

    Maybe I can use the created column and the modified column to determine the minutes and then compare to the SLA.

    Thanks, in advanced.


    • Edited by efebo Friday, March 10, 2017 9:23 PM
    Friday, March 10, 2017 9:05 PM

Answers

  • Hi efebo,

    >> Populate fields automatically.

    We can achieve that using a workflow or a InfoPath from.

    Create a workflow associated to List B in SharePoint Designer and make it run when a new item is created. Update a field based on the SLA lookup field value.


    Another way, customize the new form using InfoPath. Add a secondary data connection to List A, and retrieve SLA value from List A.

    Information about how to automatically fields based on the value in other value.

    http://www.bizsupportonline.net/infopath2003/autofill-fields-infopath-dropdown-selection-secondary-data-source.htm

    >> Calculate Effectiveness.

    We can use calculated field to calculate time to completion. If the Task Status is equal to “Completed”, Time to completion = Modified – Created. Then, calculate effectiveness based on SLA and time to completion.

    >> Display custom icons.

    To color the field, we can use calculated fields.

    Create  a “Status Indicator” valvulated column to check the Effectiveness value:

    =IF(AND(Effectiveness>=0, Effectiveness <=80),"Red",IF(AND(Effectiveness >=80, Effectiveness <=99),"Yellow","Green"))


    Create a “Status” calculated column. Output the data with “Number” or “Currency” type for this calculated column, it will render the field into HTML.

    ="<DIV style='font-weight:bold; font-size:50px;margin- color:"&[Status Indicator]&";'>•</DIV>"


    Best Regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Proposed as answer by Victoria XiaModerator Monday, March 20, 2017 5:18 AM
    • Marked as answer by efebo Thursday, March 23, 2017 8:34 PM
    Monday, March 13, 2017 7:58 AM

All replies

  • Hi efebo,

    >> Populate fields automatically.

    We can achieve that using a workflow or a InfoPath from.

    Create a workflow associated to List B in SharePoint Designer and make it run when a new item is created. Update a field based on the SLA lookup field value.


    Another way, customize the new form using InfoPath. Add a secondary data connection to List A, and retrieve SLA value from List A.

    Information about how to automatically fields based on the value in other value.

    http://www.bizsupportonline.net/infopath2003/autofill-fields-infopath-dropdown-selection-secondary-data-source.htm

    >> Calculate Effectiveness.

    We can use calculated field to calculate time to completion. If the Task Status is equal to “Completed”, Time to completion = Modified – Created. Then, calculate effectiveness based on SLA and time to completion.

    >> Display custom icons.

    To color the field, we can use calculated fields.

    Create  a “Status Indicator” valvulated column to check the Effectiveness value:

    =IF(AND(Effectiveness>=0, Effectiveness <=80),"Red",IF(AND(Effectiveness >=80, Effectiveness <=99),"Yellow","Green"))


    Create a “Status” calculated column. Output the data with “Number” or “Currency” type for this calculated column, it will render the field into HTML.

    ="<DIV style='font-weight:bold; font-size:50px;margin- color:"&[Status Indicator]&";'>•</DIV>"


    Best Regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    • Proposed as answer by Victoria XiaModerator Monday, March 20, 2017 5:18 AM
    • Marked as answer by efebo Thursday, March 23, 2017 8:34 PM
    Monday, March 13, 2017 7:58 AM
  • Hi efebo,

    >> Populate fields automatically.

    We can achieve that using a workflow or a InfoPath from.

    Create a workflow associated to List B in SharePoint Designer and make it run when a new item is created. Update a field based on the SLA lookup field value.


    Another way, customize the new form using InfoPath. Add a secondary data connection to List A, and retrieve SLA value from List A.

    Information about how to automatically fields based on the value in other value.

    http://www.bizsupportonline.net/infopath2003/autofill-fields-infopath-dropdown-selection-secondary-data-source.htm

    >> Calculate Effectiveness.

    We can use calculated field to calculate time to completion. If the Task Status is equal to “Completed”, Time to completion = Modified – Created. Then, calculate effectiveness based on SLA and time to completion.

    >> Display custom icons.

    To color the field, we can use calculated fields.

    Create  a “Status Indicator” valvulated column to check the Effectiveness value:

    =IF(AND(Effectiveness>=0, Effectiveness <=80),"Red",IF(AND(Effectiveness >=80, Effectiveness <=99),"Yellow","Green"))


    Create a “Status” calculated column. Output the data with “Number” or “Currency” type for this calculated column, it will render the field into HTML.

    ="<DIV style='font-weight:bold; font-size:50px;margin- color:"&[Status Indicator]&";'>•</DIV>"


    Best Regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Thank you very much! I will try and let you know
    Monday, March 13, 2017 12:35 PM
  • Hi efebo,

    How are things going on your requirement?

    If you think my reply is helpful, you can mark it as answer.

    Best regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, March 16, 2017 1:26 AM
  • Hi efebo,

    How are things going on your requirement?

    If you think my reply is helpful, you can mark it as answer.

    Best regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Done. Thank you very much!
    Thursday, March 23, 2017 8:34 PM