Sunday, August 2, 2009

Working with KPI List – Part 1

Hi All,

We all know about the importance of goal in business. Business works on set goals, so it is very important to track where they are, matching the goal, ahead of goal or behind the goal. To answer this question, SharePoint has come up with feature called KPI (Key Performance Indicator).

Key Performance Indicator as name suggests, it gives an idea about where we are performing related to goal. They are found in Report center site, so if you are in WSS working environment, do not think of using this feature because you are not going to get this. This is moss feature.

So let’s get started and understand how it works.

KPI is a special type of list, so while creating a list; we need to select this special list as KPI list, not a normal custom list. When you create a KPI list, then click new, you will see the difference. This is the difference.

Here is a sample of creating KPI list and how the data is entered in it. So first we will create a normal list which will hold the data that will be used in KPI list. Create a list as shown below.

Now observe here that we have sales goal against January of each year. Keep this thing as of now in your mind.

Let us go back to KPI list, Create KPI list and name it as you like.

Once you are done with creation, just click on new and observe the change, here you can see four options.

As you can see, first options says you can create KPI using list options, second option says that you can create KPI using excel workbook, third says that you can create KPI using Sql server 2005 analysis services and the last options says that you can create KPI using manual entry.

We are going to see three out of these four. We will not cover analysis service KPI. We will cover rest of it.

So let us get started and try to create KPI from List option. So click on the first option, once you click on it, it will take you to a page where you can enter the List url from which we want to fetch the data, select the list that we created earlier and click on number of list items in view. Scroll below and enter 8 and 6 respectively like shown below.

Let me explain you what all settings we just did. We have counted the number of items in the list. If total number of items in the list meets criteria of 8 or above, KPI gives green symbol. If total number of items in the list is 6 or 7 then it gives us yellow triangle. If total number of items is below 6 than it gives red square symbol.

So let us go back and see how many items we have in list, we have five items entered in it.

Now let’s go ahead and add more two rows in the list.

Now go back to KPI list and refresh the page and see symbol changes to the yellow triangle that meets to our criteria.

Now go back and add one more item to it to complete our goal of 8 items in the list and that will turn our KPI symbol to green circle.

That completed our simple example of generating KPI based on total number of items in the list. If you have observed while creating KPI, you must have seen other two options as well. It says that you can also go for the percentage value. Other is calculating average, calculating sum etc kind of a function. We will go for calculating average sales. So let’s add one more KPI which is again from List data. So click on new and select KPI from list option. In the page, shown name the KPI and then select third radio button. Select your list by specifying URL and selecting view as All items, here you can also mentioned your specific view for which you want to generate KPI, so this gives us more flexibility for criteria based KPI as well. After this select column as “Sales Goal” and select average from the drop down. Provide 1000 in the goal and 600 in warning textbox. You should have something which looks similar to shown below.

We have specified that if the average sale is 1000 or more, we are safe and it shows green symbol. If it’s between 600 to 999, we are in a warning stage else we are in danger zone and indicated by red square.

Here we have this entry in our base list.

Because average sales is higher than we specified (which is $1,000 in our case), KPI indicator shows us green symbol.

To know more on the same KPI, just click on the KPI item that we have just created; it will take you to a page which shows more detailed information.

Now, go ahead and change the sales values accordingly. When you change the value which makes the average change in them, when average goes below 999 it will start showing yellow triangle and if average changes below 600, it will start showing red square.

Ok, now let us start analyzing the other options which is entering manual information KPI, it is very simple process of giving static data. You just need to give a value and select the criteria goal for it and it simple displays the image depending on the value that you have entered like shown below.

You can also create a KPI indicator which will come from the excel file. But before starting on this, you need to create SSP for this, because the excel file that we are going to use here must be under the trusted location. So create the SSP from central administration and click on the excel trusted file location. Give the entire path till your library name in which your excel book resides. Once you are done with this, then only you will be able to use this feature. So go ahead and create one more KPI indicator and this time select the indicator using data in excel work book. Once you click on this, you need to give the URL of the workbook. So go ahead and select your work book. Now here, once you select the workbook, you need to give the cell number here to mention your indicator value. Along with this you also need to give the cell number for goal and cell number for warning.

This is the snapshot of excel workbook that I have created. In this workbook, in Shhet1 I have mentioned Sales data.

Here D11 cell is an average of sales goal value of D4 to D8 which is our target value as indicator. H$ value indicates the goal to achieve and H7 is the warning sales goal. So let’s specify these values in respective field as shown below.

Just see at the indicator field in KPI list,

Let’s prepare one workbook which look like this, here D11 is calculated column which is average of all sales figures. Cell H4 represents the goal to be achieved and cell H6 represents the warning match.

So here we will specify our criteria like shown above in workbook figure.

Go ahead and have a look at the KPI list how it looks like.

Try to open the workbook for editing and see the changes in the indicator values, you will see different indicators shown in the KPI list.

Now, we are almost done with our examples of KPI. Just one more topic left to discuss which a KPI web part is. We are done with creating KPI list, its supporting list but there is one more thing, which is web part that supports the KPI list. So let’s go ahead and add web parts. Click on edit page, where you want to put this web part. This web part will basically pulls the KPI data from the KPI list and shows details at one go.

Find web part which is named “Key Performance indicators” under Dashboard category. Once you add it, click on modify shared web part and select the KPI list.

One more web part is there which says KPI Details under same Dashboard category. So go ahead and add that web part on the page, click on modify shared web part. Select KPI list, here you will find all KPIs that you have mentioned in the KPI list in the drop down. From there you can select KPI. In this example, I have selected the excel sales data calculation KPI and selected icon as checkmark and result is shown below.

That is it. i will soon come with part 2.


Larisa said...

Thanks! I will get my brownie point for this one at work!
I used to program in ASP.NET and thought that SharePoint will be piece of cake for me. No way!
Well, I am using SP Call Center template trying to adjust it to our requirements. Right now I am figuring out how to display for my boss Average Resolution Time by Assigned To. I've done by your template all closed tickets by hours.
I can group closed tickets by Assigned To. But how can I to calculate the Average Time by each group?
Any thoughts?

SharePoint Kings said...

Not exactly but may be you can do it on javascript !!!

Mahesh P said...

Thanks dude...!!! The article is very easy to understand and useful.

Krishna Prasad said...

I got a clear idea on KPI in sharepoint, thanks a ton !!

This is simple and very lucid !

Krishna Prasad said...

I got a clear idea on KPI in sharepoint , thanks a ton !!

The article is very much lucid !

Guy said...


Would it be possible to create an indicator with multiple goals? What I would like to create is a site where each corporate standard application is an indicator and it's compatibility with Windows 7, Windows 8, IE 7/8/9 etc... is lit up green, yellow or red. Green meaning it works with that OS/Browser, yellow is in progress and red is incompatible. Make sense?

SharePoint Kings said...


sorry but not getting your question. can describe in more detail?

Anonymous said...

This is great but I have a question about using named ranges from an excel file. The KPI list settings states it allows use of named ranges but when I set up a named range of cells in the excel file and try to use it as my KPI indicator I get an error. "Excel Services returned a result with a dimension that does not match the request". Any idea on how to use named ranges in a KPI list from excel spreadsheet?

SharePoint Kings said...


not sure what this error means but is it possible to check SharePoint log?

Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to