Microsoft Office Tutorials and References
In Depth Information
Securing the Workbooks on the Server
Excel Services does not support the opening of workbooks with any of the security and protection
features discussed here.
One of the main advantages of using the server in conjunction with the View Item right is to restrict
who is permitted to view the workbook using the SharePoint security model. Only one version of
the workbook is kept and managed centrally on the server, instead of having a number of versions
distributed through e-mail or other means.
After a workbook is downloaded to the client, it is out of the server security model. Users who have
only the View Item right are not permitted to download the file to the client.
Chapter 8 discusses the Excel Services security model in detail.
External Data Queries
Excel Services supports the use of PivotTables or Online Analytical Processing (OLAP) formulas for
querying data from external databases. It does not support some types of PivotTables or other ways to
query data (such as query tables, SharePoint lists, and links to other workbooks).
In most cases, you can work around the unsupported features by using PivotTables or user defined
functions (UDFs). This section explains the limitations and workarounds for these features.
Most types of PivotTables are supported on the server, including the following:
OLAP — PivotTables querying data from Analysis Services 8.0 or 9.0. Other OLAP providers
that implement the same protocol could work, but are not formally supported.
Relational — PivotTables querying data from a relational database, through the Object Linking
and Embedding Database (OLEDB) or Open Database Connectivity (ODBC) protocols. These
include SQL Server, Oracle, and DB/2, among others.
Excel list or range — PivotTables querying data from a range in the current workbook.
For the PivotTable to work, the driver must be installed on the ECS machines. Chapter 5 discusses
external data and supported data providers.
Some types of PivotTables are not supported by Excel Services, including the following:
Multiple consolidation ranges
Ranges from other workbooks
Server-based page fields
PivotTables based on multiple consolidation ranges allow you to create one report based on several
data ranges from multiple Excel sheets. This feature is not supported on the server. The workaround