Sharepoint | SQL Freelancer Blog

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

Click here to view the rest of this post.

In my opinion, the best (and easiest) way to accomplish this goal is using SSIS packages and a component from Codeplex called Sharepoint List Source and Destination. Ray Barley wrote a tip regarding this component a while back and he explained how to extract data from Sharepoint. I encourage everyone to check out his tip as it explains how to install the component and has some very good tips regarding the Sharepoint Source task. In this post, we’ll go over the Destination task in more detail.

Setup a Sharepoint List

For this tip, I’m just going to set up a quick custom list. This example is done in Sharepoint 2007, but Sharepoint 2010 should be close to the same.

Go to Site Actions, Create:

Custom Lists, Custom List:

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

Once I have a list created, I need to create a view with custom columns that match my SQL query. For this example I need Server Name, Instance Name, and Build. To create a view click Settings, Create View:

Choose Standard View:

Name the View (for this example, I’ll name it SQLVersionView and make it my default view):

Next we’ll need to create custom columns. Go to Settings, List Settings: