Export (0) Print
Expand All

Use Power Pivot Gallery

 

Power Pivot 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 Power Pivot data.

System_CAPS_noteNote

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 Power Pivot sites in Central Administration.

This topic contains the following sections:

Prerequisites

Icons in Power Pivot Gallery

Save an Excel Workbook to Power Pivot Gallery

Create new reports or workbooks based on a published Power Pivot workbook

Open a workbook or report in full page mode

Schedule data refresh for Power Pivot workbooks in Power Pivot Gallery

Delete a workbook or report in Power Pivot Gallery

Refresh a thumbnail image

Known Issues

Verify that a Power Pivot Gallery exists. This gallery can be created automatically based on how Power Pivot for SharePoint is installed. If it is not created by SQL Server Setup, you can create it yourself. For more information, see Create and Customize Power Pivot 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 Power Pivot 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 or SQL Server 2012 Reporting Services in SharePoint integrated mode if you want to create new Reporting Services reports from Power Pivot workbooks published in Power Pivot Gallery.

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

Arrow icon used with Back to Top link 

Icons provide a visual indicator on content availability and status.

Icon

Description

GMNI_PowerPivotGalleryIcon_Hourglass

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.

GMNI_PowerPivotGalleryIcon_Truncated

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

GMNI_PowerPivotGalleryIcon_Error

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 Power Pivot Gallery views. You should be able to view the document in a client application, such as the Power Pivot for Excel add-in.

GMNI_PowerPivotGalleryIcon_badtype

The unavailable content icon appears when the document you uploaded cannot be rendered in Power Pivot Gallery. Supported document types include Power Pivot 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 in this topic.Bookmark link 'refresh' is broken in topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"c9ff92d1-787a-4f34-990f-6676b61875d7","entity_type":"Article","locale":"en-US"}'. Rebuilding the topic '{"project_id":"7d6ffa79-2ddc-4606-b774-a6a5d46784cd","entity_id":"c9ff92d1-787a-4f34-990f-6676b61875d7","entity_type":"Article","locale":"en-US"}' may solve the problem.

GMNI_PowerPivotGalleryIcon_Locked

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

Arrow icon used with Back to Top link 

You can publish Power Pivot 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.

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

    2. Click Save to SharePoint.

    3. 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.

    1. Excel 2013: In the File menu, click Save.

    2. Click Browser View 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.

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

    GMNI_ExcelSaveAs
  1. Using the Save As dialog box, select Power Pivot Gallery on your site.

  2. Click Open to open the library.

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

In a browser window, verify that the document appears in Power Pivot 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.

Arrow icon used with Back to Top link 

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 Power Pivot Gallery.

  2. In Library ribbon, 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.

Arrow icon used with Back to Top link 

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

GMNI_btn_NewDocReportGallery

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

Arrow icon used with Back to Top link 

Creating a new report based on an existing Power Pivot workbook in the library requires that Reporting Services is configured for SharePoint integration for the same sites that contain Power Pivot 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 Power Pivot Gallery. Connection information to the Power Pivot 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.

Arrow icon used with Back to Top link 

To create a new Excel workbook from an existing workbook, you must already have Excel and Power Pivot for Excel on the local computer. Choosing Open New Excel Workbook starts Excel, opens a blank workbook (.xlsx) file, and loads Power Pivot data in the background as a connected data source. Only the data from the Power Pivot 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.

Arrow icon used with Back to Top link 

Click on any visible thumbnail image of the previewed document to open it in full page mode independent of Power Pivot Gallery preview. Power Pivot 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 2013 or Excel 2010 and the Power Pivot 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 2013 or Excel 2010 are recommended for both viewing and creating Power Pivot data. If you do not have the required applications, you must use a browser to view the workbook from SharePoint.

Arrow icon used with Back to Top link 

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

GMNI_btn_NewDataRefreshReportGallery

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 a Data Refresh (Power Pivot for SharePoint).

Arrow icon used with Back to Top link 

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

  1. In a SharePoint site, open Power Pivot Gallery.

  2. In the ribbon, 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 (Files), in Manage, click the Delete Document button.

Arrow icon used with Back to Top link 

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

  1. Switch Power Pivot Gallery to All Documents view. To do this, click Library in the 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.

Arrow icon used with Back to Top link 

The Power Pivot Gallery Document content type is not supported. If you enable the Power Pivot Gallery Document content type for a document library, and attempt to create a new document of that type, you will see an error message similar to one of the following:

  • ‘New Document’ requires a Microsoft Sharepoint Foundation-compatible application and web browser. To add a document to this document library, click the ‘Upload Document’ button.

  • "The Internet address 'http://[server name]/testSite/PowerPivot Gallery/ReportGallery/Forms/Template.xlsx' is not valid."“Microsoft Excel cannot access the file 'http://[server name]/testSite/PowerPivot Gallery/ReportGallery/Forms/Template.xlsx'. There are several possible reasons:

The Power Pivot Gallery Document content type is not automatically added to document libraries, You will not encounter this issue unless you manually enable the unsupported content type.

Arrow icon used with Back to Top link 

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft