Craig "hachiroku" Mercer

Creating Custom Key Performance Indicators with Calculated Columns

Consider the following scenario
A team wants to be able to prioritize their work so they can visually see what are the most important items to focus on. They will use low, medium and high priorities, represented by yellow, orange and red images.

Things You Need

• Custom indicator images for as many levels as you require.
• Text to HTML JavaScript
• A custom list
• A choice type column in the list
• A calculated column in the list
• A web part containing the Text to HTML script

Step 1 – Get the Images

Instructions:

Go to Icon Finder (https://www.iconfinder.com/) and do a search for the kind of icons you want. In this case we’ll search for “square icon” and choose a glossy orange square at 24×24 pixels and download it to our PC. Then we’ll look for yellow and red in the same theme set and download those as well. The images tend to come with weird filenames so we’ll rename them to something more user friendly such as: “icon-yellow.png”, “icon-orange.png” and “icon-red.png”.

NOTE: It is important to find images that are in the Portable Network Graphic (PNG) format because they have transparent backgrounds and will look good in any theme.

Step 2 – Upload the Images to SharePoint

Next we’ll need to upload the images into SharePoint. SharePoint 2013 sites come with a built in library called “Site Assets” and it is highly recommended to store the images in this location.

Instructions:

Click on the Site Contents link and the click on the Site Assets library.
Click on new document and upload each image from your PC.

Step 3 – Create the Text to HTML Script

The following script is available from Christophe Humbert (Blog Path to SharePoint: http://blog.pathtosharepoint.com/). This has been tested and confirmed to work with SharePoint 2013.

Instructions:
Take the following code and paste it into a new text file (using Notepad or another text editor) and save it as “TexttoHTMLSP013.txt”.

<script type="text/javascript">
 // Copyright (c) 2010 Christophe Humbert - Path to SharePoint
 // Find all Web Parts in the page
 var listWP=[],calWP=[],divs=document.getElementById("DeltaPlaceHolderMain").getElementsByTagName("div");
 var count=divs.length;
 for (i=0;i<count;i++) {
 try {
 if (divs[i].id.indexOf("WebPartWPQ")==0){
 if (divs[i].innerHTML.indexOf("ViewDefault_CalendarView")>=0) {
 // Calendars
 calWP.push(divs[i].id);
 }
 else {
 // Other Web Parts
 listWP.push(divs[i].id);
 }
 }
 }
 catch(e){}
 }
 function TextToHTML(NodeSet, HTMLregexp) {
 var CellContent = "";
 var i=0;
 while (i < NodeSet.length){
 try {
 CellContent = NodeSet[i].innerText || NodeSet[i].textContent;
 if (HTMLregexp.test(CellContent)) {NodeSet[i].innerHTML = CellContent;}
 }
 catch(err){}
 i=i+1;
 }
 }
 var regexpA = new RegExp("\s*<([a-zA-Z]*)(.|\s)*/\1?>\s*$");
 var regexpTD = new RegExp("^\s*<([a-zA-Z]*)(.|\s)*/\1?>\s*$");
 var WP = new Object;
 function UpdateWP() {
 if (calWP.length>0){
 for (i=0;i<calWP.length;i++) {
 WP=document.getElementById(calWP[i]);
 if (WP.innerHTML.indexOf("&lt;")>=0) {TextToHTML(WP.getElementsByTagName("a"),regexpA);}
 }
 }
 if (listWP.length>0){
 for (i=0;i<listWP.length;i++) {
 WP=document.getElementById(listWP[i]);
 if (WP.innerHTML.indexOf("&lt;")>=0) {TextToHTML(WP.getElementsByTagName("td"),regexpTD);}
 }
 }
 // Check every 200 ms, forever
 setTimeout("UpdateWP()",200);
 }
 UpdateWP();
 </script>

Step 4 – Upload the Text to HTML Script

Now let’s upload the TexttoHTMLSP013.txt file to the Site Assets library just we did with the images in Step 2.

Step 5 – Create a new Custom List

Next we need to create a custom list (via the Add an app feature in SharePoint 2013) that will have our custom images used in.

Instructions:
Click on the settings “Cog” and then select “Add an app”.

Next, let’s select the Custom List type and then name it “VisualWork” (of course in practice you can name it whatever you want).

NOTE: If you create a name with white space in it (e.g.: Visual Work), the URL will end up with extra characters in it (e.g.: …/Visual%20Work.aspx). This is due to the fact that web addresses cannot contain whitespace so the white spaces are rendered with a “%20” instead of a blank space. Furthermore once a URL has been created in SharePoint is it permanent. The title and description can be modified after creation by the URL cannot. So if you create a list with white space in the name it cannot be undone.

Step 6 – Create a new Choice type Column in the List

Next we need to create a choice column which we will use to calculate against. In this case we’ll create a new choice column called “Priority” and then give it three choices: Low, Medium and High.

Instructions:
Click on the ribbon and then List>List Settings>

Create Column

Column Name: Priority
Column Type: Choice
Type each choice on a separate line:
Low
Medium
High

Then click OK.

Step 7 – Create a new Calculated type Column in the List

Now we need to create a calculated column which we will use to show the images based on what is selected in the choice column.
In this case we’ll create a new calculated column called “Indicator” and then give it three possible calculations. The calculation will be if Priority is equal to Low then show a yellow image, if Priority is equal to Medium then show an orange image and finally, if Priority is equal to High then show a red image.

Instructions:
Click on the ribbon and then List>List Settings>Create Column>
Column Name: Indicator
Column Type: Calculated
Formula:

=IF(Priority=”Low”,”<DIV><img src=https://<your url>/sites/SiteName/SiteAssets/icon-yellow.png title= Priority.is.LOW></DIV>”,IF(Priority=”Medium”,”<DIV><img src= https://<your url>/sites/SiteName/SiteAssets/icon-orange.png title=Priority.is.MEDIUM></DIV>”,IF(Priority=”High”,”<DIV><img src= https://<your url>/sites/SiteName/SiteAssets/icon-red.png title=Priority.is.HIGH></DIV>”)))

Step 8 – Add new item to the list

Now we need to create an item in the list.

Instructions:
Click on the new item link
Title: Work Item 01
Priority: Low
Then click Save.

If all went well you should now see item 1 in the list and it will show the code for the low image in the indicator column.

Step 9 – Add the Text to HTML script and move it under the list

Now we need to add the Text to HTML script to tell SharePoint to render the image instead of the code. We’ll do this by first getting the URL (address) to the script and then adding it into a new Content Editor on the page.

Instructions:
Go into the Site Assets library, right hand mouse click on the script and select “Copy shortcut”.

Next click on the Visual Work list and then on the settings “Cog” and then Edit Page

Then click on the ribbon and select Insert and then Web Part

Then go to the Media and Content section and select the Content Editor option and click Add.

The web part should now be added to the page just above the list. Next click on the upside down triangle and then click on the “Edit Web Part” link.

After you click on the link the window should reload and show the editor for the Content Editor. In the content link section paste the shortcut that you copied from the Site Assets library and then click OK.

Finally, in order to make the image render you will need to drag the Content Editor underneath the Visual Work list.

Next you need to click on “Stop Editing”

Now you should have the image rendering properly as below.

NOTE: Anywhere on your site that you want to show the image(s) you will need to add the content editor with the shortcut link and move it under the list or web part.