solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Creating a SharePoint Key Performance Indicator (KPI)

MSSQLTips author Brady Upton By:   |   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 a KPI to display to your end users

Creating the KPI

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

browse to the page where you want the KPI displayed

Custom Lists > KPI List:

Custom Lists

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

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):

click New, Indicator using data in Sharepoint list

First, give the KPI a name:

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:

the list that has the SQL Server data and choose the View you 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:

I want my results to show all SQL Servers that are compliant

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.

Some values are 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.

you can see that 25% (3 out of 12) are SQL Server 2008 R2 or above indicating the red status symbol
Next Steps


Last Update: 11/5/2012


About the author
MSSQLTips author Brady Upton
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








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
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.