Use Power Pivot Gallery
Updated: August 31, 2015
Applies To: SQL Server 2016
Power Pivot Gallery is a special SharePoint document library that provides rich preview and document management for published Excel workbooks and Reporting Services reports that contain Power Pivot data.
For a complete list of prerequisites, see Create and Customize Power Pivot Gallery.
Icons provide a visual indicator on content availability and status.
|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.|
|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.|
|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.|
|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.
|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.|
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.
Save the file.
Excel 2010: In the File menu, click Save & Send.
Click Save to SharePoint.
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.
Excel 2013: In the File menu, click Save.
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.
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.
Using the Save As dialog box, select Power Pivot Gallery on your site.
Click Open to open the library.
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.
You can also upload a workbook if you want to start from SharePoint and select from your computer which file to publish.
In a SharePoint site, open Power Pivot Gallery.
In Library ribbon, click Documents.
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.
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.
|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.|
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.
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.
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.
Power Pivot data in a published Excel workbook can be refreshed at scheduled intervals.
|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).|
To delete a document from the library, switch to the All Documents view first.
In a SharePoint site, open Power Pivot Gallery.
In the ribbon, click Library.
In Manage Views, in the Current View list, click the down arrow and select All Documents.
Select the workbook or report you want to delete.
In Documents (Files), in Manage, click the Delete Document button.
Use the following steps to regenerate a thumbnail image for a document in Power Pivot Gallery.
Switch Power Pivot Gallery to All Documents view. To do this, click Library in the ribbon and change the Current View to All Documents.
Select the workbook or report for which you want to refresh the thumbnail image..
Click the down arrow to the right, and then select Edit Properties.
Click Save. Saving the document forces the snapshot service to regenerate the preview image.
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.