Building a KPI List using Excel in Sharepoint 2007

The short definition of a KPI is the following: A consolidated value derived from data extracted from a database or other source that is displayed as an picture. Microsoft has built a feature into Sharepoint called a KPI List that will allow us to build the KPI’s we need. This is done by selecting “”Create”” from the “”Site Actions”” menu and selecting “”KPI List””. If this item does not appear, activate it by navigating to “”Site Settings”” and then selecting “”Site Features””.

Once the KPI list is created, we need to create a source from which to build our first KPI. For illustration purposes I will be using an Excel spreadsheet as a source. Create a document library with Excel as the default option and select new spreadsheet from the menu.

In the first sheet within the spreadsheet we will create some data to serve as the source for our KPI. Depending on the source for the KPI the values required will differ, but in Excel we need only three:Indicator – this value represents the current value of what we are trying to measure.Warning – this value represents the threshold that will determine whether we are over or under performing.Goal – this is the value we would like to achieve.
Using a combination of these values we can easily build a colour code or picture system to display the data.

Save the spreadsheet back to the document library from where it originated and give it a name like “”KPI Source””. We will need to link to it later when we build our KPI in the KPI list. Close Excel and navigate to the KPI list we created earlier in Sharepoint. Select the “”New”” option on the menu bar.

There are a number of sources available that can be used to create a KPI:Sharepoint List – Other lists that have already been created in Sharepoint can be used as a source.Excel Workbook – This is the option that we will be using. Accountants love their spreadsheets.Analysis Services – Cubes are obviously the ultimate source for KPI’s, but they require a huge investment in data warehousing and OLAP structures before they can be used for KPI’s. Another Microsoft product called “”Performance Point Server”” is far better suited to be used as a front-end for Cubes. This product also runs on top of Sharepoint and uses web parts to display the data. Used to be called “”Pro-Clarity”” from “”Knosys””.Manually entered – this is not really and option as it requires the users to enter the data manually for each KPI. This defeats the purpose behind the KPI List.
For this illustration we will be using Excel as the source, therefore select “”Excel Workbook”” from the menu. The following dialogue box should open that will allow us to configure the source and settings.

There are a number of options that need to be configured on this screen:Give the KPI a name that is descriptive of the purpose.Provide a more detailed description of the purpose behind this KPI.Optionally provide comments about the source and other relevant information.Browse for the spreadsheet that we created earlier. This will open another dialogue box that will allow you to navigate to the document library and click on the spreadsheet.Select the browse button next to the field called “”Cell Address for Indicator Value””. This will open the following screen.

The top of this screen will create an HTML representation of the spreadsheet we selected earlier. Options at the bottom of the screen will allow us to map the cells in the spreadsheet to the appropriate values required for the KPI. Do the following steps in sequence:Select the cell in the spreadsheet that represents the “”Indicator”” value and then click the button that says  “”Set Indicator””Select the cell in the spreadsheet that represents the “”Goal”” value and then click the button that says  “”Set Goal””Select the cell in the spreadsheet that represents the “”Warning”” value and then click the button that says  “”Set Warning””
After you have completed these steps, click the “”OK”” button to confirm these settings. Close the the KPI dialogue box by confirming “”OK””. Return to the KPI list to see the results.

Play around with the results by opening the Excel spreadsheet and changing the “”Indicator”” value.
I hope this gives you enough of a basic understanding on KPI’s to now go and create your own.


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 )

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