Performance Management using SharePoint (2003)

For those of you that have had the chance to see what’s new in SharePoint 2007, you will notice the addition of KPIs (Key Performance Indicators). This is a wonderful addition and will add the ability for business users to drive metrics at a new level. But what about all of those SP2003 installs? Sans the upgrade, is there anything one can do to add scorecards?

The answer is yes, if you’re comfortable working in FrontPage and SharePoint…

Step 1) Establish a list with your metrics, here’s what I’ve used. Set up a list with these columns:

Title: The short name for the metric

Date: The date the metric is taken

Actual: to hold the actual metric result

Target: to hold the target for the metric

Warning: if you want to have three statuses (Red, Yellow, Green) versus two

Status_Math: so you know if higher is good or lower is good

Status: Calculated column that compares Actual to Target and renders a status – see the formula below

Symbol: A calculated text field that will hold a URL to your symbol picture, e.g. a green, yellow or red dot.

You can also add other types of columns, e.g. Category (Financial, Customer, Operations, L&G), Scorecard (so that you can generate multiple scorecards), etc.

Status Formula:

=IF(OR([Actual]="",[Target]=""),"",IF([Status_Math]>0,IF([Actual]>=[Target],"Green",IF(AND([Actual]>=[Warning],[Actual]<[Target]),"Yellow","Red")),if([actual]<=[target],"Green",if(and([actual]>[Target],[Actual]<=[Warning]),"Yellow","Red"))))

Symbol Formula:

=IF(Status="Green","http://SPDomain/sites/mysite/Images/green.gif",IF(Status="Red"," http://SPDomain/sites/mysite/Images/red.gif",IF(Status="Yellow"," http://SPDomain/sites/mysite/Images/yellow.gif"," http://SPDomain/sites/mysite/Images/dash.jpg")))

Step 2) Add your data to the List. For example, if you measuring “IT Service Availability (% uptime)”:

Title, Date, Actual, Target, Warning, Status_Math
IT Service Availability (% uptime), 2/15/2008, 99.93, 99.9, 99.85, 1
IT Service Availability (% uptime), 2/22/2008, 99.82, 99.9, 99.85, 1

Step 3) Set up a page to view the metrics – you can do this thru ‘Create – Web Page – Basic Page”. This will set up a page the matches the style of your current SharePoint site.

Step 4) Browse to your new page and then open it in FrontPage 2003. This is important because FrontPage has a feature to change the format of a column, i.e. ‘Format Item as…”.

Step 5) Create a table on your basic page and insert a data view (Insert – Database – Data View), select the list you used to store your metric data, and select ‘Show Data’, this will allow you to pick the columns you want in your data view. You can select multiple columns by ‘Ctrl-Clicking’. Once you’ve selected your columns, click on ‘Insert Data View’. This will create a data view in one of your table cells. Right-mouse click on the ‘Symbol’ column, and select ‘Format Item As…’, select picture. This should reformat the ‘Symbol’ column from text URLs to pictures that correspond to the images you have stored in your image library.

You can set up Filtering (e.g. filter on a specific date to get only the KPIs for that date), Sorting, Grouping, and change the Style of the table.

You will get something like this:



This is something that can be done fairly quickly and can scale to a medium level. If you want to introduce more complex tracking, e.g. automatic metric capture from a SQL database, or perform math on multiple rows in your data table, this method starts to fall over.

Upcoming Talk on Purchasing & Quality Practices

Just wanted to remind everyone that I have a talk coming up next week, February 19th, at the AmCon conference in Bellevue Washington - it's FREE, so don't pass up this opportunity to learn more about "Six Sigma in Purchasing" - more here including the presentation material. The talk focuses on presenting a number of Supplier Management practices and describing the quality tools that can be used to execute supplier management better.

When we talk about supplier performance management, a concept called SVA will be introduced - the slides in the presentation are taken from this model. Feel free to download the model to get familiar with the math and the details of the topics. In addition to the SVA model, there also is a model available (Supplier Performance Model) focused on showing how to gather, analyze, and present quantitative and qualitative data in support of managing supplier performance.

I will be demonstrating how to use perceptual maps, correlation matrices, qualitative and quantitative measures, and many more tools, so please feel free to join us.