KPI Dashboard using SharePoint List
We can generate the KPI list using simple SharePoint list with computed columns! Here is how we can get SharePoint list based status indicator
Create a SharePoint list with desired fields, then create a field with choices, Lets say, Field name Cost_Input: with values Red, Green, Blue. Then lets create another computed field called Cost of type: Number, which is based on the existing field COST_Input field.
Enter the formula for the calculated field as:
=IF(Cost_input="Green","<DIV style='text-align: center'><IMG src='/_layouts/images/KPIDefault-0.gif'
/></DIV>",IF(Cost_input="Yellow","<DIV style='text-align: center'><IMG src='/_layouts/images/KPIDefault-1.gif'
/></DIV>",IF(Cost_input="Red","<DIV style='text-align: center'><IMG src='/_layouts/images/KPIDefault-2.gif'
/></DIV>")))
Then after add content editor webpart exactly after the list webpart, then add the below code snippet in the source editor of content editor webpart
Create a SharePoint list with desired fields, then create a field with choices, Lets say, Field name Cost_Input: with values Red, Green, Blue. Then lets create another computed field called Cost of type: Number, which is based on the existing field COST_Input field.
Enter the formula for the calculated field as:
=IF(Cost_input="Green","<DIV style='text-align: center'><IMG src='/_layouts/images/KPIDefault-0.gif'
/></DIV>",IF(Cost_input="Yellow","<DIV style='text-align: center'><IMG src='/_layouts/images/KPIDefault-1.gif'
/></DIV>",IF(Cost_input="Red","<DIV style='text-align: center'><IMG src='/_layouts/images/KPIDefault-2.gif'
/></DIV>")))
Then after add content editor webpart exactly after the list webpart, then add the below code snippet in the source editor of content editor webpart
<script type="text/javascript">
//
// Text to HTML
//
var theTDs = document.getElementsByTagName("TD");
var i=0;
var TDContent = " ";
while (i < theTDs.length) {
try {
TDContent = theTDs[i].innerText || theTDs[i].textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
theTDs[i].innerHTML = TDContent;
}
}
catch(err){}
i=i+1;
}
//
// ExpGroupRenderData overwrites the default SharePoint function
// This part is needed for collapsed groupings
//
function ExpGroupRenderData(htmlToRender, groupName, isLoaded) {
var tbody=document.getElementById("tbod"+groupName+"_");
var wrapDiv=document.createElement("DIV");
wrapDiv.innerHTML="<TABLE><TBODY id=\"tbod"+ groupName+"_\" isLoaded=\""+isLoaded+ "\">"+htmlToRender+"</TBODY></TABLE>";
var theTBODYTDs = wrapDiv.getElementsByTagName("TD"); var j=0; var TDContent = " ";
while (j < theTBODYTDs.length) {
try {
TDContent = theTBODYTDs[j].innerText || theTBODYTDs[j].textContent;
if ((TDContent.indexOf("<DIV") == 0) && (TDContent.indexOf("</DIV>") >= 0)) {
theTBODYTDs[j].innerHTML = TDContent;
}
}
catch(err){}
j=j+1;
}
tbody.parentNode.replaceChild(wrapDiv.firstChild.firstChild,tbody);
}
</script>
Update: For SharePoint 2013, Use the below code in Script editor:
Set the conditional formatting style:
<script type="text/javascript" src="http://www.google.com/jsapi"></script> <script type="text/javascript"> //load jQuery google.load("jquery", "1.2.6"); </script> <script type="text/javascript"> //make a fn for reuse $.calcHTML = function() { //loop through TDs moving text node to html $("td.ms-vb2,td[id$='Calculated'],td.ms-stylebody,td.ms-formbody").filter(":contains(<DIV)").each(function(){ $(this).html($(this).text()); }); }; //call calcHTML on DOM ready for ungrouped rows $(function() { $.calcHTML(); if (showpreview1) { //check for preview script $("td.ms-vb-title").bind("mouseover", function(){ showpreview1(this); $.calcHTML(); }); } }); //replace ExpGroupRenderData, call calcHTML for grouped rows function ExpGroupRenderData(htmlToRender, groupName, isLoaded) { $("#tbod"+groupName+"_").attr("isloaded",isLoaded).html(htmlToRender).show("fast",$.calcHTML()); } </script>Thanks to: https://www.nothingbutsharepoint.com/sites/eusp/pages/jquery-for-everyone-html-calculated-column-plugin.aspx
Alternate approach: Using Dataview web part to Design KPI Dashboard
Same thing can be achieved using SharePoint Designer & Dataview webpart. Just convert the existing List view to XSLT Dataview and set the conditional formatting as in below images.Set the conditional formatting style:
hello
ReplyDeleteGreat article.
I have made this work fine, but my next step is to join 8 of them together.
I.E. I have 8 columns now all with images. R1, R2 R3 etc.
If i make anotyher calcultaed column with =R1&R2&R3 i get them together, but they are verticle not horizontal.
Do you have a solution for this please?
Regards
Clayton
Clayton,
DeleteYou can't use values from different rows in calculated columns! If you must do that, You have to write XSL!!
Thanks a lot Salaudeen, this is what I was exactly looking for!
ReplyDeleteJust a doubt, is it possible to display the KPI with a value next to it? I tried with the symbol "&" or with the formula "CONCATENATE", but then it converts the html to pure text and it doesn't display the traffic light.
Chris,
DeleteThat's where the jQuery comes to the picture. It just converts Text to HTML. Place the jQuery provided above!
Hi Salaudeen,
ReplyDeleteThank you so much for sharing this great solution.
We are using SharePoint Online.
We followed the steps listed above with the list and code added to Script Editor.
We tried it with Cost Column, instead of displaying the KPI icon, the code in the formula shows up.
Please could you kindly advise what went wrong?
Thank you very much for your kind help.
Charlotte Tang
Hi Charlotte,
DeleteUse the jQuery code updated in the article.
hello,
ReplyDeletewhen i use the group by in my list view it doesn't work, any idea?
Hi,
ReplyDeletethe updated code for SP2013 doesn't work for me (on SP 2013), because in Line 1 there are a lot of SPAN tags. Seems like a copy paste error? Without these tags, everything looks fine is fine.
Many thanks for this elegant solution and setup instructions!