1,804 articles and 15,419 comments as of Monday, December 27th, 2010

Tuesday, December 9, 2008

Visual Indicators for the Masses: KPIs in WSS

SharePoint comes with really cool web parts for presenting key performance indicators, but these are only available in the Enterprise edition. If you are running Windows SharePoint Services or SharePoint Server Standard, it is not available. Which SharePoint technology is right for you?

In this article I will create the same KPIs as Mark did in Part 1, but I will use only Windows SharePoint Services functionality. In order to do that you will need Microsoft Office SharePoint Designer 2007. The only limitation of this concept is that you can only build KPIs on top of data stored in SharePoint lists.

Create a list

Create a simple SharePoint list to store KPI values.

Image 01

Create a data view

In order to display KPI indicators we will use built-in Data View web part.

Your results should look like this:

Image 02

Create KPIs

Now it’s time to create some fancy indicators to show if goal is reached. To do so you will need to open your page in SharePoint designer code view. Look for the following code defining the header of the list view

<th class="ms-vh" nowrap="nowrap">Indicator</th>
<th class="ms-vh" nowrap="nowrap">Goal</th>
<th class="ms-vh" nowrap="nowrap">Value</th>

Add an additional th tag. It will create one additional column to display our KPI status icon.

<th class="ms-vh" nowrap="nowrap">Indicator</th>
<th class="ms-vh" nowrap="nowrap">Goal</th>
<th class="ms-vh" nowrap="nowrap">Value</th>
<strong><th class="ms-vh" nowrap="nowrap">KPI</th></strong>

Now we will need to add actual code to display the image. Look for the following code:

<td class="ms-vb">
     <xsl:value-of select="format-number(@Value, '#,##0.00;-#,##0.00')"/>
</td>

The code above displays the value of the Value column of our list. Paste the following code below it.

<td class="ms-vb">
    <img alt="KPI Status Icon">
        <xsl:attribute name="src">
            <xsl:choose>
                <xsl:when test="number(@Value) &lt;= number(@Goal)">/_layouts/images/KPIDefault-0.GIF</xsl:when>
                <xsl:otherwise>/_layouts/images/KPIDefault-2.GIF</xsl:otherwise>
            </xsl:choose>
        </xsl:attribute>
    </img>
</td>

The solution is very simple. It will show green light when the value is lower than or equal to the goal or red light when this condition is not satisfied. For this article I have used default SharePoint icons, but change URLs above to use your own KPI icons.

Image 03

KPI advanced features

The built in KPI web part gives you some additional features that cannot be replaced with the solution proposed above. Let’s expand our solution to fix that.

Add the following columns to your list:

  • BetterValuesAre – Choice column – Two options: Lower and Higher
  • GreenIndicatorThreshold – Number column
  • YellowIndicatorThreshold – Number column

We are going to use these columns to allow different rules to be applied to KPI Status Icons. Data View will not add new columns into view by default and you will have to do that manually.

<td class="ms-vb">
    <img alt="KPI Status Icon">
        <xsl:attribute name="src">
            <xsl:choose>
                <xsl:when test="@BetterValuesAre = 'Lower'">
                    <xsl:choose>
                        <xsl:when test="number(@Value) &lt;= number(@GreenIndicatorThreshold)">/_layouts/images/KPIDefault-0.GIF</xsl:when>
                        <xsl:when test="number(@Value) &lt;= number(@YellowIndicatorThreshold)">/_layouts/images/KPIDefault-1.GIF</xsl:when>
                        <xsl:otherwise>/_layouts/images/KPIDefault-2.GIF</xsl:otherwise>
                    </xsl:choose>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:choose>
                        <xsl:when test="number(@Value) &gt;= number(@GreenIndicatorThreshold)">/_layouts/images/KPIDefault-0.GIF</xsl:when>
                        <xsl:when test="number(@Value) &gt;= number(@YellowIndicatorThreshold)">/_layouts/images/KPIDefault-1.GIF</xsl:when>
                        <xsl:otherwise>/_layouts/images/KPIDefault-2.GIF</xsl:otherwise>
                    </xsl:choose>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:attribute>
    </img>
</td>

The final result is shown in the following figure.

Image 04

The table format is not mandatory for Data View web part. You could also use “repeating layout” to create something like this.

Image 05

In the next article of these series we are going to examining how to embed the KPI view in your list and how to handle KPI calculations based on Date Values.

Author: Toni Frankola
SharePoint Use Cases
Toni started his Web adventure in late 90’s and has been working with various web technologies ever since. Today he is primarily focused on Microsoft Office SharePoint and Dynamics CRM. He works at Perpetuum Mobile, a Microsoft Gold Partner from Croatia. Toni is leading project engagements and managing a team of consultants specialized in Microsoft technologies.

 

Please Join the Discussion

29 Responses to “Visual Indicators for the Masses: KPIs in WSS”
  1. Christophe says:

    Note that we can get the same result without SharePoint Designer:
    http://pathtosharepoint.wordpress.com/2008/09/01/apply-color-coding-to-your-sharepoint-lists/
    (see the “indicator” column)
    Christophe

  2. Christophe – The purpose of this article is to show alternative ways to do the same thing. Yes, you and I both have a solution for this, but Toni has provided us with another, viable alternative. The more angles to look at, the more people will learn. — Mark

  3. Well, that’s one of many reasons that I really love SharePoint – you can achieve the same result in any which way that works for you. :)

  4. Christophe says:

    Mark and Martin: I agree, but then it would be useful for the readers to understand the trade-offs, and which method is better in which case.
    Let me start with the advantages of my method ;-)
    - WSS, SharePoint UI only. No need for MOSS or SharePoint Designer
    - site still supported by Microsoft (same reason, no use of SPD)
    - site/list can be exported and reused as template
    - applies to list views (table, boxed, newsletter), calendar views and display forms (DispForm.aspx)
    - grouping in lists will work in both IE and Firefox (doesn’t work in Firefox for the DVWP)

  5. rita says:

    Hi!
    I’m trying to implement my KPI list but I got an error:

    “This web part does not have a valid XSLT stylesheet: Error: the character “<” can not be used in a value of an attribute.
    /_layouts/images/KPIDefault0.GIF”

    Please help me.
    tanks

  6. There is a problem with formula output, we will fix that ASAP.

    In the meantime simply replace the operator ‘=’ with ‘>=’

  7. Please disregard my last comment, it seems I cannot paste the operators in this box :(-. I will send you the update via email.

  8. rita says:

    Thank you. Now it’s working :D

  9. Toni – Try using ` (backticks) around the code and see what happens. — Mark

  10. Ok, ignore my last suggestions, too. I’ll go in and make the changes manually. You should see it within the hour. — Mark

  11. Ok, team. Everything is fixed and should be ready to cut and paste. — Mark

  12. Alex says:

    I am trying to create this KPI scorecard, but am running into a little trouble. I have created a List (i think its an access list) but i cant seem to be able to find the “Data View Web Part” you have mentioned. Is this something extra that i need to install?

  13. @Alex: From your SharePoint Designer: Insert > SharePoint Controls > Data View

    Hope it helps.

  14. Alex says:

    Thanks, I am now having the problem that every time I click and so select the values is would like to import into the data view. SharePoint Deisgner crashes has an error and wants to send this into to MS…

  15. @Alex: This seems to be an issue with SPD. Try to reinstall the app.

    You can also use SPD that is installed on a different computer in the same network…

  16. dick penny says:

    Couldn’t one use the same XSLT techniques to display the KPI gifs in a multi-row, multi-column table from any data in any SP custom list with proper fragments grafted onto a DFWP using SPD(DVWP don’t exist in MOSS anymore)?

    If so, VERY powerful.

  17. Jennifer Schertz says:

    Good news. You don’t need MOSS or SharePoint Designer to implement this. I have applied the same concept to a WSS 2 site and it works great! All that is needed is to export the list web part to a XSLT dataview in FrontPage, and the conditional formatting in the example does the rest. (Of course you have to create and store your own images in a library somewhere on the site.)

  18. Chris says:

    Hi,
    I gave this a try, and got the first part working, where it’s either red or green. Now I’m looking at the more advanced area and not sure how it works.

    Could you explain the 3 new columns we add to the list? How do they work etc?

  19. Uttam Dhakal says:

    I cant see the contents inside like XSL codes and the KPI icons i have tried with every browsers is there any way i can see the whole contents clearly or is it removed somewhere,

  20. Aspid says:

    Hello
    I created a linked data source from two lists – achievements and indicators.
    Insert data from this list DVWP. Then put your miracle-code

    <Xsl: when test = "number (@ Number) /_ layouts/images/KPIDefault-0.GIF
    <Xsl: when test = "number (@ Number) /_ layouts/images/KPIDefault-1.GIF
    / _layouts/images/KPIDefault-2.GIF 
    
    = number (../../../ Indicators @ GreenIndicatorThreshold )”>/_ layouts/images/KPIDefault-0.GIF
    = number (../../../ Indicators @ YellowIndicatorThreshold )”>/_ layouts/images/KPIDefault-1.GIF
    / _layouts/images/KPIDefault-2.GIF
    

    Everything works fine, but the result for all rows is read only the first value from the list of indicators.

    Sorry for bad English!

Trackbacks

Check out what others are saying about this post...
  1. [...] In this article I will create the same KPIs as Mark did in Part 1, but I will use only Windows SharePoint Services functionality. In order to do that you will need Microsoft Office SharePoint Designer 2007. The only limitation of this concept is that you can only build KPIs on top of data stored in SharePoint lists. Read more… [...]

  2. [...] my first post of this series, I discussed using the Data View Web Part to show visually attractive KPIs in Windows SharePoint [...]

  3. [...] my the first two articles of this series, Visual Indicators for the Masses and Embed KPIs as a List View, I discussed ways to create simple KPIs in WSS. This article will [...]

  4. WSS 3.0 & MOSS: Recopilatorio de enlaces interesantes (XXIV)!…

    Un año más ( ¡FELIZ AÑO NUEVO A TODOS! ), aquí estoy dando guerra (y ya van más de dos desde que Rodrigo…

  5. [...] each item (green if we met the target, yellow almost, red not even close). I’ve been using Visual Indicators for the Masses: KPIs in WSS.  So I setup different lists for each one, So in financial the target/actual is set to [...]

  6. [...] Posted by Phil Harding on August 21, 2009 Demonstrates how to create KPI (like) dashboards without requiring MOSS Enterprise, obviously it doesn’t support all the features and flexibility that MOSS Enterprise KPI does but, even so, it’s a great solution. Read the article here. [...]

  7. [...] borrowed from this article at Marc Anderson’s blog to get the comparison logic and this article at http://www.endusersharepoint.com for the basic ideas behind the dashboard written by Toni [...]




Notify me of comments to this article:


Speak and you will be heard.

We check comments hourly.
If you want a pic to show with your comment, go get a gravatar!