Microsoft SharePoint 2007 Technologies

8.2 Key Performance Indicators (KPIs) 169

Chapter 8

lished sheet1, we can still control cells that live in it via parameters.

Parameters are entered via a workpane that appears on the right-hand side of

Excel Web Access (both the full browser and Web Part versions). You will

note that we have also added the net scores to our chart.

As you should hopefully now agree, Excel Services is a very powerful

way of surfacing business data and the business logic that turns it into some-

thing useful. Business intelligence that has traditionally been locked away can

now be used by the masses for many different purposes!

Let’s now turn our attention to key performance indicators.

8.2 Key Performance Indicators (KPIs)

KPIs can be created in many applications, such as SQL Server and Business

Scorecard Manager, and SharePoint Server 2007 also allows you to define

KPIs and then display them in appropriate places. Again, this is all part of

“BI for the masses,” as the data that SharePoint KPIs use to make decisions

can come from many different sources, even an individual cell in a workbook

published via Excel Services. KPIs in SharePoint Server 2007 are very generic

and flexible, and can be used for many different purposes. A KPI is generally

used to give an aggregated view of data and to visually indicate how that data

is performing against some goal. For example, you might want a KPI that

warns you visually when the number of unanswered support calls goes over a

certain threshold.

Figure 8.4

Parameters.

170 8.2 Key Performance Indicators (KPIs)

8.2.1 Creating SharePoint KPIs

The first step to creating a KPI is to create a KPI list in a SharePoint Server

2007 site. This is the list that will ultimately be used when displaying KPIs

through Web Parts. This list supports multiple content types, and there are

four types of KPIs that you can create. Each type gathers its data for calculat-

ing the performance of the indicator from different sources: data in a Share-

Point list, manually entered data, data in an Excel workbook, and data in

SQL Server 2005 Analysis Services.

Ultimately, each KPI is looking to gather a value, compare that value to

some thresholds, and then display an icon (by default colored red, to yellow

or green) to indicate the health of that value against those thresholds. Addi-

tionally, you can associate a details link with each KPI to display extra infor-

mation, and this link could be to any aspx page you like. Thus, a very simple

example with a manually entered KPI might be to display a red icon if the

value is less than 50, yellow if it is between 50 and 75 and green if it is greater

than 75.

Creating a KPI from data held in a SharePoint list gives you lots of flexi-

bility in configuring what it is you are monitoring. For example, it could be

as simple as the actual number of items in the list, the percentage of items in

the list that have a particular column value greater than a particular number,

or a calculation such as average, minimum, or maximum score. In our golf

scenario, maybe we want to monitor the percentage of scores that are below

par so we could do this with the net golf scores being entered into a custom

SharePoint list. Although the UI does let you enter lists that are outside the

current site collection, you will get an error when it tries to calculate the KPI;

therefore, you are limited to gathering your data from a list within the cur-

rent site collection.

Creating a KPI from an Excel workbook involves first specifying the

URL to the workbook in your SharePoint library. You can then browse this

workbook, and Excel Web Access allows you to navigate any of the sheets or

named items that have been published from the workbook. From here, you

can choose the cells that are to be used for the indicator value, indicator goal,

and indicator warning. Thus you can use all the power of Excel to perform

your calculations and then report on the performance of a particular aspect

via a SharePoint KPI. Indeed, as a consumer of the Excel workbook, you

needn’t even concern yourself about the business logic behind the calcula-

tions—all you care about is the end result and how it is performing against

certain goals. This is a very powerful feature, and its uses are effectively end-

less given that you have all the power of Excel to do your calculations.

Lastly, creating a KPI from data held in SQL Server 2005 Analysis Ser-

vices involves specifying a data connection to the SQL Server. Once this is