Share via

  • Article

How to: Create and Customize PowerPivot Gallery

PowerPivot Gallery is a special type of SharePoint document library that provides rich preview and document management for published Excel workbooks and Reporting Services report that contain PowerPivot data.

This topic contains the following sections:

Prerequisites

Overview

Create PowerPivot Gallery

Customize a PowerPivot Gallery Library

Switch to Theater View or Gallery View

Prerequisites

You must be a site owner to create a library.

The PowerPivot web application solution must have been deployed for your application, and the PowerPivot feature must have been activated for the site collection. For more information, see Deploy PowerPivot Solutions and Activate PowerPivot Feature Integration for Site Collections.

PowerPivot Gallery cannot be in a restricted site. The parent site that contains PowerPivot Gallery must be added to either the trusted site or Local intranet zone.

Overview

PowerPivot Gallery is a library template that is available when you install Microsoft SQL Server PowerPivot for SharePoint on a SharePoint 2010 server. The PowerPivot 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, PowerPivot Gallery uses a snapshot service that can read PowerPivot workbooks and Reporting Services reports that contain PowerPivot 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 PowerPivot Gallery should be identical to what you see when you view a PowerPivot 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 PowerPivot workbook data from external data sources is fully supported in PowerPivot Gallery, but it requires additional configuration. A farm or service administration must add the PowerPivot Gallery as an Excel Services trusted location. For more information, see Create a trusted location for PowerPivot sites.

PowerPivot Gallery is created for you when you install Microsoft SQL Server PowerPivot for SharePoint using the New Server installation option. If you added PowerPivot for SharePoint to an existing farm or if you want an additional library, you can create a new one for your application or site.

  1. Click Site Actions at the top left corner of your site's home page.

  2. Click More Options…

  3. Under Libraries, click PowerPivot Gallery.

  4. Type a name for the library. Be sure to include descriptive information that helps users identify this library as rich preview for PowerPivot workbooks and Reporting Services reports.

  5. Click Create.

  6. Ask a farm or service administrator to add the PowerPivot Gallery as a trusted location for Excel Services. This step is necessary to avoid errors if a user configures a workbook for PowerPivot data refresh. For more information about this task, see Create a trusted location for PowerPivot sites.

A link to the PowerPivot Gallery library will appear in the navigation Quick Launch pane for the current site.

You can create additional PowerPivot Gallery libraries if you are enforcing different permissions for different site collections or individual sites.

PowerPivot 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 PowerPivot workbooks and for Reporting Services reports that are based on PowerPivot 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 PowerPivot 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 PowerPivot 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

Use the following instructions to change the sort order, add a filter, or limit the number of documents that are visible.

  1. In a SharePoint site, open PowerPivot Gallery.

  2. In Library Tools, click Library.

  3. In Custom Views, click Modify this View. PowerPivot Gallery always shows 'Last modified' and 'Created by' values. You cannot disable these columns. You cannot enable other columns for the library.

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

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

  6. In Item Limit, specify options that are useful for PowerPivot 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.

  7. Click OK to save your changes.

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.

Screenshot of report gallery at 30%

The following table describes the different layouts for presenting thumbnail sketches of each previewed page:

View

Description

Gallery View (default)

Gallery is the default view for a PowerPivot 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 PowerPivot 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

  1. In a SharePoint site, open PowerPivot Gallery.

  2. In Library Tools, click Library.

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