Microsoft SharePoint Products and Technologies Administrators Pocket Consultant

ECS is designed with control and security in mind. Therefore, to allow workbooks to be processed by ECS, you must explicitly designate which document libraries or external locations are allowed. Doing so minimizes the impact of workbook processing on your servers and avoids inadvertently exposing information that should not be published through Excel Web Services. A Trusted File Location can be either a SharePoint 2007 Server document library, a shared folder on a file server, or a Web site on a standard web server. The Report Center site template is designed to provide a single site for deploying consolidated pages displaying figures, charts, graphs, and performance indicators that can help analysts and decision makers perform their jobs. The Report Center includes a Report Library list, which includes pre-configured content types and for Office Excel 2007 files (Reports) and Web Part pages (Dashboards) for displaying data from Excel Calculation Services.

Creating a Trusted File Location

The settings defined on the Trusted File Location control the security, caching, and data access behavior for each location. Use the steps below to configure a Trusted File Location for each document library that will be used to store workbooks accessible through Excel Calculation Services. You can also allow access to workbooks stored in file shares and non-SharePoint Web sites by configuring Trusted File Locations for these locations as well.

1. Open Central Administration from the Start menu.

2. In the Shared Services Administration section on the left, click your Shared Services Provider.

3. In the Excel Services Settings section, click Trusted File Locations.

4. Click Add Trusted File Location.

5. On the Add Trusted File Location page, type the URL or UNC of the file location and select other options as described below.

6. Click OK to add the Trusted File Location.

Trusted Location Settings

This section describes each of the settings that are shown on the Add Trusted File Location page indicated in the previous set of steps.

Location

• Address and Location Type The value you enter in the Address field depends on the option selected for Location Type.

  • Windows SharePoint Services: Designates a specific document library in a site as the trusted location: for example, http://spserver/records/excellibrary.

  • UNC: Enters the Universal Naming Convention (UNC) path to a shared folder on a file server: for example, \\fileserver\excelfiles.

  • HTTP: Provides the URL to a Web site that hosts Office Excel 2007 workbooks: for example, http://webserver/directory.

• Trust Children Check this box to allow Excel Calculation Services to trust subdirectories and child folders in the trusted location.

• Description Use this field to document the functional purpose of the trusted location and the reason it is being trusted. The description is not displayed elsewhere.

Session Management

• Session Timeout This is the length of time that a session can sit idle without any user interaction before it is closed down by the browser. Each open session counts toward the Maximum Sessions Per User limit defined globally for Excel Calculation Services (see the section "Configuring Excel Services Settings" later in this chapter).

• Short Session Timeout If a user opens a site, but does not take any action with the workbook or chart displayed in the browser, then the session is considered a Short Session and will time out according to this value.

• Maximum Request Duration This is the maximum number of seconds that Excel Calculation Services will use in attempting to process and display data from an Excel 2007 workbook. The default of 300 seconds (5 minutes) is designed to prevent Excel Calculation Services from being tied up processing overly large workbooks. To correct timeout errors, either increase the value of this setting or provide more processing power on the server.

Workbook Properties

• Maximum Workbook Size This value represents the largest size for a workbook that Excel Calculation Services will process. Users are able to upload workbooks that exceed this size to the trusted location, but cannot display the workbook data in the Web View.

• Maximum Chart Size This value represents the largest size for a chart that Excel Calculation Services will process.

Calculation Behavior

• Volatile Function Cache Lifetime This value defines for how long a dynamic function's results are cached. Many functions within workbooks change infrequently. Enter -1 to have the value calculated once when the workbook loads, 0 to force it to be re-calculated every time the function is called, or a value in seconds from 1 through 2,073,600 (24 days) to set a specific cache duration.

• Workbook Calculation Mode The calculation mode determines the mechanism that Excel Calculation Services uses to trigger data recalculations in workbooks. These settings, except for the File setting, override the workbook settings. The options are File, Manual, Automatic, and Automatic Except Data Tables.

External Data

• Allow External Data This setting controls what type of Office Data Connection (ODC) sources are permitted in workbooks by Excel Calculation Services. The setting of None prohibits all ODCs; the other settings permit either all ODCs or only those stored in Trusted Data Connection Libraries.

• Warn On Refresh Check this box to warn users before refreshing Office Data Connections.

• Stop When Refresh On Open Fails Check this box to stop the loading and display of a workbook if the Office Data Connection cannot be refreshed.

• External Data Cache Lifetime This setting determines the lifetime of data derived from an Office Data Connection when the connection is refreshed either automatically or manually. The default setting is 300 seconds (5 minutes) for each type of connection.

• Maximum Concurrent Queries Per Session This setting limits the number of Office Data Connection queries that a single workbook session can contain.

User-Defined Functions

• Allow User-Defined Functions Check this box to allow workbooks to reference installed dynamic-link libraries (DLLs) that provide User-Defined Functions.