5 Tips to Use SharePoint KPIs to Measure Project Performance

Many project teams spend time talking about project metrics up-front, but then have weak follow-through on actual implementation of the metrics. The one metric tracked within most project offices is the overall status (Green, Yellow, Red), but even then it is often times not supported by factual project performance data. There is data captured throughout the project life cycle that can be presented to provide a better picture of overall project performance.

SharePoint 2010 provides features that enable project teams to expose project data in the form of easy to consume project metrics. The Status List in SharePoint is used to implement project metrics on your project site that are updated as project data is maintained throughout the project life cycle. As with any tool, the hard work lies in defining the metrics and business rules, not adding the metrics to the Status List. This blog provides several tactical tips on how to use the SharePoint Status List to implement project metrics that help measure and improve project performance.

5 Tips to Create Meaningful SharePoint Key Performance Indicators (KPIs)

The following represent several “how to” type tips for creating project metrics within a Status List on your SharePoint 2010 project site.

1. Define what to measure – In many cases the project office will have established standards or guidelines around project metrics utilized to measure project performance. As with all project management best practices you want to select the critical few metrics that will make the biggest impact on project performance. In addition, it is best to focus on metrics that can be produced as a by-product of the project information required to effectively manage the project. I focus on the following best practices areas from a measurement perspective:

  • Schedule Performance – This metric can be derived from the project schedule using earned value, or based upon the schedule variance on key milestones (as a percent of the total duration for the milestone).
  • Cost Performance – This metric is best calculated from the project budget information, either using earned value or based upon budget variance (be careful with budget variances that do not take into consideration some flavor of earned value in the estimate to completion (ETC) calculation).
  • Impact of Change – What impact has approved changes had on the schedule and cost performance of the project? This metric should come straight from the project change control tracking tool.
  • Risks and Issues – This metric is utilized to project the potential impact of high risks and issues on the future performance. This metrics should also be a fairly straight forward extract from the risk and issue tracking tool.
  • Overall Project Status – Some project offices calculate the overall project status (Green, Yellow, or Red) as an aggregate of other project metrics. In most cases, I find it most effective for the project manager to assign the status during the weekly status process as a reflection of the cost and schedule performance, and in the overall health of the project (including trending of the project’s risk profile).

2. Establish the data source – Once you have defined the project metrics, you need to establish the source of the data that will be utilized to update the metric within you SharePoint Status List. The two sources that I utilize are Excel Spreadsheets stored in the document library on your project site (e.g., Cost Performance or Schedule Performance), and a SharePoint List utilized to track specific project information on your project site (e.g., Risks/Issue and Change Control).

  • Excel as the Source – There are a couple steps required to expose data from Excel spreadsheets within the KPI. As previously mentioned the first step is to save the spreadsheet within a document library on your project site. The second step is to utilize the name manager function within Excel to identify the specific cell or range of cells that will be accessed from SharePoint.

Excel’s Name Manager utilized to identify the specific cells to be accessed by SharePoint.


Establish the location and named area when setting up the KPI in the Status List.

  • SharePoint List as the Source – If the information for the KPI is maintained within a SharePoint list, you select the specific list and view (I discuss list views under point #4) from the Site Content displayed.

Select the SharePoint List and View when setting up the KPI in the Status List.

3. Define the parameters – After identifying the source of the data for the KPI, you need to define the parameters that establish the assignment of the metric rating (Green, Yellow, Red). First, establish whether “higher” or “lower than the target value is better for the KPI. Then specify how to determine if the metric is Green, Yellow, or Red.

  • Excel as the Source – The definition of the targets of Green and Yellow are maintained within the Excel Spreadsheet, and you specify these fields (as Named Areas) when setting up the parameters for the KPI. In this example the values in the spreadsheet contain earned value targets for Green (1.0) and Yellow (.90).

  • SharePoint List as the Source – You have options on how to define the KPI from a SharePoint list. It can be based upon a count of the items in the List View, or calculated based a specific field in the List. In the example below the schedule impact was defined based upon the sum of the schedule impact field in the approved change request view of the Change Request Tracking List. In this example the KPI is GREEN if equal to or lower than 10 days total impact, and YELLOW if less than or equal to 20 days (and RED if over 20 days).

4. Create the List Views – When using SharePoint Lists as a source of KPIs in the Status List, you must make sure the List Views are established that support the KPI. This point is best explained with the example below.

  • Impact of Change – If the metric is utilized to communicate the impact of approved change requests on the Schedule, the List View must be filtered to only include changes with “Approved” status. In addition, the view must include any fields that will be utilized to compute the KPI (in this case “Sched Impact” field).

5. Use the Metrics – Now that you have gone to all of the effort to create the project metrics on your project site, make sure you use them to effectively and proactively manage your project. You should understand what caused a KPI to change from GREEN to YELLOW or RED (e.g., a new High Risk, or a slippage in the schedule), and initiate the corrective actions required to move the KPI back to GREEN. In addition, I find it a best practice to include these KPIs in your project status report, as well as corrective actions for YELLOW and RED metrics. Use of the SharePoint Status List to measure and communicate project performance also enables the ability to produce an on-demand / on-line project status report.

Using SharePoint to Measure the Impact of Change (KPI)

As I discussed in my blog post on Managing Change, it is important to understand the cumulative impact of changes on a project with regards to the scope, schedule, and cost/budget. Throughout the life of a project, there will be changes. The project manager should be able to explain the evolution of the plan from the original baseline to the current baseline, including all approved changes that have been implemented.

Effective use of the change control log (see my blog post on Using SharePoint to Manage Change Requests) provides a tool to track and reconcile changes to the baseline schedule and budget. In addition, use of the Project Status list enables use of SharePoint 2010 Key Performance Indicators to measure and communicate the project team’s ability to manage change at any point in time throughout the project life cycle. In order for the project sponsor and steering committee to make good decisions when approving change requests, they should understand the impact of the changes they have already approved. In addition, project change is one of the key metrics utilized to assess project performance in the project closure process (and identify and implement project improvement opportunities for future product releases / projects).

Creating the Approved Change Request view in the SharePoint list

The first step to establishing the project change metrics in SharePoint is to ensure that you have the appropriate view set-up to support the computation of the metric. The cumulative impact of change on the project represents the total impact of the approved change requests (approved and implemented status) on the project schedule and budget. To support this metric, I create a view in the change request list that is filtered to include only approved and implemented change requests (sample of this list view below).

The screen shot below from the Change Request list view settings illustrates the filters utilized to limit the list to only approved and implemented change requests.

In addition, totals are added to the list view to the Approved Change Request list view to display the sum of the schedule impact (days) and budget impact (dollars). I also added a count of the change requests so you can get a quick feel for the number of change requests that are driving the total change impact. The screen shot below from the Change Request list view settings illustrates setting up the totals that are displayed at the top of the list view.

Creating the Schedule Impact Metric

To create the Schedule Impact metric, select NEW indicator from the Project Status list. Within the indicator set-up, you are prompted for a name of the metric, a brief description of the metric, and comments (I use comments to describe the specifics of the source/calculation). In addition, you are prompted to select the list, and list view (described above) where the change request data is maintained. The screen shot below illustrates the first part of the indicator set-up process.


Then you are prompted to define the schedule impact metric:

  • The metric is calculated using the “Sum” of the “Sched Impact” field in the Approved Change Requests list view.
  • The “lower” the metric value is better.
  • The metric is GREEN when the total schedule impact is X days or lower (in this example I used 10 days)
  • The metric is YELLOW when the total schedule impact is Y days and greater that X days (in this example I used between 11 and 20 days)
  • The metric is RED when the total schedule impact is greater than 20 days.

The screen shot bellow illustrates the definition of the schedule impact metric.


Based upon the information set-up for the Change Request Schedule Impact metric, the screen below illustrates the details displayed for this metric. The biggest challenge is setting up consistent criteria from project to project that effectively defines GREEN/YELLOW/RED for the cumulative schedule impact.

Creating the Cost Impact Metric

The same process is utilized (as described for the Schedule Impact metric) to create the NEW indicator, describe the Cost Impact metric, and select the list/view where the change request data is maintained.

Then you are prompted to define the metric:

  • The metric is calculated using the “Sum” of the “Cost Impact” field in the Approved Change Requests list view.
  • The “lower” the metric value is better.
  • The metric is GREEN when the total cost impact is X dollars ($) or lower (in this example I used $10,000)
  • The metric is YELLOW when the total cost impact is Y dollars and greater that X dollars (in this example I used between $10,000-$25,000)
  • The metric is RED when the total cost impact is greater than $25,000.

The screen shot bellow illustrates the definition of the Cost Impact metric.


Based upon the information set-up for the Change Request Cost Impact metric, the screen below illustrates the details displayed for this metric. Generally the definition of GREEN/YELLOW/RED for this metric is documented within the Cost Management Approach of the Project Management Plan.

Using SharePoint to Measure Project Performance (KPI)

SharePoint 2010 is an enabling tool utilized to dramatically improve the project environment. One of the powerful aspects of using SharePoint to improve your project environment is focused on the idea of measuring performance. Many of my previous blogs about using SharePoint to improve project delivery have focused on performing project management best practices. These best practice areas include managing change requests, organizing project deliverables, and managing issues & risks. The information captured in SharePoint while the project team collaborates and maintains project information can be used to communicate up-to-date project performance metrics. The beauty of using SharePoint to communicate project performance is that it is largely created as a by-product of project work performed and maintained on the project site. The project site provides a platform for project stakeholders to get a “snapshot” of key project performance metrics at any point in time.

Creating the Project Status List

The first step to adding performance metrics to your project site is to add a list, using the Project Status list type. The Project Status list is a key performance indicator list that is new to SharePoint 2010. It enables the ability to access data maintain in lists and libraries (Excel spreadsheets and Access databases) to display project performance metrics. Below is an example of a Project Status list on a project site.

For each performance indicator you establish the method of calculating the metric (sum, average, count of values), as well as the definition of Green (goal), Yellow (warning), and Red. Defining what Green, Yellow, and Red means for each key performance metric one time within your Project Office enables deployment of a standard set of performance metrics (pre-configured) within your standard project template.

Adding Project Performance Indicators

The following describes some ideas around the best approach for adding different types of performance metrics to your project status list.

Overall Status: This indicator uses a column captured with the metadata (overall project status) attached to the last status report saved in the project status report document library. This approach is very straightforward to set-up and the easy for project managers to understand and maintain.

Budget Status: In most cases, I maintain an Excel spreadsheet for project budget tracking. If that is the case, then the budget performance metric is set up to access specific cells in the budget tracking spreadsheet. The Green/Yellow/Red status can be defined based upon $ variance, % variance, or an earned value metric (CPI). An alternative approach to the Excel spreadsheet is to add metadata to the Project Status document library to capture the budget status and/or budget variance. This alternate approach is simple to set-up, but a bit redundant in terms of the way budget information is maintained on your project site.

Schedule Status: I generally use the Green/Yellow/Red status maintained within the project milestones list to compute the schedule status (average status of the active milestones). Similar to the budget status, an alternative approach is to add metadata to the Project Status document library to capture the schedule status. Again, this approach is simple to set-up, but a bit redundant in terms of the way schedule information is maintained on your project site. A third approach is to capture an Earned Value metric (SPI) within the budget tracking spreadsheet and link it to the Schedule Status indicator. Note: The KPI feature does not support accessing information directly from MS Project to capture schedule performance metrics (MS Project Server is required to enable better integration of project schedule information with project dashboards).

High Impact Risk and Issues: The risk/issue performance indicator is based upon the number of (count) high probability/impact risks and issues. All you need to decide is based upon the size/complexity of your project what number of high risks/issues defines Green/Yellow/Red.

Change Requests – Schedule Impact: This indicator is based upon the sum of the schedule impact (days) within the Approved Change Request view of the Change Request list (this view includes all change requests that have been approved and implemented). This metric effectively communicates the cumulative impact of project changes on the project schedule. Again, the key to this metric is defining the schedule impact days relating to Green/Yellow/Red (likely defined within the schedule materiality discussion in the Project Management Plan).

Change Requests – Cost Impact: This indicator is based upon the sum of the cost impact ($) within the Approved Change Request view of the Change Request list (this view includes all change requests that have been approved and implemented). This metric effectively communicates the cumulative impact of project changes on the project budget. Cost variance parameters (that tie to Green/Yellow/Red) are generally defined within the Project Management Plan.

In future blog posts I will provide the specifics associated with setting up these different types of project metrics within your project site.

Creating a Summary View

For the purpose of communicating project status metrics to stakeholders it is a best practice to add the summary view of the Project Status Metrics to the main page of your project site. This is accomplished by adding a Project Status Summary web part to the main page. This web part behaves a little differently than other web parts because it is a KPI (Key Performance Indicator) type web part. This web part provides the options to display only the status indicator, or the status indicator and the actual performance values. It also provides the ability to change the design metaphor for the status symbols (default, checkmarks, and stoplight).

Below is an example of the Project Status Summary web part with the default status symbols.

Below is an example of the Project Status Summary web part with the checkmark status symbols.

The user clicks on one of the indicators to display the specifics associated with a metric. Below is an example of the metric details displayed.