Creating a KPI list based on a SharePoint list

SharePoint as a business tool can be utilized for displaying data for the end users. And not just displaying the data, but displaying it in brief so that the users get to know the overall scenario without getting down to the basics. For these reasons, I have always liked SharePoint.
Key Performance Indicator (KPI) is financial and non-financial metrics used to help an organization define and measure progress toward organizational goals. KPIs can be delivered through Business Intelligence techniques to assess the present state of the business and to assist in prescribing a course of action.

Sample KPI list
 
Consider the following scenario:
You have a company where the performance is based on Surveys that the users provide for the product/company. The Company Director would like the survey data targets to be displayed on the home page of a report site on the SharePoint web application. Now, there are four parameters that need to be displayed on the KPI list.Top Area [Overall survey points including all the parameters. The total point are 10 and if the user give a overall survey of equal to or greater than 8 points, then that would be considered under this parameter]Bottom Area [This is opposite of Top Area. Now if the end user give a overall score of equal to or less than 4 points, then that would be considered under this parameter]Product Quality [This is the individual rating the end user gives about the product that they are using. This is also a score out of 10 points. If the user gives a score of equal to or greater than 8 points then that would be counted positive under this category or else for any value less than 8, it would be considered as negative]Engineer Knowledge [This is the individual rating the end user gives based on what they think about the support engineer/executive/customer service agent that they has a talk with. This is also a score out of 10 points. If the user gives a score of equal to or greater than 8 points then that would be counted positive under this category or else for any value less than 8, it would be considered as negative]
 
The above scenario is just an example so that we can develop out KPI list. The scenario may depend on the company requirement.
e.g. A sales company may want to display the sales that are done for different products in different areas. As per the definition of KPI, it is not necessary that we display only financial data. We can display any data where target is in picture. E.g. if a department want to meet 75% of total product sales in a specific region, we can create a KPI list that can show the current status of the sales as per the data that is present in a SharePoint list / database / external source like XML. In this tutorial, we will first continue with using another SharePoint lists as the data source for generating the KPI list.
 
So, now that we have an understanding of the scenario, let us get started with the actual work.I have created a collaboration portal site which by default creates a “Reports” sub site under the top level site. We can even use a team site or a blank site template to start with. But there might be features that are required for a specific web part to function properly. For these reasons, I tend to start off with a publishing site template and then test it with other templates.Browse to the “Reports” site and click on “View All Site Content” in the quick launch bar. Select the Create button in the Actions menu bar.
Create Site Content
Create a list. I am using the Custom List, so that I can have the columns as per the report requirements.
Creating Custom List
Enter the description of the custom list and click on “Create” button.
Survey Data – Custom List Details
Now to add the custom columns, we need to click on “Settings” button in the Actions menu bar of the list and go to “List Settings”.
Survey Data – List Settings
Under the columns section, select “Create Column” and create the following columns in the list.
Survey Data – Columns

The Engineer column is a lookup column that is pulling out data from other list [which contains the engineer details. This concept in SharePoint is called re-usability, where a list can contain general data and that can be used in multiple locations. Thus the end-user does not have to always feed in the data for the individual items. The list automatically pulls out the data that is present and the user just have to select the item from the list.]
 Once we have the above columns created, we will add some items in the “Surveys” list. This data will be used for getting the KPI on out site. After filling in some items, the list should look somewhat like this.
Survey Data – List Items
Now that we have the data filled in the list, we need to create a KPI list in the site. So, once again , we will browse to the “View All Site Content” of the Reports Site and select the “Create” Option. This time, select the “KPI list” under the custom list section and fill in the details to create a KPI list. Now, we have already been through the process of creating a list, so I am keeping it short this time.Once the list is created, it should look like this.
Survey KPI – Blank List
Select, New -> Indicator using data in SharePoint list
Creating a new KPI item
Put the following settings for the KPI item
Name : Top Area
Description :This KPI displays the performance indicator for the top area.
List Url : Select the “Survey Data” list from the list locator. The url should be
/Reports/Lists/Survey Data/AllItems.aspx
         Under the “Value Calculation” section, Keep the option – “Percentage of list items in the view where” selected as we are going to show the data in percentage format on the KPI.
         Select the “Overall” column -> is greater than or equal to -> 8 [Here, “8” is the value which defines the limit. If the Overall score of the survey is greater than “8” then the survey data will be counted under the Top Area parameter.]
 
Moving on to the next section [Status Icon], select the following settings:
         Better values are : greater
         Display when has met or exceeded goal           70
         Display when has met or exceeded warning     65
         Display otherwise
 Leave the other settings as default and click on “OK” button.
 
Now, this was an example for the “Top Area” KPI parameter. Similarly, I have created 3 more KPI items for the bottom area, engineer knowledge and product quality. Now all these items have their own logic as the one above. Depending on the scenario, you can create your own KPI items.
Once the KPI items are created, the KPI list should look like this.
Final Survey KPI list view

Now, you have your KPI list ready and working. The only thing that might be missing is to display the list on your home page of the Report site. For that you need to browse to the home page of the Report Site -> Site Actions -> Edit Page -> Click on “Add a web part” on a zone of your choice.
Add the “Key Performance Indicators” web part on the page.
KPI Web Part
Configure the KPI web part and specify the “Survey KPI” list as the source for the KPI web part and there you are.… KPI on your Report site.

Creating a Key Performance Indicator (KPI) by using Microsoft Office SharePoint Server 2007

A Key Performance Indicator (KPI) is a visual cue that communicates the amount of progress made toward a goal. This article explains how to create KPIs by using Microsoft Office SharePoint Server 2007 KPI lists and how to display KPIs on Web pages.

Key Performance Indicators are valuable for teams, managers, and businesses to evaluate quickly the progress made against measurable goals. By using SharePoint KPIs, you can easily visualize answers to the following questions:What am I ahead or behind on?How far ahead or behind am I?What is the minimum I have completed?

To create and publish KPIs in Office SharePoint Server 2007, you must follow four procedures: create a KPI list, add the KPI to the list, add aWeb Part (Web Part: A modular unit of information that consists of a title bar, a frame, and content. Web Parts are the basic building blocks of a Web Part Page.) to a Web page, and link the KPI list to the Web Part.

Before you begin, you may want to choose one of the following KPI types:KPI typesUsing data in SharePoint lists   When SharePoint lists contain items that you can count, items that are part of a workflow, or items that contain dates, you can use a KPI to track how long the issues or tasks have been open, how many are open, and what percentage of a task is complete. You can also keep track of totals, such as the amount of time that an issue has been open or the total number of sales in a region.Using data in Microsoft Office Excel workbooks   You can set up a KPI in an Excel workbook and link to the KPI from Office SharePoint Server 2007. As the data in the workbook changes, the KPI is automatically updated. You can choose to have the workbook displayed on the same Web page by using the Excel Web Renderer (EWR).Using data from Microsoft SQL Server 2005 Analysis Services   Office SharePoint Server 2007 can use KPIs from Analysis Services, a component of Microsoft SQL Server 2005. A systems administrator or database analyst usually sets up these KPIs and registers the data connection with Office SharePoint Server. Then, anyone with the appropriate permissions can access the database and link to the Analysis Services KPIs.Using manually entered information   In situations where there is no formal system set up or you have a one-time project to track, you can use this KPI and enter the criteria manually. This KPI is useful for displaying information that is communicated in e-mail or some other nonstructured system.

To create a KPI, you first create a KPI list to which you add one or more KPIs. You can create KPI lists in the Reports Library of the Reports Center in a team or organization site, so that other people easily can find them. For more information on the Report Center, seeIntroduction to Business Intelligence features.In the site where you want to create the KPI list, on the default page of the Report Center, click Site Actions, and then select View All Site Content. Under the All Site Content heading, click Create.On the Create page, under Custom Lists, click KPI List.Type a name and an optional description for the KPI list.Click OK.
You now have a KPI list to which you can add one or more KPI types.
 Top of PageAdd a KPI to the KPI listOn the KPI list toolbar, click the arrow next to New.Select one of the following KPI types, depending on where the data for the KPI resides.

Select this optionWhen the source data is
Indicator using data in SharePoint list
A SharePoint list that contains items from which you want to create an aggregate value, such as a sum, minimum, or maximum. Before you set up the KPI, make sure the SharePoint list already is in the view that you want to use. You must first display the appropriate columns in order for the KPI to work.
Indicator using data in Excel workbook
An Excel workbook where the KPI is calculated in the workbook.
Indicator using data in SQL Server 2005 Analysis Services
A SQL Server 2005 Analysis Services cube.
Indicator using manually entered information
Information that is not in a system and therefore entered manually.
On the New Indicator page, do one of the following, depending on the type of KPI you chose in step 2.

For this KPIDo this
Indicator using data in SharePoint list
In the Name and Description boxes, type a name and optional description for the indicator.In the Comments box, type text to help people who are viewing the KPI understand what it represents.Under SharePoint List and View, in the List URL box, enter the URL of the list or library.
 Notes  If you don’t know the URL for the .odc file, click Browse to open the Select a Link dialog box, and then navigate to the .odc file.The SharePoint list or library must be in the samesite collection (site collection: A set of Web sites on a virtual server that have the same owner and share administration settings. Each site collection contains a top-level Web site and can contain one or more subsites.).In View, select the view that contains the items you want to use in the KPI.Under Value Calculation, select one of the following ways to calculate the goal of the KPI:Number of list items   A count of the total number of items in the list.Percentage of list items where   A 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, Maximum, or Minimum of a numerical column in the list.
 Note   The Calucaltion using all list items in the view option is only available if your list includes a numerical field.

Indicator using data in Excel workbook
In the Name and Description boxes, type a name and optional description for the indicator.In the Comments box, type text to help people who are viewing the KPI understand what it represents.In the Workbook URL box, enter the location of the workbook.
 Notes  If you don’t know the workbook URL, click to open the Select a Link dialog box, and then navigate to the file.The URL must be relative to the current site.
In Cell Address for Indicator Value, type the workbook number followed by an exclamation point and then the cell address of the location in the worksheet for the KPI. For example, if the indicator is in cell D15 on worksheet 1, type Sheet1!D15. Otherwise, use a named range.

Indicator using data SQL Server 2005 in Analysis Services
In the Data Connection box, enter the URL where the Microsoft Office data connection (.odc) file is located.
 Note   If you don’t know the URL for the .odc file, click Browse to open the Select a Link dialog box, and then navigate to the .odc file.In the Only display KPIs from display folder box, select the display folder in the Analysis Services database that contains the KPI.In the KPI List box, select the KPI you want, such as Total Revenue or Average Profit Margin.Select the Include child indicators check box to display all of the child indicators for the selected KPI.In the Name and Description boxes, type a name and optional description for the indicator.In the Comments box, type text to help people who are viewing the KPI understand what it represents.

Indicator using manually entered information
In the Name and Description boxes, type a name and optional description for the indicator.In the Comments box, type text to help people who are viewing the KPI understand what it represents.Type the numerical value of your progress so far.

In the Status Icon section, under Status Icon Rules, in the Better values are list, select higher or lower to indicate which range of numbers will be green.Type the values for the status indicators in the boxes. For example, to track the minimum percentage complete for a set of tasks, you can set the green indicator at the goal value and the warning value to be one less than the goal value. In that case, if you want to see when the minimum percentage complete drops below 25 percent, you set the green indicator to 25 and the yellow indicator to 24.
 Note   The Status Icon Rules for an Indicator using data in Analysis Services KPI are preset by the database analyst.
After you add all of the KPIs that you want to the KPI list, you can publish them on a Web page by using one of two KPI Web Parts.
 Publish the KPI on a Web page
After you add one or more KPI types to the KPI list, you can display the KPI list on My Site, a team site, or any other SharePoint page. In addition, the KPI list is available for use by anyone in your organization who has permissions to access it. You can use one or all of the KPIs that appear on the list.
To display the KPIs, you edit the Web page where you want to display the KPI list and add a KPI Web Part. To display the entire KPI list, use the KPI List Web Part. To show only one of the KPIs from the list, use the KPI Details Web Part.Add a Web Part to a pageOn the page where you want to add the KPI, click Site Actions, and then select Edit Page.In the zone in which you want to add the KPI, click Add a Web Part.In the Add Web Parts dialog box, in the All Web Parts section, under Dashboard, do one of the following.To insert the entire KPI list, select Key Performance Indicators.To choose one KPI from the KPI list, select KPI Details.Click Add.
 Link the KPI list to the Web PartIn the Web Part, click Open the tool pane.In the tool pane, under Indicator List, click to navigate to and double-click the KPI list that you created in the previous set of procedures. The list may be in the Report Center of the site.Select one of the KPIs in the list. (If you selected the KPI List, the entire list is displayed.)Click OK.

Creating a KPI list based on a SharePoint list

SharePoint as a business tool can be utilized for displaying data for the end users. And not just displaying the data, but displaying it in brief so that the users get to know the overall scenario without getting down to the basics. For these reasons, I have always liked SharePoint.
Key Performance Indicator (KPI) is financial and non-financial metrics used to help an organization define and measure progress toward organizational goals. KPIs can be delivered through Business Intelligence techniques to assess the present state of the business and to assist in prescribing a course of action.

Sample KPI list
 
Consider the following scenario:
You have a company where the performance is based on Surveys that the users provide for the product/company. The Company Director would like the survey data targets to be displayed on the home page of a report site on the SharePoint web application. Now, there are four parameters that need to be displayed on the KPI list.Top Area [Overall survey points including all the parameters. The total point are 10 and if the user give a overall survey of equal to or greater than 8 points, then that would be considered under this parameter]Bottom Area [This is opposite of Top Area. Now if the end user give a overall score of equal to or less than 4 points, then that would be considered under this parameter]Product Quality [This is the individual rating the end user gives about the product that they are using. This is also a score out of 10 points. If the user gives a score of equal to or greater than 8 points then that would be counted positive under this category or else for any value less than 8, it would be considered as negative]Engineer Knowledge [This is the individual rating the end user gives based on what they think about the support engineer/executive/customer service agent that they has a talk with. This is also a score out of 10 points. If the user gives a score of equal to or greater than 8 points then that would be counted positive under this category or else for any value less than 8, it would be considered as negative]
 
The above scenario is just an example so that we can develop out KPI list. The scenario may depend on the company requirement.
e.g. A sales company may want to display the sales that are done for different products in different areas. As per the definition of KPI, it is not necessary that we display only financial data. We can display any data where target is in picture. E.g. if a department want to meet 75% of total product sales in a specific region, we can create a KPI list that can show the current status of the sales as per the data that is present in a SharePoint list / database / external source like XML. In this tutorial, we will first continue with using another SharePoint lists as the data source for generating the KPI list.
 
So, now that we have an understanding of the scenario, let us get started with the actual work.I have created a collaboration portal site which by default creates a “Reports” sub site under the top level site. We can even use a team site or a blank site template to start with. But there might be features that are required for a specific web part to function properly. For these reasons, I tend to start off with a publishing site template and then test it with other templates.Browse to the “Reports” site and click on “View All Site Content” in the quick launch bar. Select the Create button in the Actions menu bar.
Create Site Content
Create a list. I am using the Custom List, so that I can have the columns as per the report requirements.
Creating Custom List
Enter the description of the custom list and click on “Create” button.
Survey Data – Custom List Details
Now to add the custom columns, we need to click on “Settings” button in the Actions menu bar of the list and go to “List Settings”.
Survey Data – List Settings
Under the columns section, select “Create Column” and create the following columns in the list.
Survey Data – Columns

The Engineer column is a lookup column that is pulling out data from other list [which contains the engineer details. This concept in SharePoint is called re-usability, where a list can contain general data and that can be used in multiple locations. Thus the end-user does not have to always feed in the data for the individual items. The list automatically pulls out the data that is present and the user just have to select the item from the list.]
 Once we have the above columns created, we will add some items in the “Surveys” list. This data will be used for getting the KPI on out site. After filling in some items, the list should look somewhat like this.
Survey Data – List Items
Now that we have the data filled in the list, we need to create a KPI list in the site. So, once again , we will browse to the “View All Site Content” of the Reports Site and select the “Create” Option. This time, select the “KPI list” under the custom list section and fill in the details to create a KPI list. Now, we have already been through the process of creating a list, so I am keeping it short this time.Once the list is created, it should look like this.
Survey KPI – Blank List
Select, New -> Indicator using data in SharePoint list
Creating a new KPI item
Put the following settings for the KPI item
Name : Top Area
Description :This KPI displays the performance indicator for the top area.
List Url : Select the “Survey Data” list from the list locator. The url should be
/Reports/Lists/Survey Data/AllItems.aspx
         Under the “Value Calculation” section, Keep the option – “Percentage of list items in the view where” selected as we are going to show the data in percentage format on the KPI.
         Select the “Overall” column -> is greater than or equal to -> 8 [Here, “8” is the value which defines the limit. If the Overall score of the survey is greater than “8” then the survey data will be counted under the Top Area parameter.]
 
Moving on to the next section [Status Icon], select the following settings:
         Better values are : greater
         Display when has met or exceeded goal           70
         Display when has met or exceeded warning     65
         Display otherwise
 Leave the other settings as default and click on “OK” button.
 
Now, this was an example for the “Top Area” KPI parameter. Similarly, I have created 3 more KPI items for the bottom area, engineer knowledge and product quality. Now all these items have their own logic as the one above. Depending on the scenario, you can create your own KPI items.
Once the KPI items are created, the KPI list should look like this.
Final Survey KPI list view

Now, you have your KPI list ready and working. The only thing that might be missing is to display the list on your home page of the Report site. For that you need to browse to the home page of the Report Site -> Site Actions -> Edit Page -> Click on “Add a web part” on a zone of your choice.
Add the “Key Performance Indicators” web part on the page.
KPI Web Part

Configure the KPI web part and specify the “Survey KPI” list as the source for the KPI web part and there you are.… KPI on your Report site.