Create and Customize Power Pivot Gallery
APPLIES TO:SQL Server Analysis ServicesAzure Analysis Services
Power Pivot Gallery is a special type of SharePoint document library that provides rich preview and document management for published Excel workbooks and Reporting Services reports that contain Power Pivot data.
In this topic
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.
The Power Pivot Gallery requires Microsoft Silverlight. The Microsoft Edge browser does not support Silverlight.
To view the library contents in Edge, click the Library tab in Power Pivot gallery, and then change the document library view to All Documents.
To change the default view , click the Library tab and then click Modify View. Click "Make this the default view" and then click OK to save the default view.
For more information on what Edge supports, see the Windows blog, A break from the past, part 2: Saying goodbye to ActiveX, VBScript...
You must be a site owner to create a library.
You must have Contribute permissions or above to publish or upload a file.
Power Pivot Gallery cannot be in a restricted site. The parent site that contains Power Pivot Gallery must be added to either the trusted site or Local intranet zone.
The Power Pivot web application solution must have been deployed for your application, and the Power Pivot feature must have been activated for the site collection. For more information, see Deploy Power Pivot Solutions to SharePoint andActivate Power Pivot Feature Integration for Site Collections in Central Administration.
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.
Power Pivot Gallery is a library template that is available when you install Microsoft SQL Server 2016 Power Pivot for SharePoint on a SharePoint server. The Power Pivot Gallery combines an accurate preview of the file contents with facts about document origin. You can see immediately who created the document and when it was last modified. To create preview images, Power Pivot Gallery uses a snapshot service that can read Power Pivot workbooks and Reporting Services reports that contain Power Pivot data. If you publish a file that the snapshot service cannot read, no preview image will be available for that file.
Preview images are based on how the workbook is rendered by Excel Services. The representation in Power Pivot Gallery should be identical to what you see when you view a Power Pivot workbook in a browser. However, preview has a limited surface area. Portions of a workbook or report might be trimmed to fit the available space. You might need to open a workbook or report to view the document in its entirety.
Refreshing Power Pivot workbook data from external data sources is fully supported in Power Pivot Gallery, but it requires additional configuration. A farm or service administrator must add the Power Pivot Gallery as an Excel Services trusted location. For more information, see Create a trusted location for Power Pivot sites in Central Administration.
Create the Power Pivot Gallery
Power Pivot Gallery is created for you when you install Microsoft SQL Server 2016 Power Pivot for SharePoint using the New Server installation option. If you added Power Pivot for SharePoint to an existing farm or if you want an additional library, you can create a new one for your application or site.
SharePoint 2010: Click Site Actions at the top left corner of your site's home page.
Click More Options.
Under Libraries, click Power Pivot Gallery.
SharePoint 2013: Click the settings icon . Click Site Contents
Click add an app.
Click Power Pivot Gallery.
Type a name for the library. Be sure to include descriptive information that helps users identify this library as rich preview for Power Pivot workbooks and Reporting Services reports.
Ask a farm or service administrator to add the Power Pivot Gallery as a trusted location for Excel Services. This step is necessary to avoid errors if a user configures a workbook for Power Pivot data refresh. For more information about this task, see Create a trusted location for Power Pivot sites in Central Administration.
A link to the Power Pivot Gallery library will appear in the navigation Quick Launch pane for the current site.
You can create additional Power Pivot Gallery libraries if you are enforcing different permissions for different site collections or individual sites.
Customize a Power Pivot Gallery Library
Power Pivot Gallery is a SharePoint document library. Therefore, you can use standard library tools in SharePoint to change library settings or work with individual documents in the library. Each library that you create can be independently customized to use a different view or library settings.
Sort order and filters can be modified to change where workbooks appear in the list. By default, documents are listed in the order in which they were added, where the last document published appears at the bottom of the list. Once a document is published, it retains its place in the list. Updating and republishing the document updates it in place in the list.
You cannot enable or disable preview for specific documents. The snapshot service will generate preview images for all Power Pivot workbooks and for Reporting Services reports that are based on Power Pivot workbooks that are stored in the same library. These images can be viewed by all users who have View permissions on the document.
You cannot extend Power Pivot Gallery to provide preview for other document types. Preview is supported only for Excel 2010 workbooks or SQL Server 2008 R2 Reporting Services reports that contain Power Pivot data.
You cannot change the settings that control document origin information. Facts that appear about individual documents, such as who added or last modified the workbook, are determined by a fixed set of columns that cannot be modified.
Change sort order, add filters, or limit the number of documents
Power Pivot Gallery always shows 'Last modified' and 'Created by' values. You cannot disable these columns. You cannot enable other columns for the library.Use the following instructions to change the sort order, add a filter, or limit the number of documents that are visible.
In a SharePoint site, open Power Pivot Gallery.
In the ribbon, click Library.
SharePoint 2010: In Custom Views, click Modify this View.
SharePoint 2013: In Manage Views, click Modify View.
In Sort, specify the criteria that will be used to determine how workbooks appear in the list. By default, documents are listed in the order in which they were added.
In Filter, specify criteria that will be used to show or hide workbooks based on conditional values set on columns. For example, you might want to hide all workbooks created prior to a certain date.
In Item Limit, specify options that are useful for Power Pivot Gallery libraries that contain a very large number of documents. You can limit the actual number of items that appear in the list, or display items in batches.
Click OK to save your changes.
Disable or Hide the Refresh Button
You cannot hide the Manage Data Refresh button. However the button is disabled if the user does not sufficient permissions.
Workbook owners or authors must have Contribute permission to schedule data refresh on a workbook. Users with Contribute permissions can open and edit the workbook’s data refresh configuration page to specify the credentials and schedule information used to refresh the data.
So, users that only have View or Read permission levels, will not be able to access the refresh button. The refresh button is visible but disabled. For more information, see User permissions and permission levels in SharePoint 2013.
Switch to Theater View or Gallery View
Preview varies depending on how you configure the view for the library. In Gallery view, you can hover the mouse pointer over individual worksheets in the workbook to bring a sheet into focus in the preview area.
The following table describes the different layouts for presenting thumbnail sketches of each previewed page:
|Gallery View (default)||Gallery is the default view for a Power Pivot Gallery. The preview appears to the left. Smaller thumbnails of each worksheet appear next to it in a sequential left-to-right order.|
|All Documents||This is the standard layout for document libraries. You can choose this view to manage individual documents or view library contents in a list format.
Use this view to edit properties, delete, or move individual documents.
If you enabled versioning, you must use this view to check documents in or out of the library.
|Theatre View and Carousel View||These are specialized views that work best if you are showcasing a small number of related documents. The full rotation of thumbnails includes all pages in all documents in the library. If you have a large number of documents, these views might be impractical for users who want to find or open a specific Power Pivot workbook.
Theatre view: The preview area is centered. Smaller thumbnails of each worksheet appear lower on the page, on either side.
Carousel view: The preview area is centered. The thumbnails that immediately precede and follow the current thumbnail are adjacent to the preview area.
Switch to a different view
In a SharePoint site, open Power Pivot Gallery.
In the ribbon, click Library.
In Manage Views, in Current View, select the view you want to use from the list. Predesigned views include Gallery, Theater, and Carousel. Alternatively, you can choose All Documents if you want to move, delete, or manage documents in the library.