SharePoint KPIs Part 1 - SharePoint KPIs in Excel
Written By: Rahul Mehta -- 1/20/2011 --
(477) comments --
Categories: Design, MOSS 2007
Certain departments of an organization (like the sales department) have frequently changing targets or goals. For instance, in a sales department a sales representative can have different sales target/goals for each week.
Effectively the performance measurement can vary each week. Consider a scenario where the performance measurement threshold values needs to be changed frequently by the business management without depending on IT staff to configure the KPI. In such cases, the KPI threshold values needs to be kept configurable.
In scenarios like one mentioned above, we would prefer to use a SharePoint KPI List which retrieves threshold values from an external source. For
the current situation, we will use an Excel sheet to store the threshold values. We have a sample
Excel sheet which holds some threshold values which is (1) the sales goal value of a product,
(2) the indicator (actual) sales value and (3) a warning value for sales of a product.
Now let us create a KPI from an Excel sheet. Goto KPI List->New and select "Indicator using data in
A page will appear where we are supposed to enter some important information like
Name of the KPI, the Excel Workbook URL location from where we would select
the threshold values (Goal, Indicator and Warning values). As we are taking our threshold values from
Excel sheet, we need to either select an Excel workbook cell (like A1, B1 and so
on) or manually enter a number. In our case, we have selected relative cell values from
an Excel sheet.
After entering the information, press OK and you will see a KPI created in a KPI List with an indicator showing status in Green, Yellow or Red. In our case, we have kept a goal of 1000 where our actual/indicator value is 670, thus results in a red light or rather say below warning level.
Our scenario is to change the threshold values without configuring the KPI. So let us change, the threshold values
in the Excel sheet by replacing actual/indicator value to 870.
Go back to list and check the KPI and you will be able to see the Status change from red to yellow
showing it is in the warning level range.
We can respectively change the goal and warning values too from within the
Excel sheet as we did with the indicator value.
- UUse a KPI List with Excel workbooks to measure different data without changing the KPI in SharePoint
- Use an Excel sheet to change the goal/actual/warning level values to reflect real time data.
- Return to
MSSharepointTips to read about other topics and ideas.
- Check out MSSQLTips.com for great
information about Microsoft SQL Server.