Ceptara

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:

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.