Indicators can also be created for data that is stored in an Excel workbook. In this scenario, Excel is doing all the calculation work, and the indicator is simply getting the value of the indicator from a particular cell that you enter. You can enter the goal and warning values as fixed values in the indicator definition or to be driven by values in the Excel spreadsheet.
Out of all the data stored in Excel workbooks throughout your organization, you will identify the source of a KPI as a cell in a workbook that has meaning and whose location will not change regardless of the growth of the data in the spreadsheet or any possible updates to the spreadsheet from an outside data source. If that seems like a daunting task, think of it this way: The cell is most likely going to be part of a total or summary row and should be the cell that brings all the data in the rows and columns together, such as a sum of Q3 sales for all regions, as shown in Figure 16.11.
Tip
If your spreadsheet will be growing so that the location of the summary cell will change, you can create a summary worksheet that grabs the value of the cell calculated on another page. This way when the details page is updated with more rows, Excel manages the relationship between the details and the summary worksheets and you can grab your cell value from the summary worksheet, as shown in Figure 16.12.
You must designate your Excel storage location as a trusted file location so that the KPI list can access the data provided in the spreadsheet. This is a security mechanism SharePoint provides so that you can enable locations for trusted workbooks without dangerous links or programming to outside data sources. The trusted file location is configured at the Shared Service Provider level. To configure SharePoint to trust the file location of your Excel workbooks, follow these steps:
1. | Open the administration page for your Shared Service provider. |
2. | Select Trusted file locations in the Excel Services Settings section. |
3. | Select Add trusted file location in the top navigation bar. |
4. | Enter the address for your document library, file share, or Web site address in the Address field, as shown in Figure 16.13. Figure 16.13. Entering the address for your trusted file location |
5. | Select whether the location is a SharePoint site, file share, or Web site address using the Location type radio buttons. |
6. | Select whether you would like to trust child libraries or directories by checking the Children trusted box. |
7. | Enter the appropriate session management settings in the Session Management section:
|
8. | Enter the appropriate workbook values in the Workbook Property section:
|
9. | Enter the calculation behavior in the Calculation Behavior section.
|
10. | Enter the external data settings in the External Settings section, as shown in Figure 16.14. Figure 16.14. Configuring the external data settings for your trusted data location
|
11. | Select whether you would like to allow user-defined functions in the Allow User-Defined Functions section. |
You can create a KPI from an Excel worksheet. To do so, follow these steps:
1. | Navigate to the KPI list to which you would like to add the manual indicator. If you do not already have a KPI list, follow these steps:
|
2. | Select Indicator using data in Excel workbook from the New menu. |
3. | Enter the name of the indicator in the Name field. |
4. | Enter the location of the workbook in the workbook URL field. |
5. | Enter the cell location of the indicator value in the Cell Address for Indicator Value field. |
6. | Set the status icon rules. Choose whether better values are higher/lower. If you choose lower, your goal value will be lower than your warning value. Enter the goal (green) and warning (yellow) values. |
7. | Enter the details page in the Details Link section for drilling down on the indicator. If no page is entered, a default details page is displayed when the user clicks. If no page is entered, a default details page showing all the properties, including description and comments, appears when the user clicks the item. Ideally, the details link should direct users to the location where they can take action to update the information driving the KPI, such as the spreadsheet. |
8. | Enter whether you want the indicator to be recalculated every time it is viewed or when the update value link is clicked in the Update Rules section. |
9. | Click OK. |