SharePoint List Risk Matrix using XSLT

Representing risks in a matrix or heat map type view is pretty common so lets take a look at how we can do that in SharePoint.

First of all we need to capture our risks in a list

  1. Create a new custom list and rename the default Title column to Risk to capture a label for our risk
  2. Create a new Choice field called Consequence and use the following options:
    1. Minor
    2. Moderate
    3. Severe
    4. Major
    5. Catastrophic
  3. Create a new Choice field called Likelihood and use the following options:
    1. Improbable
    2. Rare
    3. Unlikely
    4. Possible
    5. Likely
    6. Almost Certain
  4. Create a new Calculated column called ConsequenceScore and use this formula:         =IF(Consequence="Minor",1,IF(Consequence="Moderate",3,IF (Consequence="Severe",10,IF(Consequence="Major",30,100))))
  5. Create a new Calculated column called LikelihoodScore and use the following formula: =IF(Likelihood="Improbable",0.001,IF(Likelihood="Rare",0.01,IF(Likelihood="Unlikely",0.03,IF(Likelihood="Possible",0.1,IF(Likelihood="Likely",0.3,1)))))
  6. Both of the above should return a number with no decimal places
  7. Create a new Calculated column called RiskScore with the following formula:  =ConsequenceScore*LikelihoodScore

When you now add a new item to your list you should get a form like this to capture the name of the risk and choose the Consequence (sometimes called impact) and Likelihood (sometimes called probability).

New Risk Form

New Risk Form

Good start but all we can see at the moment is the usual list of letters and numbers.  To make it look pretty we need some custom XSLT.  If you are not familiar with XSLT then take a quick read through my previous little mini series of blogs.

Today I have made it easy and added the files on GitHub for you to download:

RiskMatrix.xsl

RiskMatrix.css

I have now tried adding these files to OneDrive so hopefully that will make it easier for everyone to get them.

The XSL file includes a link to the CSS file.  I have assumed you will put both files in /Style Library/custom.  If this is different then you will need to change the path to the CSS file in the XSL file – it is near the top and commented so will be easy to find.

Now the easy bit.  Add your list as a web part to any page and edit the web part.  Under the Miscellaneous area type in the path to your XSL file (/Style Library/custom/RiskMatrix.xsl) and click OK.

XSL Link

XSL Link

You should now have a nice Risk Matrix – congratulations!

Risk Matrix

Risk Matrix

I will be presenting this and more at the Australian SharePoint Conference on Wednesday 21 March 2012 in Melbourne so do come along and say hello.

This entry was posted in SharePoint and tagged , , , , , , , . Bookmark the permalink.

153 Responses to SharePoint List Risk Matrix using XSLT

  1. Ramesh says:

    Hi.. this is quite useful. how do i go about placing the links against each of the risk counts.. so that when user clicks on the numbers, it links up to a view to show the list of risks for included in the cell?

    • paylord says:

      Hello Ramesh,

      The simplest way to tackle this would probably to tackle it in a slightly different way. Add in some jQuery, populate the results in an array and then present the filtered list with a CLICK function on the numbers.

      Dave

  2. Luis says:

    This was very useful … thanks a lot!!!

  3. Tilaga says:

    Really awesome.. Thank you so much for your valuable posting.

  4. Theo says:

    Can this also work with a view of list in SP 2010?

  5. Fawzy says:

    Hello paylord,
    The PDF links are broken. so is it possible to republish?

    Fawzy

  6. zukoo says:

    Hi,
    I can’t get the xsl either.
    Cool post anyway🙂

  7. Alex Dean says:

    worked a treat! just be wary when copying the code from the pdf files as some readers will show the translated special characters and not the escaped chars.
    need to be escaped with > and <

    • paylord says:

      Hey Alex,

      Glad this worked for you. The copy/paste issue was happening from simple page text which is why I tried the PDF option. Sadly, WordPress does not seem to allow me to share a text file.

      Dave

  8. Andrew says:

    Hi Paylord, just wondering if you can email me the xls file directly as I am having trouble with the formatting of the file?

    Thanks in advance

  9. Anu says:

    Hi
    Can you please mail me the xsl & css files? I am having trouble with formatting of PDF…

    • paylord says:

      Hello Anu,

      Sorry you are having trouble. I have emailed the files through as requested – let me know how you get on.

      Dave

      • Anu says:

        Hi Dave
        Thanks for sending the files – I could not get this to work, I am getting “Correlation ID” error for web part. I have checked everything, but unable to find the problem.
        Thanks,
        Anu

      • paylord says:

        Hmm – frustrating. I know these files have worked for me and others as you can see below. In the past I have seen correlation errors when there is something wrong with the output HTML – perhaps a TD not closed properly or similar. These are also often caused by incorrect quotes – for some reason quotes sometimes paste incorrectly. Having checked the above, the only option is to try commenting out sections of the XSLT until you can identify where the issue is.

      • Anu says:

        Hi Dave
        After fixing the entire quotes, it worked. Also, looks like the output did not like the “<" character. Had to replace with a string "Less than". Thanks a lot for the suggestions and sharing this great template.

        Regards,
        Anu

      • paylord says:

        That is great news – glad you got it sorted.

        Dave

  10. Ankit Gupta says:

    Hi Dave,
    I am getting all the count as zero ie all the cells have zero value. Can you please suggest. Thanks

    • Ankit Gupta says:

      i was able to figure this out…i just needed replace this
      select=”count(/QueryResponse/Rows/R….
      with
      select=”count(/dsQueryResponse/Rows/R…
      and it worked…hurray!!!

      • paylord says:

        Great to hear you got it sorted🙂

      • John G says:

        I’m bumping into a similar issues with this where I’m only getting 0 back in all of the cells. It’s almost as if nothing is being passed to the XSL file from the SharePoint list, but I know that’s not true because when I rem out any of the xsl:variable cell statements, the entire heat matrix errors out. I’ve checked Column names until I’m blue in the face, removed all spaces in the options, etc., but no joy.

        I’m going back through to look for bad quote marks and greater/less than signs, but the table displays now, so I suspect that will not be the culprit. Has anybody else run up against the “all 0 syndrome”?

        Thanks,
        John

      • John G says:

        Evermore curious – the correct tallies show up when I view the table in SPD, but not in my browser…

      • paylord says:

        Hello John,

        SPD tends to be a little more forgiving to small HTML errors than a browser so that may be the cause. I was going to suggest you make sure that the columns are all included in the view you are using but if you are seeing correct results in SPD then I am less convinced this is the issue – but still worth checking. You seem to have covered most of the usual culprits such as column names etc, remember these are also case sensitive.

        I will be away for a few days but happy to help further if you post more information when I return.

        Dave

      • John G says:

        Hello again Dave – I figured out my problem. I had some sloppy entries in the XSL reference section that I had to clean up; once I re-entered them the results started showing properly.

        Love your blog, BTW. You’ve put a lot of work into this – thanks so much.

        Happy New Year

      • paylord says:

        That is great news John – well done figuring it out.

        Dave

  11. Erwin Nooijens says:

    Hey Dave,
    Can you send me the two files through email as well?
    Kind regards,
    Erwin

  12. Corri Apodaca says:

    Hi Dave – great post and just what i need – would you mind sending me the files as well?

    Thank you in advance!
    Corri

  13. Shane says:

    Hi Dave, thanks for the great post! just what I was looking for! The above links didn’t work for me, could you please email me the files if that’s OK? thanks again!!

  14. Hi Dave,

    Another one looking for the files by email if that’s ok?

    Thanks in advance.

    John

  15. M de Jong says:

    Dear Dave,
    I am also very interested in the files. Can you please send them to me also.
    Btw the formula of ConsequenceScore is missing a bracket at the if statement of severe.
    In wordpress you can use the tag “code” to display the code properly.
    Maybe a more convenient way for you is to host the attachements as a zip on onedrive.live.com or a similar fileshare site.
    Thanks for you

    • paylord says:

      Hello and thanks for your comments and suggestions. I did try the code approach early on but had a few issues though more recent posts seems to have worked fine. I will try to get around to revisit these earlier posts and also investigate sharing the files from OneDrive.

      I have emailed the files in the meantime so hope they do what you need.

  16. Cherry says:

    Hi Dave,

    I want create a SharePoint list view where Sum less than 10 by Employee.

    Can you please see my question here http://social.technet.microsoft.com/Forums/sharepoint/en-US/f93bcf5a-9164-44dc-8dd7-6784743280cd/create-sharepoint-list-view-where-sum-less-than-10?forum=sharepointgeneralprevious

    Please help me out ASAP. It’s a bit urgent.

    • paylord says:

      Hi Cherry,

      Sorry but I agree with the comment you already have from Andy. I cannot think of a simple way to do this out of the box so you would need to use something like jQuery to get the data from the list and then manage what you present on the page.

      You cannot simply add a filter to your view based on that Sum value.

      Good luck.

      Dave

  17. M de Jong says:

    My client was really happy with the current result, but being a typical client they immediatly came with the idea of taking it to the next level. Would it be possible to display instead of the count of items the actual row id’s? (even if they would be 10 or 15 in each cell). Preferrably with hyperlink to the actual list item.
    Kind regards, Manfred

    • paylord says:

      Absolutely – I have done several different permutations of this matrix concept with different clients. Another alternative to what you suggest is to make the cell a filter – so a user can click on the cell they are interested in and it dynamically filters a view of risks to just show those that are relevant. I use jQuery to achieve this sort of thing but it is a bit beyond a quick response here. I may do a follow up blog post at some point though.

      Good luck with it.

      Dave

      • Runnerk2 says:

        Good morning Dave,

        If you ever post this jQuery please let me know, my client has asked me to use the 5×5 cubes with the scale of 1-5 and want to see each relevant risk with its scale of where the risk fall into this cube.
        How did you measure your thresholds?

        Thanks
        R

      • paylord says:

        If I do blog on this it will be right here.

        What do you mean by thresholds? Multiplying the probability and impact gives the risk score which in turn decides the colouring on the matrix – is that what you mean?

      • Ahalya Rajakarier says:

        Hi Dave,
        Thank you so much for this post. It will definitely help me out with a project I am currently working on.
        I also have a requirement to display the IDs instead of the totals and I couldn’t find the solution using JQuery. Please can you post it as a blog / let me know how to achieve it?
        Many thanks, Ahalya

      • paylord says:

        Hello Ahalya,

        If you are using SP2013 then I would probably recommend you do this in JSLink instead as that would be easier.

        I have done something similar for a client which included the ability to click on a cell in the matrix and filter the view of risks to only show the relevant results. However, this was definitely a combination of XSL and jQuery and was also relatively complex so not something I can give you a quick answer for here. What I would say is that, depending on the number of risks you are working with, showing IDs could be rather cluttered and probably not very meaningful to most users. Using a separate list view of the risks which is then filtered when you click on the appropriate cell of the matrix is likely to be more useful and present better.

        I do have plans to do another risk management post to achieve the matrix using either JSLink or Display Templates but not sure it will be soon.

        Good luck with your project and let me know how you get on.

        Dave

      • Ahalya Rajakarier says:

        Hi Dave,
        Many thanks for your quick response. I will try as you have suggested and let you know the outcome.
        Regards, Ahalya

  18. Runnerk2 says:

    Hi Dave,
    Fist of all this is an excellent post, I have been looking for this for a long time. I have been tasked
    to build a risk matrix so this will help me tremendously.
    Could you please send me the complete formula in step 5 (it got cut off at the end on my screen).
    Also, since I am newbie in SharePoint… does ” /Style Library/custom” folder require admin rights
    to Sharepoint to put your files? (I may not have access to it and need to know if i need to contact my admin).
    Thanks again
    R

    • Runnerk2 says:

      HI Dave,
      I finally figured it out… Just had to think about it…
      Thank you very much.
      R

      • paylord says:

        Hello,

        I am assuming that this means you are all sorted – great news.

        If you do need anything further then do let me know.

        Glad it was useful🙂

        Dave

      • runnerk2@gmail.com says:

        Good Morning Dave,
        I have finally created this site according to my customer’s needs…the (5×5) risk matrix.
        Which they absolutely like…thanks to you.
        This matrix web part is in a sub site and both (css and xsl docs) are in “style Library” on the root site.
        everything works well when I bring it up, but when my users are trying to look at it (they have contributor’s access) they can not see anything…
        The message they get is:
        Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.”
        Any idea why it is giving me this error?
        I looked at your blogs and could not see anyone else has the same issue…I even moved the two files in my sub site and re-directed the tool to read from there but still the users get the same error…
        Thanks very much in advance…
        R

      • paylord says:

        Hello, Sorry for the slow response but have been away for a week or so. My guess here is that you have not checked in the XSLT files or the users do not have access to them.

        Hope you get it sorted.

        Dave

      • John G says:

        RunnerK2 – I’m experiencing the same issue with users that do not have ‘full access’. My admins can display the matrix appropriately, but those with ‘contribute’ permissions (or below) cannot. If you’re still monitoring, I was wondering if you found a solution/workaround.

        Thanks & HNY,
        John

      • paylord says:

        Hey John,

        You do need to make sure that the users have access to the XSL files, so if you have these located in the style library then make sure everyone has at least read-only access. Also, remember to check them in.

        Hope this helps.

        Dave

      • John G says:

        Dave – I thought about that as I hit the ‘submit’ button on my last post (because I saw that you mentioned it previously). You’re absolutely correct – the reason that only the admins could get it to work was because they were the only ones with unfettered access to the .css and .xsl files – I moved my files up into the parent directory which has less restrictive permissions and voila! It’s working for everybody. Thanks again.

        John

      • paylord says:

        Great news – thanks for letting me know🙂

        Dave

  19. Ryan says:

    PayLord, this is a great solution! I am implementing this at work. I have it working except for on of my columns that contain spaces for the column called Impact. For example, 1-Very Low, 2-Low. I am able to get results when there is not a space in the value only. I can get results for 2-Low, but not 1-Very Low. Any input is greatly appreciated.

    Does not work:

    Works:

    • Ryan says:

      I just noticed it took out the code..stripping off the ends to see if it comes through..

      xsl:variable name=”RM1A” select=”count($Rows[@Impact = ‘2-Very Low’][@Probability = ’20’]

      • paylord says:

        Hello Ryan,

        It should work fine but you will need to have the text exactly the same. Make sure you are using the same hyphen and spaces are all in the same places. Capitalisation is also important. Maybe try removing the spaces and see if that works – so 2-VeryLow. I also notice in your 2 comments you have 1-Very Low and 2-Very Low so make sure you are consistent in your list and XSLT.

        Let me know how you get on.

        Dave

  20. Parker Golden says:

    I am getting an error when I set up the Scores, the error message says ”
    Sorry, something went wrong

    The formula contains a syntax error or is not supported.

    Technical Details

    Learn more about the syntax for formulas.

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: 420aa79c-e0e4-1000-7409-b5c3079785be

    Date and Time: 7/22/2014 7:26:25 AM

    Please help.

    • paylord says:

      Hello Parker,

      Quotation marks are the most likely issue as they do not seem to copy very well. Replace them in your code with standard quotation marks and hopefully that will resolve the problem.

      Let me know how you get on.

      Dave

  21. Anders Munck says:

    Great post! I’m using it for creating a matrix of how much training is available and everything works great except it doesn’t count. When I filter I can only get ALL the rows or ZERO rows:

    This gives all the rows:

    And any of these variations should have valid results, but give zero rows:

    The only difference I can think of between my use-case and your example is that most of my columns are Lookup columns, but I tested on a regular text-column, and still no dice.

    Do you have any idea what I’m doing wrong?

  22. Peter De Maeyer says:

    Hello,
    Please can you send the two files too.
    Many thanks

    Peter

    • paylord says:

      Hello Peter,

      I have emailed through the files as requested but both should be available from the links which now go to GitHub which resolves all the previous issues.

      Good luck and let me know how you get on.

      Thanks

      Dave

  23. Stoney says:

    Hi,
    I´m trying to get this to work but I get an error when I use the formula in one of the clc fields
    =IF(Consequence=”Minor”,1,IF(Consequence=”Moderate”,3,IF (Consequence=”Severe”,10,IF(Consequence=”Major”,30,100))))

    The formula contains a syntax error or is not supported.
    I have tried to use ; instead of the , with no luck (needed when region is set to Icelandic)
    Best regards,

    • paylord says:

      Hello Stoney,

      Can you confirm you have definitely created a column with the internal name Consequence?

      If so, there are only a couple of things that I can see immediately that may be worth trying:

      Remove the space between the IF and the ( on the third clause. I really do not think this makes any difference but worth a try.

      Wrap the column names in square brackets – [Consequence].

      Check the quotes – when copying and pasting quotes they often do not work well – not sure if you can see the difference between ” and “?

      Good luck and hope you get it working.

      Dave

      • Peter says:

        Hi ,

        I had the same problem. Solution:

        =IF(Consequence=”Minor”,1,IF(Consequence=”Moderate”,3,IF (Consequence=”Severe”,10,IF(Consequence=”Major”,30,IF(Consequence=”Catastrophic”,100)))))

      • paylord says:

        Thanks for that suggestion Peter, though the ELSE option has certainly worked for me.

        Did you manage to get your counts working?

        Dave

  24. Dee says:

    I used your solution to create a Risk Matrix and it worked great! Now I am trying to adapt it to a SharePoint Task List instead of a Custom List and the matrix displays, but does not count the items. As a test, I took the Calculated fields used in the OOTB Task List and put them in a Custom List and now the count works. Is there a way to get the count working on the OOTB SP Task List so that I don’t lose its inherent functionality?

    Thanks!

    • paylord says:

      Hello Dee,

      Sorry for the slow reply but I have been away for a few weeks.

      There is no reason this should not work with you adding the required columns to a standard task list. Make sure the internal names of the columns match the XSLT exactly (it is also case sensitive). Also ensure that the columns are included in the view and you should be good to go.

      Dave

  25. Pingback: Managing the Risk Management Process on SharePoint

  26. Gary G says:

    Please email me a copy🙂

  27. Peter Doyle says:

    Hi Dave,

    Great post.
    I deployed the code and the table renders but it’s missing all the colors.
    Is there updated code for 2013?

    Thanks,
    – Peter

    • paylord says:

      Hello Peter,

      Check the path to your CSS file is correct. The link is towards the end of the XSLT.

      The same code will work fine in 2013 – though I have been thinking about producing a JSLink version.

      Dave

  28. Makhlouf says:

    Great post. I used your solution and it worked fine. Now I want to use this matrix for a SharePoint External List instead of a Internal List.
    How can I do this?
    Advanced Thanks

    Makhlouf

    • paylord says:

      Hello Makhlouf

      I have not tried this with an External List but I am pretty sure the web part you use to present an external list can be customised with XSLT too. There may be a XSL Link field in the settings or possibly an XSL Editor where you could paste in the XSL.

      Let me know how this goes.

      Dave

  29. Reza says:

    Hello Dave,
    I have been using this awesome risk matrix for a while now.
    Thanks very much for allowing us to use this…
    When displaying the 5×5 matrix, in each cube, by default it displays “0”. Is there a way not to show “0” and only show blank unless numbers are selected?
    Thanks again.
    Reza

    • paylord says:

      Hello Reza,

      Glad to hear you are making good use of it.

      You can certainly test for a non-zero value before you render the value. Something like:

      <xsl:if test="$RM1A != 0">
       <xsl:value-of select="$RM1A" />
      </xsl:if>
      

      Though you will need to replace each value-of with the above.

      A better way would probably be to create a separate template to do this and call it with a parameter which is the value. It would achieve the same thing but would be more maintainable if you wanted to make changes in future.

      Hope this helps.

      Dave

      • Reza says:

        I’ll give it a try and let you know.
        Appreciate the quick response.
        Cheers!
        Reza

      • Reza says:

        Hi Dave,
        I finally got around to test it with non-zero value and it worked like a charm…
        My users are happy, my customer is happy … life is good.
        Thanks very much for your help.
        Cheers!
        Reza

      • paylord says:

        Great news – thanks for letting me know🙂

  30. tonton polak says:

    5 you
    Made with great rendering

  31. Joshep says:

    Hello Dave

    I followed step by step of your Matrix Risk tutorial. I created the list with all colummns, it works.

    But when i try to make the matrix , doesn´t work.

    1. Put the files in /Style Library/Custom/…
    2. Insert into webpage my list like Web Part. The list appears in the webpage.
    3. Put the path of .xsl file into Miscellaneous XSL Option
    4. Apply , and i don´t see the Matrix.

    Could you help me please?

    My sharepoint version is 2010.

    Thanks

    • paylord says:

      Hello Joshep

      First thing to check is the path to your XSL file. If you are in the root of a site collection then the relative path will be /Style Library/custom/etc… but if you are in a lower level site collection then the path will be something like /sites/SiteCollectionName/Style Library/custom/etc…

      When you apply the XSL are you getting any sort of error on the page? If so then this would probably indicate a syntax error in the XSL file. One of the common culprits is the quotes so do check to ensure they are standard quotes and not the ‘pretty’ ones.

      Let me know if this helps or try to give me a bit more information on what is going wrong.

      Good luck!

      Dave

  32. jonpclayton says:

    Hi Paylord, i really like this project. I have implemented it and have everything built and rendering fine with one exception. I don’t get any number in the matrix, all zeros. Any thoughts?

    • paylord says:

      Hey Jon

      Thanks for the comment. The numbers in the matrix come from the variables. These are dependent on the column names being exactly the same and the choice values being exactly the same.

      If you have changed anything then you will need to alter the variable formulae to match.

      That is the most likely issue as everything else is rendering OK.

      Let me know if you get it sorted or need more help?

      Good luck.

      Dave

  33. Rudy says:

    Hello Dave,

    Thanks a lot for such an awesome trick. I have tried head to toe but at last got same error as Ane had i.e. “Correlation ID”. As suggested I had replaced all the inverted commas also but still not working form me.
    can anyone share me the working file @rudreshtewari@yahoo.com
    Happy New year to all in Advance !
    Stay Hungry for Knowledge in 2016 also

    • paylord says:

      Hello Rudy

      A correlation error usually means there is some sort of syntax error in the XSLT. This could certainly be the inverted commas but if you have already replaced those then sounds like it is another issue. I have emailed through the XSL and CSS files so hopefully they will get you going. Let me know?

      Thanks

      Dave

  34. So I am trying to use multiple values instead of one such as

    1-3 = Low impact
    4-6 = Moderate
    7-9 = High
    10 = Critical

    1-2 = Unlikely
    3-5 = Possible
    6-8 = Likely
    9-10 = Certain

    The user is entering the number instead of the name because of calculations in another column. How would I code this under the variable and stick to a 4×4 chart?

    • paylord says:

      Hello mtbates

      This is certainly possible. You will need to change the calculation on the ConsequenceScore and LikelihoodScore columns to reflect your values. Something along the lines of:

      =IF(Consequence<4,1,IF(Consequence<7,2,IF(Consequence<10,3,4))))

      You then need to adjust the calculation in your XSL to something like:

      xsl:variable name="RM1A" select="count($Rows[@Consequence = 1][@Likelihood = 1])"

      Remove the unwanted variables and row/columns in your table and should be good to go.

      Good luck.

      Dave

      • Got it to work but now that I moved it from my test environment to the area I need it at I am getting all 0’s. Any idea what could be causing it? Does the dsQueryResponse need to be redirected for any reason? I got the table and the css up correctly just not the data.

      • paylord says:

        @mtbates

        Great so far. The fact that you get the grid on the page suggests there are no syntax errors so that sounds like the data is not getting through. Can you confirm you have included all the columns on the view that you are applying the XSLT to and that there are no filters? Also that the internal column names are identical to those used in the XSLT. If you have any spaces in the internal names then this gets a bit messy as spaces will be escaped – so you need to have them in XSLT something like Column_x0020_One. XSLT is also case sensitive.

        Hope this helps get you up and running.

        Dave

  35. I really appreciate your help on this.
    Ok so Checked to make sure the columns are not filtered and are visible. The names are identical with capitalization taken in consideration.

    • paylord says:

      @mtbates

      Sorry to stress this but definitely the internal column names as opposes to the display names? Pretty sure you have this correct given you had it working in a different environment but it is probably the main reason for data not coming through.

      Assuming the above is OK then next step is to confirm the calculated columns are all OK. If you remove the XSLT from the view then you should just see your columns of data – including your calculated columns.

      If they are all correct then next bit is the XSLT formulae. Can you post one of your XSLT calculations so I can check it? Simplest thing is probably to create a simplified XSLT Stylesheet and just have one or two of your variables and render them in DIVs. This will hopefully establish that the data is all as it should be.

      The only other thing is to then check how they are being rendered in the grid. Let me know if the above does not work and I will get you to email me a copy of your XSLT.

      Fingers crossed!

      Dave

  36. Dave,

    I think I learned something new today. I read over your reply many times thinking I was missing something than I thought wait let me look into this internal column name. Completely different from what I thought it meant. So followed some instructions on how to find it and it worked flawlessly. For anyone that might be wondering if you go to the list settings and click on the column in the url near the end is Field=blahblahblah. The blahblahblah is the internal name. As soon as I changed my variables to the internal names the info popped in instantly.
    Guess you learns something new every day.

    Thanks so much Dave!

    -Matt

    • paylord says:

      Hey Matt

      That is great news. Perhaps I should have been clearer in what I meant but thank you so much for taking the time to explain for any others who may be unsure.

      Well done on getting it all working!

      Dave

  37. Stuart Anderson says:

    Great idea but cant get this working in SharePoint 2013

    =IF([Consequence]=”Minor”,1,IF([Consequence]=”Moderate”,3,IF ([Consequence]=”Severe”,10,IF([Consequence]=”Major”,30,IF([Consequence]=”Catastrophic”,100)))))

    or

    =IF(Consequence=”Minor”,1,IF(Consequence=”Moderate”,3,IF (Consequence=”Severe”,10,IF(Consequence=”Major”,30,100))))

    If tried replacing the [] and “” but makes no difference i still get syntax error.

    Kind Regards

    • paylord says:

      Hello Stuart

      Sorry to hear you are having issues with this. I am assuming from your post that the problem you are experiencing is with the formula for the calculated column.

      The square brackets can be used to surround a column name with spaces but where the column is a single word as above then they are not vital. The quotes need to be standard quotes and if you paste them in from the browser then they are often an alternative quotation mark that is not recognised but if you have replaced those already then that is obviously not the issue.

      Are your column names exactly the same?

      Have you tried creating the formula from scratch, double-click on the column names to the right to get them into the formula?

      It also looks like you have a space after the 3rd IF before the bracket – might be worth removing that?

      It definitely works in SP2013 so let me know if you are still having trouble?

      Good luck

      Dave

  38. Yogi says:

    Hi Paylord, This was exactly what I was looking for. In fact, I’m wanting to display symbols (circles, triangles, squares etc) in stead of the digits to represent the risks. But I’ll get started with what you have there.
    I could download the github files. Could you please email them to me?

  39. This is great and I have managed to get the XSL working but I get all 0s. I have set a row counter to make sure I am getting data back. It counts all the rows. Just out of interest why do you have the score columns as from what I can see in the XSL they are not used? Any idea why I could be getting 0s. I am positive my column names are correct as they are the same as I use in my calculated fields.

    • paylord says:

      Hello James

      Glad to hear you have got this working so far – well done!

      The score columns are used to simplify the calculation of the overall risk score. You are correct that they are not really used in the matrix but are often used to sort the risks showing the highest at the top.

      The most common reason for the zeros is either the columns names not matching (note that the XSL uses the internal names), the actual values in the lookups, or the columns missing from the view.

      Hope this helps you resolve it – let me know how you get on.

      Thanks

      Dave

  40. AG says:

    Oddly after putting both files in the same folder as yourself, and creating a simple list with only the mentioned columns, I still cannot get the webpart to display anything other than the list

    • paylord says:

      Hello AG

      From what you have described I am guessing that you have either not added the URL to the XSL or the path is incorrect. Please check that first and let me know how you go?

      Good luck!

      Dave

      • AG says:

        Hi,
        just as a test i followed the same set up as yourself, so placed both files in the same location as yourself, being the style library\custom folder

        i’ve check the webpart settings and in the misc section under xsl link the url is /Style Library/custom/RiskMatrix.xsl

        so i did not alter the settings in the actual XSL as you mentioned i wouldnt need to do this?

      • paylord says:

        Hello AG,

        What is the URL of your site? /Style Library/etc will point to the root level site collection so if yours is at a sub-level then you will need to include that as part of the path. For example /sites/ag/Style Library/etc.

        Dave

  41. AG says:

    ah yes, sorry wasnt paying attention, so that got me to the next stage, and now i get this

    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

    I’ll look at this issue later, but would appreciate your help on the formulas i would use for a slightly different set up.

    We have:
    ImpactScore:
    Minor
    Moderate
    Major

    CurrentLikelihoodScore:
    Unlikely
    Possible
    Likely

    IMPACT
    Minor – Moderate – Major
    LIKELIHOOD
    Likely 3 6 9
    Possible 2 4 6
    Unlikely 1 2 3

    Impact * Likelihood = Risk Rating Number
    High: Above 6
    Medium: Above 2 up to 6
    Low: Up to 2
    How would i set up my formulas for these as very different to your set up please?

    • paylord says:

      Hello AG

      It sounds like there is some form of syntax error in the XSLT if it is throwing that error. A common issue is quotes which often do not copy well so may try replacing those and see if it fixes the issue..

      The scoring is going to be simpler for you. The formula language is pretty much the same as Excel so if you are unsure you can always try in there. Something like:

      =IF(Impact="Minor",1,IF(Impact="Moderate",2,3))

      You will also need to change all the XSLT to match your column names and values.

      Good luck🙂

      Dave

      • AG says:

        Thanks, got formulas to work, but now i need to convert the RiskRatings from numbers to text i.e upto “2” is a low, 2to6 is a medium etc
        =IF(RiskScore<2,Low,IF(RiskScore,High)))
        But cant get it to work, once i do i will give changing the file around to represent my columns and scoring,

  42. AG says:

    Sorry formula did not copy properly:

    =IF(RiskScore<2,Low,IF([RiskScore],High))))

    • paylord says:

      Hello AG

      You will need quotes around the values you are trying to set and you also seem to have too many brackets:

      =IF([RiskScore]<2,"Low",IF([RiskScore]<6,"Medium","High"))

      Hope this helps.

      Dave

  43. AG says:

    Thanks, had to alter it slightly to get it to work properly:
    =IF(RiskScore<=2,"Low",IF(RiskScore<=6,"Medium",IF(RiskScore<=9,"High")))

    I really appreciate all your help, Thanks
    which bits do i need to change on the xslt to get that work please, as ive opend the file in notepad ++ but wondering if i should use something else to make the changes?

    • paylord says:

      Awesome.

      Notepad++ will be fine – if you format as XML it will probably help. It is the formulae near the top that you need to focus on – the column names and value both need to match exactly what you have in the list.

      Good luck!

      Dave

      • AG says:

        Hi Dave,
        Struggling with changing XLST, cant seem to find the formulas, nor could i see the column names, did a find, and managed to replace all consequence with Risk.
        Any futher help will be apprciated

      • paylord says:

        Hey AG

        In RiskMatrix.xsl there are variables defined from line 19-48 which define the number of risks for each cell. You will need to change the formula for each of these to ensure they match the column names you are using and the values you are using. My example represents a matrix of 5×6 – from what you have shared I believe you are using 3×3 so you will not need all of the variables and will also need to change the HTML to leave you with a 3×3 matrix.

        As an example:

        count($Rows[@Impact = 'Minor'][@Likelihood = 'Likely'])

        This assumes the internal names of your columns are Impact and Likelihood.

        Hope this gets you going.

        Dave

  44. AG says:

    HI, sorry been occupied elsewhere, i cant see these variables and when i do a Ctrl+F “count($Rows” the only entries appear around line 510-622. Assume this is not what you were referring to, and i need to change something else?

  45. AG says:

    It seems when i download the RiskMatrix.xsl file and even the other, it seems to be converted to HTML? any ideas, i can see the variables in your original file, but cant see why it gets converted when i download it

  46. AG says:

    OK Ignore both my messages, managed to get it in the correct format, just trying this out, will send a message soon

  47. AG says:

    HI Dave, looks like back to my original issue:
    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

    I’ve replaced all the single and double quotes in the xsl but no joy, error message persists.

    • paylord says:

      Hey AG

      Glad you managed to resolve the other issues.

      If you have already tried the basics mentioned in earlier comments then consider commenting out the majority of the XSL file until you no longer get an error then add in bit at a time. This is the simplest way I have found to troubleshoot XSL in SharePoint.

      Good luck!

      Dave

  48. AG says:

    HI Dave,

    I’ve removed everything i dont require and changed all that needs to be changed, but still getting the same webpart error, are you able to assist?

    Thanks

    • paylord says:

      Hey AG

      Have you tried commenting out almost everything and then just adding in small sections at a time. You can add a simple DIV with Hello World or similar and leave that as the only HTML within the StyleSheet. Once that works then slowly add in sections of the HTML until you identify where the error is. XSL is very sensitive to syntax – so any HTML errors will cause it to error. For example, if there is DIV element opening but not closing – or a TD outside of a TR.

      Hope this helps you find the problem.

      Dave

  49. AG says:

    Thanks, i’ve started looking at the Correlation ID errors and managed to fix a number of minor errors such as: ‘tr’ start tag on line 90 position 5 does not match the end tag of ‘table’.
    My problem is now its giving me an error:
    Error while executing web part: System.Xml.Xsl.XslLoadException: Expected token ‘]’, found ‘Likelihood ‘. …ows[@Impact = ‘Minor’][@Current –>Likelihood <– = 'Likely']) An error occurred
    at (21,3).

    When i comment out that entire block it then of course gives an error that the variable is not set.
    I can't see what is wrong with the line:

    Let me know if you can spot anything please?

  50. Chappers74 says:

    Hi – Thanks for a great post – I too had similar problems to others on here where everything was going swimmingly until I flipped the switch and all it would display was zeroes. I checked every thing, square brackets, spaces, spellings etc all to no avail. In the end I went through the exercise exactly as described here and met with success (no idea how or why but i’m not one to question the Gods of Sharepoint).
    In the end I achieved a work around by taking the exercise list and then adding in the additional fields from my list to effectively make the example list a duplicate of my list – and it still works. I would pass this on as a nugget of advice to others who are just getting started in this field as a potential solution

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s