Nov 27 2013
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.
- 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
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.
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.
Take the following code and paste it into a new text file (using Notepad or another text editor) and save it as “TexttoHTMLSP013.txt”.
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.
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.
Click on the ribbon and then List>List Settings>
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.
Click on the ribbon and then List>List Settings>Create Column>
Column Name: Indicator
Column Type: Calculated
=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.
Click on the new item link
Title: Work Item 01
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.
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.
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.