SharePoint BI Basics: Set a KPI Column in Your Lists and Libraries as a Visual Reference

Business Intelligence is a hot button with most managers and executives these days, because the immediacy of having real-time data turned into actionable insights is priceless for strategic decision-making. In a recent class on “Business Intelligence Training – The Microsoft Self-Service BI Toolset,” one of our most popular courses, a student asked me an excellent question. With a little research, I came up with a better answer than I was able to give in class off the top of my head. So I thought I’d share it with you.

The question is, “How do I set a KPI (Key Performance Indicator) column for every object in my SharePoint list or library?” Our answer will be applicable to SharePoint 2010 and 2013, but I’ll be using screenshots from SharePoint 2010. 

Initially, I thought that this would require a hyperlink column that would need to be set by a workflow or some manual intervention.  Instead, I ran across this answer in a StackExchange discussion which shows that you can return html in a calculated column.  This means that our calculated column can not only do the evaluation of our conditions, but return the appropriate image for our KPI.  With a little more searching, it turns out that this solution has been around since SharePoint 2007 and just not used very much.  I think there are a lot of people interested in visual indicators who can benefit from this solution.

In my list (or library – both will work the same), I’m going to create a column to evaluate.  This will be my trigger to define which KPI will show up.  I’m going to make this column a choice field and set the three options to Correct, Incorrect, and Undefined.  For the KPI column, I will create a calculated column. This calculated column will use an IF calculation to set the actual KPI.  Now what is fascinating about this calculated column is that if we set the returned value to be a “number”, then we can use the following formula to return lines of html that will be interpreted by the web browser. 

=IF([KPItrigger]="Correct","<img src='/_layouts/images/KPIDefault-0.GIF'>","<img src='/_layouts/images/KPIDefault-1.GIF'>")

In this IF statement, I check the column ( [KPItrigger] ) to see if it is equal to a specific string (“Correct”).  I then use built in KPIs that are hidden in SharePoint to display the status; a green circle or a yellow triangle.  For a pretty full list of images available in SharePoint click on this link. Look in the alphabetical view for the most comprehensive listing.

Now when I create items in the list, I get KPIs.

Right now, I only have 2 KPIs defined.  If I want to have a more obvious KPI for my 3^rd KPItrigger option of “Incorrect”, I need to get a little creative with my calculation.  I will have to nest an IF statement inside the first IF statement to allow for 3 KPIs. (formula available for copy and paste below image)

=IF([KPItrigger]="Correct","<img src='/_layouts/images/KPIDefault-0.GIF'>",IF([KPItrigger]="Incorrect","<img src='/_layouts/images/KPIDefault-2.GIF'>","<img src='/_layouts/images/KPIDefault-1.GIF'>"))

This will check the first IF and then turn over to the second IF when it doesn’t find the “Correct” value.  With the second highlighted IF statement, when the “Incorrect” value is not found then the KPIDefault-1.GIF image will be used for any other value found.

By just changing the formula and not changing our data, we now get 3 KPIs on our list.

This is an example of how Business Intelligence can improve the way you do business.  With just a little effort and knowledge, you can simplify a list of information to provide visual indicators.  This is just scratching the surface of what Microsoft’s Self-Service Business Intelligence capabilities can do for you.

To learn in-depth about Microsoft’s Self-Service BI Toolset, check out our hands-on 3-day course, Business Intelligence Training – The Microsoft Self-Service BI Toolset, on our PremierPointSolutions.com website. In just 3 days, you can learn to build no-code BI applications, reports, and dashboards by leveraging the power of PowerPivot, Power View, Report Builder and PerformancePoint in combination with SharePoint.