Simple No-Code Visual KPI Using A List Calculated Column

Products: SharePoint 2010, 2013

[Updates: Also works for 2013]

There are numerous posts regarding calculated columns and the ability to display KPI images based on list values, etc.   Microsoft provides a reference to a list of the basic formulas that may assist you with solving some of your most common business related challenges.

Many of the other solutions call for some special HTML code to be added to the page or to another column within the list.  For this solution, no additional code is required; it’s simply a matter of correct configuration.

Requirements

The client has a simple list of systems and servers that need to more visible to the enterprise.  The expectation is to provide a visual KPI for each item on the list and manage this from the intranet Help Desk site.  As always, this solution needs to be simple to manage by the key stakeholders of the site.

Create a new list and name it SystemStatus; then configure the following columns.

Columns
The following columns are required for this solution.
– 1st column: Title        (Required)
(single line of text; 255 characters)

– 2nd column: Description  (Optional)
(multi-line)

– 3rd column: Status       (Required)
(choice options: Available (default), Experiencing Problems, Down)

– 4th column: Region       (Required)
(choice options: Central, East, West)

– 5th column: Result       (Required)
(calculated)
(data type returned as a number with zero decimal places)

Formula
=”

NOTE: After copying the above formula to your list column formula box, you may need to re-type the single & double quotes !!

Images

There are numerous icons available with the delivered SharePoint platform.  These may be found on the server at …/yoursite/_layouts/images/… .  This solution references the following icons to indicate the health of our systems and servers across the enterprise.

http://yoursite/_layouts/images/kpidefault-0.gif   [Green Symbol] http://yoursite/_layouts/images/kpidefault-1.gif   [Yellow Symbol] http://yoursite/_layouts/images/kpidefault-2.gif   [Red Symbol]

System Status Management

The Site Owners and Content Editors of the site refer to the System Status list when making changes.

  • manage the Status value
    * select one of the three options: ‘Available’, ‘Experiencing Problems’ or  ‘Down’
  • manage the Region value
    * select one of the three options: ‘Central’, ‘East’ or ‘West’

Results

References

Advertisements
This entry was posted in Business Intelligence, KPI. Bookmark the permalink.

12 Responses to Simple No-Code Visual KPI Using A List Calculated Column

  1. Venkatesh R says:

    It’s rendering the HTML tag as plain text. (Ie. Browser displays the HTML tag instead of Image). Am searching for JS code to make the Text to HTML.

  2. andrewwilkie says:

    Thanks for this, I’ve been able to follow along, yet how can I get my list to look like your “Results” image? That would look a lot neater than an ‘across’ the page list

    • kerdirks says:

      Using SharePoint Designer
      – create an ASPX page
      – insert the SharePoint control of Web Part Zone
      – insert the SharePoint control of Data View
      – locate the System Status list in the Data Source window pane
      – click on System Status and select Show Data
      – select the Result column, and insert as a Multiple Item View
      – set grouping on the Region column
      – if the icon image doesn’t display properly, format an IMG statement with the Result column including the URL to the KPI icon
      – the HTML/XSL should resemble the following

      div style=”text-indent:30px;”
      img src=”{@Result}” alt=”” Select=@Title
      div

      Kerry

  3. Ignas says:

    If you use this, SharePoint REST services will stop work for list with this column.

  4. jose says:

    I AM GETTING FORMULA ERROR WHEN USED IN SHAREPOINT ONLINE(OFFICE 365). Any work around please let me know

  5. Sweta Grover says:

    I am using sharepoint 2013 – When I tried to put the code in formula of calculated column – I get the follwoing error. Here is my Formula – I just changed the URL for my images.

    =””

    Error Message – as below – any Help in this regards is appreciated.
    Sorry, something went wrong The formula contains a syntax error or is not supported.
    Technical Details
    Learn more about the syntax for formulas.

    Troubleshoot issues with Microsoft SharePoint Foundation.
    Correlation ID: 3064c49c-1d83-1045-f35c-6f26a023a960
    Date and Time: 10/21/2014 12:01:49 PM

    ——————————————————————————–

    Go back to site

    • kerdirks says:

      Sweta,
      I was able to use the above Formula in my SP2013 Test Site with success.
      NOTE:
      After copying the Formula into the list column Formula box, re-type all single quotes and double quotes. Appears there is a syntax compatibility issue with copying the Formula directly from WordPress.

  6. Craig says:

    The formula appears in my web browser like its shown below. Is there some text that i am missing?

    =”

    http://enterprise/_layouts/images/kpidefault-0.gif“,
    IF(Status=”Experiencing Problems”,”http://enterprise/_layouts/images/kpidefault-1.gif“,
    IF(Status=”Down”,”http://enterprise/_layouts/images/kpidefault-2.gif“,””))))
    &”‘>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s