Encoding a Risk Matrix in Project Server
By blurg64
Out of the box Project Server provides the ability to store a project’s risks and issues within an associated project workspace. As these risks and issues are stored in SharePoint lists, there is a tremendous amount of flexibility to customize these lists. As a consultant, a common request I hear again and again is the need to extend the out of the box risks lists to encode some form of risk matrix, such as the one outlined below:
Implementing such a matrix is relatively simple thanks to SharePoint calculated columns, but can be fiddly to get correct. In this post I will run through the process of setting up the risk list to encode the matrix above and hopefully provide you with some insights on how it works that you can leverage elsewhere.
Setting up the columns
Before setting up the matrix, it is necessary to set up the three columns required to support the matrix:
· Consequence – used to store the risk consequence
· Likelihood – used to store the risk likelihood
· Residual Risk – used to store the calculated risk from the matrix
Each of these columns will be added to our Risk list in the Project Template site.
Remember: If you intend for this customization to be available in all new project sites moving forward, it is essential you make the changes in a template site not linked to a project and then register that template into Project Server.
Consequence & Likelihood
In the Project Template site Risk list, create two new choice columns called Consequence and Likelihood. In each column set of the choice values to be as follows:
| Consequence Choice Value |
| (1) Insignificant |
| (2) Minor |
| (3) Moderate |
| (4) Major |
| (5) Extreme |
** __**
Residual Risk
The last column that needs to be set up is the Residual Risk, unlike the other two columns, this is a calculated column and uses a formula to reference the Consequence and Likelihood columns to determine the Residual Risk. The formula itself consists of only three operators, IF OR & AND and is relatively simple to implement, however as you can see in the matrix above, there are 25 different conditions that need to be encoded, so there is a lot of it 
[sourcecode language=”javascript”]
=IF(OR((Likelihood=”(1) Rare”),AND(Likelihood=”(2) Unlikely”,OR(Consequence=”(1) Insignificant”,Consequence=”(2) Minor”,Consequence=”(3) Moderate”)) ,AND(Likelihood=”(3) Possible”,OR(Consequence=”(1) Insignificant”,Consequence=”(2) Minor”)),AND(Likelihood=”(4) Likely”,Consequence=”(1) Insignificant”),AND(Likelihood=”(5) Almost Certain”,Consequence=”(1) Insignificant”)),“Low”,IF(OR(AND(Likelihood=”(2) Unlikely”,OR(Consequence=”(4) Major”,Consequence=”(5) Extreme”)),AND(Likelihood=”(3) Possible”,OR(Consequence=”(3) Moderate”,Consequence=”(4) Major”,Consequence=”(5) Extreme”)),AND(Likelihood=”(4) Likely”,OR(Consequence=”(2) Minor”,Consequence=”(3) Moderate”)),AND(Likelihood=”(5) Almost Certain”,OR(Consequence=”(2) Minor”,Consequence=”(3) Moderate”))),“Medium”,IF(OR(AND(Likelihood=”(4) Likely”,OR(Consequence=”(4) Major”,Consequence=”(5) Extreme”)),AND(Likelihood=”(5) Almost Certain”,Consequence=”(4) Major”)),“High”,IF(AND(Likelihood=”(5) Almost Certain”,Consequence=”(5) Extreme”),“Extreme”))))
[/sourcecode]
Deciphering the above can be a bit daunting, but in essence the formula is broken down into four IF statements that are nested, each used to determine the residual risk condition (Low, Medium, High or Extreme). Within each IF statement the relevant Likelihood and Consequence values are evaluated. In the case of the ‘Low’ residual risk, there are five different evaluations as outlined below:
| Evaluation |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
If any of the above evaluations are true, then the IF statement will return the text ‘Low’ into the calculated column.
Debugging
With a formula as complex as the above, it’s pretty easy to forget a comma or bracket. Unfortunately SharePoint is a little unforgiving and if you get it wrong and you will see an (un)helpful error message like this:
Unfortunately if you get an error all you can really do is review the formula to try to work out where the problem is.
Once the formula is accepted test each Residual Risk calculation manually by creating a risk and setting the relevant values for the Likelihood and Consequence and saving the risk.
If all goes well you will now have a new column called Residual Risk that is automatically populated depending on the values entered in the Likelihood and Consequence columns as outlined in the Risk Matrix.
Hopefully this has given you an overview of how simple it is to extend the risk list to encode a risk matrix. Of course you are not limited to just risks, the technique outlined above can also be applied to the issues list, or any other list within any SharePoint workspace where you want to encode an automated matrix type calculation.
Update: Gord Schmidt has posted a more elegant number based approach to the matrix here.