ITIL determines the Priority of an incident as a function of its Impact (its direct effect on business processes) and Urgency (the amount of delay that can be accepted before resolution). Most ITIL-based tools (Remedy, ServiceNow, etc.) handle this conversion internally based on customer definitions.
In some cases you may want to implement this calculation directly within a SharePoint list, or, since this is likely a well-understood concept in the organization, apply a similar calculation to another process. For example, a team using a SharePoint list to accept service requests may define the impact and urgency of those requests to determine a calculated
The Basics
While each implementation may differ slightly in the number of options available, a common scenario is presented below. Both Impact and Urgency can be one of four values (“Low” to “Critical”) and these convert to a priority in a non-linear cascade as follows:
The chart is simple enough to navigate, but getting the calculation into SharePoint takes some finagling.
The Fields
We need three fields in total. Firstly, of course, we need a field each for Impact and Urgency. These are simple multi-option drop-down fields with the following values:
- 4 – Low
- 3 – Medium
- 2 – High
- 1 – Critical
The numbers are important, but their placement isn’t as long they’re consistent enough to tease out using SharePoint’s built-in functions. In this example, since they’re the first characters, the LEFT function alone – as in, LEFT(Impact) and LEFT(Urgency) – is enough.
The last field, Priority, is calculated field that returns a “Single line of text”.
The Calculation
Our calculation is broken up into three parts.
Firstly, we convert Impact and Urgency to their numerical equivalents. This is done, as described above, with the LEFT() function.
Secondly, imagine the Priority grid above replaced with numbers starting with “16” in the upper left and counting down, left-to-right, to “1” at the bottom right. Each one of these numbers would correlate to a specific Priority.
We need some math to obtain the correct number based on the Impact and Urgency. It’s simple enough, but a bit convoluted. The full equation is:
((Impact+Urgency)+((Impact-1)*3)-1)
If Impact were, say, 3 and Urgency were 2, this would result in:
- ((3+2)+((3-1)*3)-1)
- (5+(2*3)-1)
- (5+6-1)
- 10
Applying that to the actual grid and we get “3 – Medium”.
Thirdly, we need to convert the resulting positional number to the correct Priority. This can be done with multiple nested IF statements, but SharePoint provides the CHOOSE() function for just this purpose. This function takes a list of options and a number; it returns the option that corresponds to the passed value.
In Conclusion
Putting it all together, our final calculation would be:
=CHOOSE(((LEFT(Urgency)+LEFT([Impact to Restoration]))+((LEFT(Urgency)-1)*3)-1),"1 - Critical","1 - Critical","1 - Critical","2 - High","1 - Critical","2 - High","2 - High","3 - Medium","2 - High","3 - Medium","3 - Medium","4 - Low","3 - Medium","4 - Low","4 - Low","4 - Low")
It’s a bit unwieldy, but most SharePoint calculations are.
While this solution is rather esoteric, I hope somebody finds it useful!
