Share via

  • Article

How to: Use PowerPivot Gallery

PowerPivot Gallery is a special-purpose SharePoint document library that provides rich preview and document management for published Excel workbooks and Reporting Services reports that contain PowerPivot data.

Note

Depending on how your server is configured, you might see warning or error messages in the preview area for specific documents. Messages can appear when an Excel workbook is set to automatically refresh its data whenever it is opened. Data refresh warning messages will appear as the preview image if Excel Services is configured to show Warn on data refresh error messages. Farm or service administrators can modify configuration settings to allow a preview of the actual worksheet to appear. For more information, see Create a trusted location for PowerPivot sites.

This topic contains the following sections:

Prerequisites

Icons in PowerPivot Gallery

Save an Excel Workbook to PowerPivot Gallery

Create new reports or workbooks based on a published PowerPivot workbook

Open a workbook or report in full page mode

Schedule data refresh for PowerPivot workbooks in PowerPivot Gallery

Delete a workbook or report in PowerPivot Gallery

Refresh a thumbnail image

Prerequisites

Verify that a PowerPivot Gallery exists. This gallery can be created automatically based on how PowerPivot for SharePoint is installed. If it is not created by SQL Server Setup, you can create it yourself. For more information, see How to: Create and Customize PowerPivot Gallery.

You must have Contribute permissions or above to publish or upload a file.

You must have Silverlight. Silverlight can be downloaded and installed through Microsoft Update. If you view a PowerPivot Gallery library using a browser that does not have Silverlight, click the link on the page to install it. You must close and reopen the browser after you install it.

You must have SQL Server 2008 R2 Reporting Services in SharePoint integrated mode if you want to create new Reporting Services reports from PowerPivot workbooks published in PowerPivot Gallery.

To view or create a Reporting Services report that is based on a PowerPivot workbook, both the workbook and the report must be in the same PowerPivot Gallery. The report must either use a PowerPivot workbook that contains embedded data, or the workbook must contain at most one external data source that is a PowerPivot workbook.

Icons provide a visual indicator on content availability and status.

Icon

Description
Hourglass icon

The hourglass icon appears when a thumbnail image of each page in the document is being generated. Refresh the page to display the update the image.
Truncated report icon

The pages icon appears when a workbook or report has more pages than can be displayed in PowerPivot Gallery. To view all the pages, you must use a client application.
Error icon

The error icon appears when a thumbnail image could not be rendered for the document. The document is published to the library, but it cannot be rendered in the custom PowerPivot Gallery views. You should be able to view the document in a client application, such as the PowerPivot for Excel add-in.
Invalid content icon

The unavailable content icon appears when the document you uploaded cannot be rendered in PowerPivot Gallery. Supported document types include PowerPivot workbooks and reports created in SQL Server 2008 R2 Reporting Services Report Builder.

This icon also appears if you recycle a document from Recycle Bin.

If you are getting this icon for a document that previously presented a valid preview image, you can refresh the image by editing a document property and then saving your changes. For instructions, see Refresh a thumbnail image in this topic.
Locked content icon

The locked content icon appears when thumbnail images are purposely disabled for this document. PowerPivot Gallery does not generate thumbnail images for Excel workbooks that contain no PowerPivot data, or for PowerPivot workbooks or Reporting Services reports that do not meet the requirements for snapshot generation. For more information, see the Prerequisites section in this topic.

You can publish PowerPivot workbooks to the library using all of the sharing techniques that Excel 2010 provides. For example, in Excel 2010, you can use Save As to specify all or part of a SharePoint path to a library.

  1. Save the file.

  2. In the Excel 2010 File menu, click Save & Send.

  3. Click Save to SharePoint.

  4. Click Publish Options if you want to use Excel Services Options to select individual sheets or parameters that you want to publish. For example, the Parameters tab in Excel Services Options lets you choose which slicers appear in the published workbook.

  5. Click Save As. In the Save As dialog box, in File name, enter a full or partial URL to PowerPivot Gallery. If you enter a portion of the URL address, such as the server name, you can browse the site to find the PowerPivot Gallery. To do this, click Save to open a connection to the server you specified.

    Screen shot of Excel Save As dialog box

  6. Using the Save As dialog box, select PowerPivot Gallery on your site.

  7. Click Open to open the library.

  8. Click Save to publish the workbook to the library.

In a browser window, verify that the document appears in PowerPivot Gallery. Newly published documents will appear in the list. Library settings determine where the document appears (for example, sorted in ascending order by date, or alphabetically by name). You might need to refresh the browser window to view the most recent additions.

You can also upload a workbook if you want to start from SharePoint and select from your computer which file to publish.

  1. In a SharePoint site, open PowerPivot Gallery.

  2. In Library Tools, click Documents.

  3. In Upload Document, select an upload option and then enter the name and location of the file you want to upload. Library settings determine where the document appears. You might need to refresh the browser window to view the latest addition.

Create new reports or workbooks based on a published PowerPivot workbook

For PowerPivot workbooks that you publish to PowerPivot Gallery, you can create additional workbooks or Reporting Services reports that use the published workbook as a connected data source.

New document button

Click the downward arrow portion of the New Report button to launch Report Builder or Excel 2010. PowerPivot Gallery must use one of the predesigned views (Theater, Gallery, or Carousel) in order for the New Report button.

Create Report Builder Report

Creating a new report based on an existing PowerPivot workbook in the library requires that Reporting Services is configured for SharePoint integration for the same sites that contain PowerPivot Gallery. When you select the Create Report Builder Report option, Report Builder is downloaded from the report server and installed on the local workstation on first use. A placeholder report file is created for the new report and saved to PowerPivot Gallery. Connection information to the PowerPivot workbook is created for you as a new data source in the report. As a next step, you can build the datasets and report layout in the design workspace. As you use Report Builder to assemble your report, you can save your changes and the final result to the report document in the gallery. To avoid data disconnections later, be sure to keep the report and workbook files together in the same library.

Open New Excel Workbook

To create a new Excel workbook from an existing workbook, you must already have Excel 2010 and PowerPivot for Excel on the local computer. Choosing Open New Excel Workbook starts Excel, opens a blank workbook (.xlsx) file, and loads PowerPivot data in the background as a connected data source. Only the data from the PowerPivot window in the original workbook is used in the new workbook. PivotTables or PivotCharts from the original workbook are excluded. The new workbook links to data in the original workbook. The data is not copied to the new workbook itself.

Open a workbook or report in full page mode

Click on any visible thumbnail image of the previewed document to open it in full page mode independent of PowerPivot Gallery preview. PowerPivot workbooks will open in a browser. Reporting Services reports will open in the ReportViewer web part that is part of Reporting Services deployment on a SharePoint server.

An alternative approach to viewing the workbook in a browser is to open it in Excel on a client workstation. You must have Excel 2010 and the PowerPivot for Excel add-in to view the file. You can use Excel 2007 to open the file but you cannot use it to pivot on the data. For this reason, Excel 2010 is recommended for both viewing and creating PowerPivot data. If you do not have the required applications, you must use a browser to view the workbook from SharePoint.

PowerPivot data in a published Excel workbook can be refreshed at scheduled intervals.

New Data Refresh button in Report Gallery

Click the Manage Data refresh button to create or view a schedule that retrieves updated data from connected data sources. For instructions on how to create a schedule, see Schedule PowerPivot Data Refresh.

To delete a document from the library, switch to the All Documents view first.

  1. In a SharePoint site, open PowerPivot Gallery.

  2. In Library Tools, click Library.

  3. In Manage Views, in the Current View list, click the down arrow and select All Documents.

  4. Select the workbook or report you want to delete.

  5. In Documents, in Manage, click the Delete Document button.

Refresh a thumbnail image

Use the following steps to regenerate a thumbnail image for a document in PowerPivot Gallery.

  1. Switch PowerPivot Gallery to All Documents view. To do this, click Library in the server ribbon and change the Current View to All Documents.

  2. Select the workbook or report for which you want to refresh the thumbnail image..

  3. Click the down arrow to the right, and then select Edit Properties.

  4. Click Save. Saving the document forces the snapshot service to regenerate the preview image.