Creating a SharePoint Key Performance Indicator (KPI)

By: Brady Upton   |   Read Comments   |   Related Tips: > Sharepoint

Problem

In a previous tip I explained how to insert SQL Server data into a Sharepoint list using a SSIS package. This tip will focus on creating a KPI in SharePoint to display to your end users.

Solution

A KPI (Key Performance Indicator) is a graphical representation that displays progress against a predefined measure or business goal. KPIs make it easier for end users to evaluate the amount of progress without reading a bunch of data.

We will use the sample data (see below) I used in the previous tip to create our KPI (this data is static since we imported it into Sharepoint as a separate list, so it will not report real time data)

Let’s say, for example, our DBA Manager wants to upgrade all the SQL Servers to at least SQL Server 2008 R2 and wants to know how many SQL Servers are not compliant. This is a good scenario for using a KPI to display this data.

---

Creating the KPI

In Sharepoint, browse to the page where you want the KPI displayed and go to Site Actions > Create:

Custom Lists > KPI List:

For this example, I’ll create a list called SQL Versions KPI:

Once, the KPI list is created you should see a blank list. To add a KPI, click New > Indicator using data in Sharepoint list (you can also use other data sources for KPI’s):

First, give the KPI a name:

Next, point the KPI to the URL where you created the list that has the SQL Server data and choose the View that was created:

For value calculation, select one of the following:

• Number of list of items in the view - Count of number of entries in the view selected
• Percentage of list items in the view where - Calculation that compares the value of a content type within a column or up to five columns in the list
• Calculation using all list items in the view – A computation of Total, Average, Minimum, or Maximum of a numerical column in the list

For this example, I want to show the percentage of non-compliant servers, so I’ll choose Percentage of list items in the view where…

Also, I want my results to show all SQL Servers that are compliant (SQL Server 2008 R2 or above), so I’ll add the following into the where dropdowns:

Status Icons determine which icon to be used to represent the status of the indicator. For this example, my DBA manager wants 100% of my servers upgraded, so I’ll select 100 for my green indicator meaning that my goal has been met. I’ll choose 60% for my midpoint and everything else will be red. Note: some values will be better higher or lower depending on what you are measuring.

---

Additional Options 

Two other options that are available are:

Details Link

Select the custom page that contains detailed information about this indicator. If no custom page is selected a default details page will be displayed when a user clicks on the indicator in the status indicator list web part.

Update Rules

Recalculate the indicator value for every viewer or manually update the value of this indicator with the Update Values link on the status indicator or on the status indicator list web part.

---

Viewing the KPI

After creating the KPI, you can see that 25% (3 out of 12) are SQL Server 2008 R2 or above indicating the red status symbol.

Next Steps

• Review these related tips:
  • Using SSIS to Automatically Populate a SharePoint List
  • Accessing SharePoint Lists with SQL Server Integration Services SSIS 2005
  • How To Call a SharePoint Web Service from a SQL Server CLR Function

Last Update: 11/5/2012

About the author

Brady currently serves as a SQL Server DBA in Honolulu, HI.

View all my tips

We Recommend

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. SQL Monitor: prioritize your SQL Server workload with easy-to-use performance monitoring Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check. Secure column & whole database on all versions and editions of SQL Server with NetLib’s TDE Demystify TempDB Performance and Manageability