Posts Tagged ‘MOSS 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 }


Calculated Column Formula: Date Format #SP2010 #SharePoint #in

March 9, 2011 1 comment

Just a reminder for me really… At a client at the moment who wants to put the date in list web parts in the following format: dd-MMM-yyyy.

I found the following article suggesting using the TEXT function in a Calculated Column

Formula Example:

1 =TEXT(Created,"dd-mmm-yyyy")



Useful findings whilst learning Reporting Services with #SP2010 #SharePoint #PS2010 #ProjectServer

So the first phase of the project I have been on for the last year is starting to move into the closing phases.  The information architecture is in place, most of the custom development is complete and mapped to the project lifecycle / business processes of my client.

The project takes data from multiple places including:

  • SAP (Resource, cost rate, metadata and product contract information)
  • Project Server 2010 (Timesheets, Plans, Project / Task / Assignment data)
  • SharePoint 2010 (Risks, Issues, Actions, Documents etc.)
  • Custom database (Snapshot data)

I’ve learnt a lot about SharePoint 2010 and Project Server 2010 since I started this project (pre public beta).  So now that we have all this data, the time has come to bring it all together with Business Intelligence dashboards and UI design.

Once of the main tools we are using for this is Reporting Services in SharePoint Integration mode and although I have lead projects involving SSRS in the past, actually getting my hands dirty and creating reports myself is something of a novelty.

However, time and resource pressures have meant that I have had to setup the SSRS infrastructure, development environments and test reports to ensure that the data can be utilised in the required way.  As part of this setup I am also training my client on the business intelligence studio interface and deployment of reports in to SharePoint environment.

So getting to the point of this post, below is a list of useful links, findings and discoveries I have made during this setup process this week to serve as a reminder to me and will hopefully help others too.


Reporting Services Web Part – Firefox Compatibility (Windows, Linux – 3.6.13)

  • Turn off Asynchronous data (Edit Web Part Settings > View Section)

NB: If this setting is left to its default, the loading screen does not go away until you hit the refresh button on the Web Part toolbar.


SharePoint Integration Mode – Linking to pages within SharePoint

It would appear that the Reporting Services web part in SP2010 disables links where the target URL is within SharePoint itself (“/pages/default.aspx” for example).

This blog post suggested a useful workaround:

However the suggested approach was good if you had a definitive URL such as default.aspx, but if you wanted to add a query string parameter based on the data returned, adding as action directly to the image did not allow me to select the appropriate date.

To get round this, I added a calculated field to the data set using the same process suggested in the post:

="javascript:void(window.location.href='/pages/projectdashboard.aspx?projectid='" + dataset.fields[<field name>] +")"

NB: Since working this out, I have extended it to use a standard JavaScript function that is running from the Master Page to make links in the reports more generic.

Then for the action on the image I was able to select “Go to URL” and select my calculated field.

This doesn’t work in the designer preview (or any exports of the report), however it works a treat when you are using the report as part of a dashboard with drill down features.


Interactive Column Sorting

Another useful blog post:

Although the instructions are for SSRS 2005, it is still relevant today.

  • Right click on the column header field
  • Select Properties
  • Interactive Sort Tab
  • Select the appropriate sort field
  • Click OK


Alternating Row Colours

Always a popular choice for tabular based reports.

Highlight the data row and under the properties pane for the background color, use the following expression:

=IIf(RowNumber(Nothing) Mod 2 = 0, "#F7F7F7", "#FFFFFF")



MSDN Documentation Links

NB: Although a lot of the above links are for SSRS 2008, there is an option at the top of the page to make the content relevant for SSRS 2008 R2 also.

Useful JavaScript Function – Refreshing a page with parameters for use with Query String Filter Web Parts in #SP2010 #SharePoint #ProjectServer #PS2010 #in

February 22, 2011 Leave a comment

In my current project I have the need to determine the current users personal preferences based on favourites stored in a list.

These personal preferences then drive various web parts on the page.

Using this the Enterprise Edition of SharePoint Server 2010 makes this very easy with the use of the Query String Filter Web Part and various other web parts that support connections including:

  • Reporting Services Web Part
  • Excel Services Web Part
  • List Web Parts
  • etc…

Using the combination of parameters and the above web parts it makes it a lot easier to create dashboard pages that are specific to a users:

  • Preferences
  • Job Role
  • Department
  • etc…

Now in most of my projects, I have found that dashboard pages tend to be buried under a 2nd or 3rd level of navigation, which makes it very easy to create links with parameters already in them.

However, in this case, I have to create various dashboard pages at the root of the site, whilst using standard SharePoint Publishing Navigation (Global Navigation).  The parameters being passed are user selectable based on favourites stored in a list.

Given that the SharePoint Site Map Provider doesn’t know about this random favourites list I have created, I need to improvise to ensure that the end user is presented with dashboard pages personalised to them, as soon as they hit the root site URL.

Code Example:

1 <script type="text/javascript"> 2 3 var currentLocation = location.href; //Current Page URL 4 //set it to lowercase for comparison purposes 5 currentLocation = currentLocation.toLowerCase(); 6 7 //run function before document / SharePoint ready to avoid unwanted page flashing 8 refreshPageWithParameters(); 9 10 function refreshPageWithParameter() { 11 12 var urlParameter = ""; 13 14 //In this example I am checking for a query string parameter called portfolioid 15 urlParameter = querySt("portfolioid"); 16 17 //confirm we do not have the parameter so the page refresh doesn't loop 18 if (urlParameter == "") { 19 //Stop the rest of the page render, JavaScript will still run 20 document.execCommand("stop"); 21 22 /* 23 Run functions you need to do to get your parameter value here 24 25 set output of functions to urlParameter below 26 */ 27 28 urlParameter = "test"; //<function output here>; 29 30 var newURL = currentLocation; 31 32 if (currentPathname.indexOf("?") != -1) { 33 //& - Other Query String Parameters exist - add this to the end 34 newURL = "&portfolioid=" + urlParameter; 35 } 36 else { 37 //? - Other Query String Parameters do not exist 38 newURL = "?portfolioid=" + urlParameter; 39 } 40 41 //Refresh the page with the new URL 42 location.href = newURL; 43 44 } 45 46 } 47 48 function querySt(ji) { 49 50 //This Useful JavaScript function was explained in a previous blog post 51 hu =; 52 hu = hu.toLowerCase(); 53 gy = hu.split("&"); 54 55 for (i=0;i<gy.length;i++) { 56 ft = gy[i].split("="); 57 if (ft[0] == ji) { 58 return ft[1]; 59 } 60 } 61 62 return ""; 63 } 64 65 </script>

Essentially the following happens:

  • The page load to a certain point
  • We determine if we have the query string parameter(s) we are looking for
  • If we don’t, we stop the page from rendering using document.execCommand(“stop”)
  • Go get our require parameter(s)
  • Determine if any previous parameters existed on the page currently (to make the code more generic)
  • Reload the page with the new parameter(s)

If you find any unwanted page flashing before the page reload, use JavaScript to set style=”display: none;” on the main content <div> on the v4.master – Master Page, if the parameter doesn’t exist.

Although this is certainly one method of achieving my goal and seems to currently fit my clients requirements, I would certainly be interested to hear from others on how to achieve my goal without drastically changing standard SharePoint components.

Useful JavaScript Function: isInteger and isNumeric #SharePoint #SP2010 #in

February 16, 2011 Leave a comment

Just a quick post as a reminder for myself in future projects.

1 function isNumeric(sText) 2 { 3 var ValidChars = "0123456789."; 4 var IsNumber=true; 5 var Char; 6 7 8 for (i = 0; i < sText.length && IsNumber == true; i++) 9 { 10 Char = sText.charAt(i); 11 if (ValidChars.indexOf(Char) == -1) 12 { 13 IsNumber = false; 14 } 15 } 16 return IsNumber; 17 18 } 19 20 function isInteger(sText) 21 { 22 var ValidChars = "0123456789"; 23 var IsNumber=true; 24 var Char; 25 26 27 for (i = 0; i < sText.length && IsNumber == true; i++) 28 { 29 Char = sText.charAt(i); 30 if (ValidChars.indexOf(Char) == -1) 31 { 32 IsNumber = false; 33 } 34 } 35 return IsNumber; 36 37 } 38

Programmatically setting a field to hidden within a Content Type #SharePoint #SP2010 #in

February 9, 2011 2 comments

At a client recently, my development colleague was struggling to find out how to set a field attached to a custom Content Type in a list to be hidden to aid in document management (so it is not shown in the Document Properties panel in Microsoft Word).

The following post seemed to suggest an unofficial way:

However, we try to keep things in a supported model if possible.

Through further looking he came across the following property:

    After further playing in code, he managed to crack it with the “.hidden” method.
    Code Example:

This code will set the Hidden flag (same as you can through the UI) against the document library. The above iterates through a List of strings which contains the fields i want to hide.

    1 SPContentType docCT = docLib.ContentTypes[0]; 2 3 foreach (string fieldDispName in fieldsToHide) 4 { 5 SPField field = docCT.Fields[fieldDispName]; 6 docCT.FieldLinks[field.Id].Hidden = true; 7 } 8 9 docCT.Update(); 10

NB: Please not that this works because we are using our own content type attached to the library.

Creating Test / Filler / Example text from Microsoft Word #SharePoint #SP2010 #in ‘#MSOffice

February 7, 2011 1 comment

I always forget the following link when I want some example text in my projects for Plain / Rich Text fields (in Lists and InfoPath Forms), Word Documents (Document Management etc.)

Microsoft Office Help Link:

Quick Reference

Within Word type the following:

  • =rand()
  • =lorem()

These produce a single paragraph, but the function is configurable via the following parameters:

  • =rand(insert the number of paragraphs, insert the number of sentences)
  • =lorem(insert the number of paragraphs, insert the number of sentences)

Example: =lorem(1,6)

%d bloggers like this: