Implementing KPIs from SharePoint Lists
Creating KPIs from data in SharePoint lists is an easy way to summarize the status collected in the wealth of valuable data around your organization, and to drive people to update the status of their items. However, the fact that there is a wealth of information in SharePoint can make the indicator hard to define.
When you create a KPI from a SharePoint list, you combine a view of the list with calculations performed on a column or columns. This combines the SharePoint flexibility of creating a view for all the pertinent items with the KPI ability to calculate and compare a value against a goal and display that result to drive action.
Cross-Ref
See Chapter 4 for details on how to create metadata, custom lists, and views that can then be used in a KPI based on SharePoint data.
For example, you can create a KPI that turns red if ten percent or more of active tasks in a Tasks list are past due and not complete. To do this, you create a view for tasks where status does not equal complete. Next, define a KPI based on that list that calculates the percentage of items where the value of the due date column is less than the current date, and the value of the % complete column is not equal to 100. Then proceed to set the success level to be 5% or below and the warning level to be when this percentage is between 6% and 10%. The stop sign would then appear for anything above 10%.
The process for planning a KPI from a SharePoint list has three steps:
1. | Identify the source list and view for your KPI and confirm that it has the right metadata to calculate your value. For example, if you will be counting the number of items in a document library that are classified as “customer reference” type items, you will want a column for content types that have a choice value of “customer reference.” You will then need to create a view that shows only content where content type is equal to customer reference. |
2. | Determine how you will calculate the value for your item. This can be either a formula that calculates a percentage of items that meet a criteria or a calculation of average, sum, minimum, or maximum for a selected column. |
3. | Determine the values for success, warning, and failure. |
Identifying the SharePoint source and view
The SharePoint lists and libraries within your organization contain a wealth of information. Identifying the lists and libraries that you want to use as a source for KPIs involves discovering the ones that can be analyzed to indicate action, inaction, or bottlenecks in your organization. Lists that are the most effective sources for indicators will have enough data so that the analysis provides meaningful results and/or represents a key decision point or bottleneck that needs to be highlighted to drive action.
To help with the visualization of KPIs based on your SharePoint lists, some possible KPIs for the core SharePoint list types are suggested in Table 16.5.
| List Type | Columns Used | Resulting KPI |
|---|---|---|
| Document Library | Status | What percentage of documents are waiting for approval? |
| Tasks | Due date, % complete | What percentage of tasks are not complete and are due today or before? |
| Issue Tracking | Priority, Due Date | What high priority issues need to be resolved today? |
| Issue Tracking | Category, Issue Status | How many active and unresolved issues are in my category? |
| Announcements | Expires | What announcement lists need to be refreshed? |
| Events | Amount of funding request(custom), Funding approval status (custom), Start date | What events are within the next month that have not been approved? |
| Discussion Lists | Replies | What percentage of discussion topics have no replies? |
| Survey | Question about customer satisfaction | What percentage of responses are above your target rating? |
After the list has been identified, choose or create the view that represents the core entries that you want to evaluate and that represent future activity. For example, a KPI on a Tasks list should most likely be focused on all active tasks because closed tasks are not relevant to the goal of driving action. Or, you can use a view to narrow down a particular type of document in a library, such as expense reports or documents whose audience is external customers, if those are the ones that need attention.
Determining the KPI calculation
The KPI calculation for a SharePoint list can be either a value calculation of the number or percentage of items that meet the selected criteria, or a calculation of sum, average, minimum, or maximum of a selected column. The value calculation lets you use text fields and calculations to define your criteria and then calculates the number or percentage based on those fields. For example, if we wanted to know what percentage of active tasks are due but are not complete, we would create a percentage calculation for the active view of a Task lists where the % Complete field is equal to 100 and the Due Date field is equal to or less than today, as shown in Figure 16.7.
Figure 16.7. Calculating a KPI percentage value calculation on a SharePoint list
The KPI entry can also calculate the value based on the sum, average, minimum, or maximum of a particular field on your SharePoint list for all items included in the view. If you define a view of all active tasks, you can then average the % Complete field to calculate a KPI value, as shown in Figure 16.8. This calculation can only be done on number fields.
Tip
The calculation of a value will fail if any of the items do not have a value. To prevent this, you can require that the column contain data and set an appropriate default value for items so that the calculation of the KPI will succeed even if the task owner has not updated that field.
Determining values for goals and warnings
After you have calculated your KPI value, you must determine what values it will be compared against to determine the KPI status of red, yellow, or green. You need to determine whether higher or lower values are better, and the goal levels you set must be consistent with this determination. That is to say if higher values are better, the value will be green if the KPI calculation is higher than the goal and yellow if lower than the goal but higher than the warning.
Determining the goal and warning values for a percentage or average calculation is the most straightforward because the values are fairly predictable—between 0 and 100 for percentages and the average of expected values for the average calculation. The goal is the value for which you want the indicator to show green, the warning is the value for which you want the indicator to show yellow, and red will show for the values that are over/under the yellow value depending on whether better values are higher or lower. For the example of average of the Percent Complete field for a Tasks list, higher values would be better, and we could set the goal value at 85% (green shows when the average percent complete for all active tasks is 85% or higher) and the warning value at 50% (yellow shows when the average percent complete for all active tasks is 50%–84%) as shown in Figure 16.9.
Note
If you are calculating a percentage field, make sure you enter the goal and warning values as numbers less than 1. For example, an 85% goal would be entered as .85 and a 100% goal would be entered as 1. Although SharePoint allows you to fill in the goal field as 85%, it strips off the percentage and then tries to match the calculated field against 85 instead of .85.
Setting values for sum, minimum, and maximum values can be a little more difficult because the numbers can presumably vary widely depending on the allowed values. Setting a goal for a field that will be summed is useful for measuring progress to date on a company initiative or goal. For example, maybe your company has a yearly giving campaign where everyone donates items for an auction and provides an estimated value for that item. The items are tracked in a SharePoint list as shown in Figure 16.10, and the company goal could be to gather $100,000 of donated items. You could create a KPI for the giving campaign, create the value to be a sum of the estimated value column, and set a goal of $100,000 and a warning at $75,000. Anything below will show red so that campaign organizers know that they need to continue to drum up effort.
The minimum and maximum fields help to identify KPI values that are outside the range of expectations. Do you want a KPI that identifies if someone has submitted a customer approval feedback form that has an overall satisfaction rating of 3 or below? In this case, you would create an Indicator that calculates the minimum value for the overall satisfaction rating and set a goal of 4 and a warning level at 3.
To create a KPI based on a SharePoint list, follow these steps:
1. | Navigate to the KPI list to which you would like to add the manual indicator. If you do not already have a KPI list, follow these steps:
|
2. | Select Indicator using data in SharePoint list from the New menu. |
3. | Enter the name of the indicator in the Name field. |
4. | Enter the location of the SharePoint List and View to support the indicator in the SharePoint List and View section. Views can be used to select a subset of the items. |
5. | Select the fields necessary to create the value calculation. The value calculation will be either a number of items that match your criteria, a percentage of items that match your criteria, or a calculation of average, sum, minimum, and maximum for a column.
|
6. | Set the status icon rules. Choose whether better values are higher/lower. If you choose lower, your goal value will be lower than your warning value. Enter the goal (green) and warning (yellow) values. |
7. | Enter the details page in the Details Link section for drilling down on the indicator. If no page is entered, a default details page showing all the properties, including description and comments, appears when the user clicks the item. Ideally, the details link should direct users to the location where they can take action to update the information driving the KPI, such as the list view. |
8. | Enter whether you want the indicator to be recalculated every time it is viewed or when the update value link is clicked in the Update Rules section. |
9. | Select OK. |