Share Excel 2007 workbooks as interactive reports

Note: This article refers to an example SharePoint site created by Adventure Works, a fictitious company that manufactures bicycles, bicycle components, and bicycling accessories.

Every quarter, the sales and accounting teams at Adventure Works create financial reports in Microsoft Office Excel 2007. The files must be protected from unwanted changes so that users see accurate data, or one version of the truth.

To ensure one version of the truth, Adventure Works publishes its Office Excel 2007 workbooks as interactive reports on Microsoft Office SharePoint Server 2007. From there, employees can filter, sort, and run calculations on the information without the risk of unwanted changes to the source data.

To share your Office Excel 2007 workbooks as interactive reports, your organization must run Excel Services, a standard component of Office SharePoint Server 2007. The following sections explore Excel Services and describe some of the ways you can use reports in Office SharePoint Server 2007.

What is Excel Services?

Excel Services consists of three basic components:

• Excel Calculation Services, the engine that loads workbooks, runs calculations, and refreshes data.

• Microsoft Office Excel Web Access, a Web Part that displays your workbooks and enables you to interact with them.

• Excel Web Services, a tool for developers who want to write custom programs.

Your server administrator must enable and configure Excel Services before you can use it.

Adventure Works uses Excel Services and a Report Center site to host its reports. The following sections explore some of the ways you can use Excel Calculation Services, Office Excel Web Access, and Office Excel 2007 to store workbooks in a Report Center site and share them as interactive reports.

Create reports

To create reports, you first create an Excel workbook, and then publish the workbook to Excel Services. From there, Office Excel Web Access creates an HTML version of the report, and you view that file in your browser. You must publish the whole workbook, but you can define the parts of the workbook that users see, such as individual sheet tabs or named ranges of data. By displaying only specific parts of the workbook and protecting the whole workbook from unauthorized access, you can keep data confidential and enable authorized users to refresh, recalculate, and interact with the viewable data.

You can also define parameters, cell values that users can enter from their browsers. For example, users can enter parameters as part of creating a what-if analysis.

As you proceed, remember that workbooks published to Office Excel Web Access have only the features that you enable. For example, if users need to filter numbers or dates, you must apply an AutoFilter when you save the workbook. If users need to filter text, you must apply a filter.

View workbooks in your browser

One of the biggest advantages to publishing workbooks as interactive reports is the ability to view them in your browser. When you use Office Excel Web Access, you don't need a local copy of Office Excel 2007. For example, the Sales department at Adventure Works can view current sales data at any time, without having to check out the file:

While you can't edit the workbook data in Office Excel Web Access, you can perform other actions such as the following:

• Recalculate the data in the workbook.

• Refresh data from external sources.

• Navigate among the worksheets, named ranges, and other items in the workbook.

• Sort and filter data.

• Expand or collapse levels of data and use report filters in PivotTable reports.

• Obtain different results or views by selecting data from another Web Part, such as a Filter Web Part.

• Temporarily change values for what-if analyses, and use tools such as Goal Seek and Solver.

Work with the data in Excel

The Sales team at Adventure Works often needs to perform calculations that aren't available in an interactive report. To do those additional tasks, they can open their reports directly in Office Excel 2007.

Note: The Open in Excel command is only available if you have the correct permissions. If you can't use the command, you can open a snapshot of the data in Office Excel 2007.

After they open the report, they can use the full range of functions, filters, and other analytical tools that Office Excel 2007 provides. Reports opened in Office Excel 2007 contain the values, formulas, and other data in the current source file. Again, users can open workbooks in Excel without having to check them out or worry about accidentally changing the source file.

Use snapshots to analyze your data

By design, only certain employees at Adventure Works can open a report directly in Office Excel 2007. If you don't have permissions to open reports, you can create a snapshot.

A snapshot is a limited version of a workbook that contains only viewable information such as data values and text formatting. You can't perform calculations on snapshots, but you can use other tools such as filters. The following sections describe what is saved and what is removed when you create a snapshot.

Data saved in a snapshot

• All cell formatting, styles, and themes.

• All cells, columns, rows, and worksheets that are currently displayed.

• The current text, number, and date values in cells, and values returned by formulas and functions.

• Excel tables, charts, PivotTable reports, and PivotChart reports.

Data removed by a snapshot

• Privacy information that might be in the workbook and that you might not know about, such as user names or personal summary information in the document properties.

• All conditional formatting.

• Hidden cells, rows, columns, and worksheets.

• All Excel worksheet formulas and functions.

• Any interactive features, such as expanding and collapsing levels of data in a PivotTable report, filtering and sorting Excel tables and worksheets, and grouping and ungrouping outlined data.

• All connections to external data sources and cached data, including Web-related hyperlinks and Web publishing options.