Implementing KPIs from Excel

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.

Identifying your Excel KPI source

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.

Figure 16.11. Sales Summary Data


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.

Figure 16.12. Using a Summary Worksheet to define a static cell location



Configuring a trusted source for your Excel storage location

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

[View full size image]

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:

  • Session Timeout: Values between –1 and 2073600 seconds (no timeout – 24 days). This is the maximum time that an Excel Calculation session can stay active as measured from the end of the request. If you enter a value of 0, the session stays active only for that particular request.

  • Short Session Timeout: Values between –1 and 2073600 seconds (no timeout – 24 days). This is the maximum time that an Excel Web Access session can stay open but inactive as measured from the start of the open request. If you enter a value of 0, the session stays active only for that particular request.

  • Maximum Request Duration: Values are –1 (no limit) and between 1 and 2073600 seconds (1 second – 24 days). This defines the maximum duration of a single request in a session.

8.
Enter the appropriate workbook values in the Workbook Property section:

  • Maximum Workbook Size: Values are 1 to 2000MB. This is the maximum size of the workbook that can be opened by Excel Calculation Services.

  • Maximum Chart Size: Any positive integer is a valid value. This is the maximum size of the chart that can be opened by Excel Calculation Services.

9.
Enter the calculation behavior in the Calculation Behavior section.

  • Volatile Function Cache Lifetime: Valid values are –1 (calculated once per session) and 0 (function is always calculated) and 1 to 2073600 seconds (1 second to 24 days). These values define the maximum time in seconds that a manual or automatic calculation will be cached.

  • Workbook Calculation Mode: File, manual, automatic, or automatic except data tables. This setting defines the calculation mode of Excel Calculation Services and overrides the workbook settings unless you select “file.”

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

[View full size image]

  • Allow External Data: None, trusted data connection libraries only, and trusted data connection libraries and embedded.

  • Warn on Refresh: Select this option if you want to display a warning before refreshing the data from an external location.

  • Stop When Refresh on Open Fails: Selecting this option stops the open operation on an external file if the file contains a Refresh On Open data connection, the file cannot be refreshed when it is opening, and the user does not have open rights to the file.

  • External Data Cache Lifetime: Valid values are –1 (never refresh after first query) and 0 to 2073600 seconds (0 seconds to 24 days). These values define the maximum time in seconds that the system can use cached manual or automatic external data query results.

  • Maximum Concurrent Queries Per Session: Any positive integer is a valid value. This defines the maximum number of concurrent queries that can be executed during a single session.

11.
Select whether you would like to allow user-defined functions in the Allow User-Defined Functions section.

Creating your KPI from an Excel worksheet

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:

a. Open the site that you want to host your KPI list and click View All Site Content in the left navigation.

b. Click Create in the top navigation bar.

c. Click KPI list from the Custom Lists section.

d. Enter the Title and Description for your KPI list and click Create.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.220.66.151