Posts Tagged ‘Project Server 2007’

Calculated Column Formula: Probability and impact analysis for risks #SharePoint #SP2010 #ProjectServer #PS2010 #EPM #MSProject #in

March 9, 2011 2 comments

Many project managers like to do risk analysis in terms of probability and impact, ultimately creating a heat map when data is rolled up at the project or programme level.

Further information about the methodology can be found here:

To aid in the creation of these roll up dashboards, we need to evaluate each risk as it is entered (and updated) and assess the probability of the risk occurring against the impact it has against the project (normally in terms of Cost, Resource or Time).

The following chart details this analysis:


NB: Numeric values have been assigned for the formulas listed below

To implement this in SharePoint (either standalone or as an extension to the standard Risks list in Project Server) we need to translate what the Project Manager / Team Members would like to state in words regarding probability & impact into a numeric value for sorting and analysis.

The following Choice columns are added to the Risk form.

User Interface Columns:

Column Type Values
Probability of Risk Choice Very Low, Low, Medium, High, Very High
Impact of Risk Choice Very Low, Low, Medium, High, Very High

Once we have the UI version of Probability and Impact, we need to use some hidden / calculated columns to convert these values in to numbers.

Once we have these values we then multiply them together:

Probability x Impact = Expected Outcome Value

Hidden Columns:

Column Type Values
Risk Probability Value Calculated Column =IF([Probability of Risk]=”Very Low”,0.1,IF([Probability of Risk]=”Low”,0.3,IF([Probability of Risk]=”Medium”,0.5,IF([Probability of Risk]=”High”,0.7,IF([Probability of Risk]=”Very High”,0.9,0)))))
Risk Impact Value Calculated Column =IF([Impact of Risk]=”Very Low”,0.05,IF([Impact of Risk]=”Low”,0.1,IF([Impact of Risk]=”Medium”,0.2,IF([Impact of Risk]=”High”,0.4,IF([Impact of Risk]=”Very High”,0.8,0)))))
Expected Value Result Calculated Column =[Risk Probability Value]*[Risk Impact Value]

Now that we have our Expected Outcome as a number, we can convert this back to a value the end user will understand, report and dashboard from.

RAG Outcome:

Column Type Values
RAG Calculated Column =IF([Expected Value Result]<0.05,”Green”,IF([Expected Value Result]>0.14,”Red”,”Amber”))

For each risk in the list we will now have a RAG value.

Further customisations I end up doing in clients include adding a RAG indicator column showing a graphical representation of the risk using jQuery.

RAG Indicator:

Column Type Values
RAG Indicator Calculated Column =”<div class=’convertToHTML’><img src=’/pwa/customisation/images/RAG” & [RAG] & “Sml.gif’ alt=”‘ & [RAG] & ‘” title=”‘ & [RAG] & ‘”></div>”

The above formula expects three images:

  • RAGGreenSml.gif
  • RAGAmberSml.gif
  • RAGRedSml.gif

Images in the formula are stored in a document library called:

  • Customisation

with a folder inside called:

  • images

Run the following jQuery function on the page with the list view on to convert the RAG Indicator calculated column string into rendered html:

1 function listViewCalcColChangeToHTML() { 2 $('table[class=ms-listviewtable]').find('td:contains("convertToHTML")').each(function() { 3 $(this).html($(this).text()); 4 }); 5 }

Useful JavaScript Function: PreSaveAction #SP2010 #SharePoint #PS2010 #ProjectServer #in

January 25, 2011 1 comment

Just a quick blog post to talk about a standard JavaScript function that exists in SharePoint 2007 and 2010 – PreSaveAction().

Now I have done extra validation and functions on list forms in allsorts of ways before (some of which is blogged on this site) and it is only recently that this standard function has been brought to my attention. (Thanks @GlynClough via twitter).

Once I learned about this function I of course google’d about it to see what I could find and came across the following blog articles:

These are excellent articles that explain the function, however in the way I tend to work in my projects it posed a problem.

We are essentially overriding a standard SharePoint JavaScript function and the articles assume that you can place a Content Editor Web Part onto the page to add the functionality.

Now in SharePoint 2010 this is less of a problem, since there are very good and official ways to add web parts to list form pages, even in a Publishing Site.

However, in Microsoft Office SharePoint Server 2007 utilising publishing sites, editing newform.aspx and editform.aspx pages caused many issues.

This forced the consultant / developer to create their own new / edit forms and pages in order to insert sometimes a very small amount of code.

Since most of my projects seem to involve the Publishing feature in one way or another, I typically place JavaScript reference files in the Master Page.  This means that my JavaScript code is running on every page.

So, getting to the point of this article, I have modified the general example that is supplied on other sites to take into account this method of JavaScript development by simply querying what page I am on before I run my validation tests and functions.

Code Example:

1 <script type='text/javascript'> 2 3 //global variable to be used in multiple functions 4 var currentPathname = location.pathname; 5 //set to lowercase to make comparisons in the code easier 6 currentPathname = currentPathname.toLowerCase(); 7 8 function PreSaveAction() {; 9 10 if (currentPathname.indexOf('Enter Pathname here IE. /lists/tasks/editform.aspx') != -1) { 11 //Setup your validation checks here 12 if (1 == 1) { 13 alert('Validation passed, let SharePoint continue'); 14 return true; 15 } 16 else { 17 alert('Validation failed, stop SharePoint from saving the item'); 18 return false; 19 } 20 } 21 22 //If we have got to this point, then no pages need pre-validation. Let SharePoint continue. 23 return true; // OK to proceed with the save item 24 } 25 26 </script>

%d bloggers like this: