In This Chapter

Understanding SharePoint and Excel Services

Publishing an Excel workbook to SharePoint

Using the Power Pivot Gallery

Refreshing Power Pivot data connections

By publishing your Excel reports and dashboards to SharePoint, you can make them available to others in your organization via a browser and prevent multiple users from having separate versions of your workbooks on their computers. You can also make your Power Pivot reports easier to find, share, and use.

In this chapter, you first discover how SharePoint helps organizations share and collaborate on data. You will then explore the options for publishing your Power Pivot reports to SharePoint.

Understanding SharePoint

SharePoint is Microsoft’s premier collaborative server environment, providing tools for sharing documents and data across various organizations within a company’s network.

Typically deployed on a company’s network as a series of intranet sites, SharePoint lets various departments control their own security, workgroups, documents, and data. As with any other website, a SharePoint site — or an individual page within the site — is accessible by way of a URL that the user can access using a standard web browser.

SharePoint is most often used for the storing of version-controlled documents, such as Word documents and Excel worksheets. In many environments, email is used for passing documents back and forth between users. The potential for mixing up different versions of the same document is considerable. Also, storing multiple copies of the same document takes up a lot of disk space. Because SharePoint provides a single source for storing, viewing, and updating documents, many of these issues are eliminated.

And because SharePoint easily handles virtually any type of document, it is frequently used to consolidate and store various types of documentation (project drawings, videos, schematics, photographs, and workbooks, for example) that are required for large projects where multiple teams must collaborate.

Microsoft chose SharePoint as the platform for Excel publishing because of the significant features built into SharePoint, including these:

• Security: SharePoint supports users and groups of users. Users and groups may be granted or denied access to various parts of a SharePoint website, and designated users may be granted permission to add, delete, or modify the site.
• Versioning: SharePoint automatically maintains a version history of objects and data. Changes can be rolled back to an earlier state at virtually any time. The ability to roll back changes can be granted to individual users, and DBA support is not required.
• Recycle bin: Deleted data and objects are held in a “recycle bin” so that they can be recovered, if necessary. SharePoint supports an Undo feature for its data.
• Alerts: Users and groups can be alerted by email message whenever a specific document in SharePoint is added, deleted, or changed. When granted the proper permissions, users can manage their own alerts.
• End-user maintenance: SharePoint sites are meant to be maintained by their users, without the intervention of IT departments. Although SharePoint pages are not as flexible as typical web pages, a SharePoint developer can add or remove features from pages; change fonts, headings, colors, and other attributes of pages; create subsites and lists; and perform many other maintenance and enhancement tasks.
• Other features: Every SharePoint site includes a number of features, such as a calendar, a task list, and announcements that users may turn off or remove.

Most IT organizations have already implemented a SharePoint environment, so your organization likely already has SharePoint running on its network. No lone user can simply start up a SharePoint site. If you’re interested in using SharePoint, contact your IT department to inquire about getting access to a SharePoint site.

Understanding Excel Services for SharePoint

The mechanism that allows for the publishing of Excel documents to SharePoint as interactive web pages is Excel Services.

Excel Services is a broader term to describe these three components:

• Excel Calculation Services: Serves as the primary engine of Excel services. This component loads Excel documents, runs calculations on the Excel sheet, and runs the refresh process for any embedded data connection.
• Excel Web Access: Allows users to interact with Excel through a web browser.
• Excel Web Services: Hosted in SharePoint Services, it provides developers with an application programming interface (API) to build custom applications based on the Excel workbook.

When you publish a workbook to Excel Services, your audience can interact with your Excel file in several ways:

• View workbooks that contain a data model and Power View reports.
• Navigate between worksheets.
• Sort and filter data.
• Work with pivot tables.
• Use slicers and pivot table report filters.
• Refresh data for embedded data connections.

Publishing an Excel Workbook to SharePoint

To take advantage of the functionality afforded by Excel Services, you must have the proper permissions to publish to a SharePoint site that is running Excel Services. To obtain access, speak with your IT department.

After you have access to publish to SharePoint, follow these steps:

1. Click the File tab on the Excel Ribbon, choose Save As ⇒ Other Web Locations, and then click the Browse button.

   This step opens the Save As dialog box.

2. Enter the URL of your SharePoint site in the File Name input box (see Figure 7-1).

3. Click the Browser View Options button.

   The Browser View Options dialog box opens.

4. Select which parts of the workbook will be available on the web, as shown in Figure 7-2, and then click the OK button.

   You can choose to show the entire workbook, only certain sheets, or only specific objects (charts, and pivot tables, for example). You can also define parameters to allow certain named ranges to be editable in the web browser.

5. Click the Save button to connect to the SharePoint site and see your document library, as shown in Figure 7-3.

   You can think of a document library as a directory on the SharePoint site.

6. Enter a filename in the File Name input box, double-click the library where you want the file saved, and then click the Save button.

After you’ve published the workbook, you can view it on the web by finding the document in the appropriate library on your SharePoint site. When you open the workbook, it shows up in the browser, with several menu options (see Figure 7-4), as described in the following list:

• Edit Workbook: Either download the workbook or edit the workbook in the browser.
• Share: Email a link to your newly published workbook.
• Data: Refresh any external data connections that are in your workbook.

Workbooks on the web are running in an environment that is quite different from the Excel client application you have on your PC. Excel Services has limitations on the features it can render in the web browser. Some limitations exist because of security issues, and others exist simply because Excel Services hasn’t yet evolved to include the broad set of features that come supplied with standard Excel.

In any case, the following list describes some limitations on Excel Services:

• Data validation does not work on the web. This feature is simply ignored when you publish your workbook to the web.
• No form of VBA, including a macro, runs in the Excel Web App. Your VBA procedures simply don’t transfer with the workbook.
• Worksheet protection doesn’t work on the web. Instead, you need to plan for, and use, the Browser View Options dialog box (refer to Figure 7-2).
• Links to external workbooks no longer work after publishing to the web. Any links or references to other workbooks will no longer work after you publish your file to SharePoint.
• You can use any pivot tables with full fidelity on the web, but you cannot create any new pivot tables while your workbook is on the web. Create any pivot tables in the Excel client on your PC before publishing on the web.
• OfficeArt doesn’t render on the web. This includes Shape objects, WordArt, SmartArt, diagrams, signature lines, and ink annotations.

Publishing to a Power Pivot Gallery

A Power Pivot Gallery is a type of document library that showcases Power Pivot reports and allows for scheduled refresh cycles.

Exploring the Power Pivot Gallery

For your end users, the Power Pivot Gallery provides an attractive portal that serves as a one-stop shop for all the reports and dashboards you publish. For you, the Power Pivot Gallery enables better management of your Power Pivot reports by allowing you to schedule nightly refreshes of the data within them.

Speak with your SharePoint administrator about your organization’s SharePoint instance, and ask that person to consider adding a Power Pivot Gallery to the site. After you have access to a Power Pivot Gallery, you can upload your Power Pivot workbooks by following the same steps for publishing a standard workbook to SharePoint (see “Publishing an Excel Workbook to SharePoint” in this chapter).

Figure 7-5 illustrates a typical Power Pivot Gallery. Note that each workbook is shown as a thumbnail, providing users with a snapshot of each report. Clicking a thumbnail opens the report as a web page.

If you have an Office 365 SharePoint subscription, you have no option, unfortunately, for a Power Pivot Gallery, because Office 365 doesn’t support it. This situation may change as Microsoft continues to add improvements to Office 365.

Refreshing data connections in published Power Pivot workbooks

You can manually refresh the data connections within your published Power Pivot report by opening the workbook and selecting the Data drop-down menu. As you can see in Figure 7-6, you have the option of refreshing a single connection or all connections in the workbook.

Alternatively, you can use the Power Pivot Gallery to schedule an automatic refresh based on a schedule you define. Simply click the Manage Data icon in the upper-right corner of the target report’s shadow box (see Figure 7-7).

SharePoint opens the Manage Data Refresh screen, shown in Figure 7-8. The idea is to configure each setting to set up the schedule you want.

This list describes each settings section:

• Data Refresh: The Data Refresh section holds the On-Off switch for your schedule. Place a check in the Enable Schedule check box to make the schedule active. Remove the check to stop automatic refreshes.
• Schedule Details: In the Schedule Details section, you can specify the frequency and intervals of the schedule. In addition to selecting the time intervals, you can refresh as soon as possible. Placing a check mark next to the Also Refresh as Soon as Possible option starts a refresh within a minute. This option is helpful when you want to test the refresh process.
• Earliest Start Time: The Earliest Start Time section lets you specify the time of day to run the refresh process.
• E-mail Notifications: The E-mail Notifications section lets you specify who should receive an email from SharePoint every time the scheduled refresh is run. Individuals who are specified receive an email regardless of whether the process ran successfully.
• Credentials: Most data sources require authentication in order to connect to them (username, password, and so on). The Credentials section lets you specify how authentication is passed to external data sources. This section has these three options:
  • Use the Data Refresh Account Configured by the Administrator: Authenticates the SharePoint system account to the data source. You typically have to work with your SharePoint administrator to set up this authentication method and ensure that the data source can use SharePoint’s system account.
  • Connect Using the Following Windows User Credentials: Lets you explicitly enter a username and password for authentication. Avoid using your personal username and password here. Instead, use this option with an application account, which is a “dummy” user created by your database administrators.
  • Connect Using the Credentials Saved in Secure Store Service (SSS) to Log On to the Data Source: This authentication option allows data connections to be refreshed without requiring a password. In order to use this option, you need a Secure Store ID from your SharePoint administrator.
• Data Sources: This setting lets you define whether all data connections are refreshed, or only specific connections. Deselecting the All Data Sources check box enables the selection of individual connections in your workbook.