Create KPI on SharePoint Lists

Users who already invested in Windows SharePoint Services WSS 3.0 and want to leverage the KPIs e.g. Issues, Risk or any custom list, similar to SharePoint Portal Server KPIs.

Below is the lil solution.

We will be working on an existing workspace with issues lists. You need to have full permission to access the workspace using SharePoint Designer and modify the workspace lists.

Create Impact field with Choice menu and add the value as shown below.

(1) Critical

(2) High

(3) Medium

(4) Low

Create Priority field with Choice menu and add the value as shown below.

(1) Critical

(2) High

(3) Medium

(4) Low

Create KPI field select the Calculated and enter the formula as shown below

=IF(OR(AND(Priority=”(4) Low”,Impact=”(4) Low”),AND

(Priority=”(4) Low”,Impact=”(3) Medium”),AND

(Priority=”(4) Low”,Impact=”(2) High”),AND

(Priority=”(3) Medium”,Impact=”(4) Low”),AND

(Priority=”(3) Medium”,Impact=”(3) Medium”)),”Low”,

(IF(OR(AND(Priority=”(3) Medium”,Impact=”(2) High”),AND

(Priority=”(2) High”,Impact=”(4) Low”),AND

(Priority=”(2) High”,Impact=”(3) Medium”),AND

(Priority=”(1) Critical”,Impact=”(4) Low”)),”Medium”,

(IF(OR(AND(Priority=”(3) Medium”,Impact=”(1) Critical”),AND

(Priority=”(2) High”,Impact=”(2) High”),AND

(Priority=”(1) Critical”,Impact=”(3) Medium”),AND

(Priority=”(1) Critical”,Impact=”(2) High”)),”High”,”Critical”)))))

Important: Copy the above mentioned formula into a notepad and remove all the spaces or gaps between the lines. If you do not remove the gaps and paste the formula as is, SharePoint will not accept the format and through error.

Create new view for Issues List and name it “Dashboard” view.

Keep Title and KPI field only.

Add Issues webpart on the Home Page

Modify the webpart and select the new view which you created earlier “Dashboard”.

Edit the workspace Homepage using SharePoint Designer.

Open the current workspace in SharePoint Designer and Open the Default.aspx page to edit the webpart.

Create a new folder under images folder and name it Issues. Also copy all four (Critical.gif, High.gif, Medium.gif and Low.gif) files.

Select the Issues webpart from the homepage.

Convert to XSLT Data View.

Split the view in SharePoint Designer and Select the value against the KPI in Issues List webpart.

Select the existing value

<TD><xsl:value-of disable-output-escaping=”yes” select=”ddwrt:AutoNewLine(string(@KPI))” /></TD>

Change the value as per below

<TD><img border=”0″ src=”images/Issues/{@KPI}.gif” alt=”{@KPI}” /></TD>

You can see the values in KPI Column changed to image from the text value.

Save and Close the site.

You can now have KPIs against Issues list or any list with values. Just need to create the perfect formula and you can do a lot with this.

Save as these KPI .gif for sample use.

I hope this was helpful.

!! Happy Dashboards!!


4 comments so far

  1. Patrícia, Brazil on

    Hello friend,
    I was searching the internet a way to create my KPI, no need to install the Report server. And your tutorial has served me fairly.
    Some of the fixes I’d like to tell, since I have the WSS 2007 are:
    In creating the lines, I had to place the place the “,” -> “;”
    Ex: = IF (OR (AND (Priority = “(4) Low, Impact =” (4) Low “), AND
    correction: = IF (OR (AND (Priority = “(4) Low”; Impact = “(4) Low”); AND

    And in line src=”images/Issues/{@KPI}.gif” alt=”{@KPI}” I had to redo all the ” ” because it was showing errors.

    Thanks for your help. Brazilian hugs.

    • Cash on


      It is great to know that the content helped you to achieve what you need.

      Thanks so much for leaving comments!

      Good on Ya!

  2. Nick on

    Wow this is great…power of calculated columns in SP is amazing

  3. Abdullah on

    thank you so much very lovely formula

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: