Archive

Posts Tagged ‘Calculated Column’

Unique alphanumeric list ID’s via SPD Workflow and Calculated Columns #SharePoint #SP2010 #in

March 9, 2011 14 comments

A common thing I do for most clients is to create a unique alphanumeric ID that is consistent in length with pre-filled zeros.

Risk list alphanumeric ID example:

  • RSK0001

However, as has been documented many times (link); you cannot use the ID column of a list in a calculated column.  To get over this issue I use a simple SharePoint Designer workflow to copy the ID value to another column (Unique Reference) and then base my calculation on the Unique Reference column.

Step One: Create a common site column for use across the Site Collection

  • Column Name: Unique Reference
  • Column Type: Single Line Of Text

Step Two: Create a calculated column for the alphanumeric ID

  • Column Name: Risk ID
  • Column Type: Calculated Column
  • Formula: =”RSK” & TEXT([Unique Reference], “0000”)

NB: The TEXT function will prefill the ID with zeros

    Step Three: Add the Unique Reference and Risk ID column to your List or Content Type

Step Four: Create the SharePoint Designer Workflow

  • Create a list or content type (SP2010 only) workflow
  • Workflow should fire on Creation only (disable Manual and Edit)
  • Use the following steps:

image

  • Publish the workflow (list workflow)

OR

  • Assign the workflow to the content type (if you have created a redistributable workflow in SPD 2010) and assign the content type to the list (content type workflow)

Step Five: Hide the Unique Reference column in the list / content type

  • This will stop the Unique Reference column from showing to the end user.

NB: You must hide the Unique Reference column after you have created and published the workflow otherwise it will not appear in SharePoint Designer

You list items will now have a unique alpha numeric ID

NB: Please remember that automatic workflows will not fire if you are logged in as the farm account (link)

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: http://www.expertprogrammanagement.com/2010/06/project-risk-management/

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:

probabilityVsImpact

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

http://www.sharepointusecases.com/index.php/2009/01/customizing-datetime-format-on-a-list-view-web-part/

Formula Example:

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

Output:

09-Mar-2011

Follow

Get every new post delivered to your Inbox.

Join 1,690 other followers

%d bloggers like this: