Create a Power Pivot Gallery Library

Important: Click PowerPivot Gallery to view the latest information about this subject on the Microsoft web site.

PowerPivot Gallery is a special-purpose SharePoint document library that provides rich preview and document management for published Excel workbooks that contain PowerPivot data as well as other document types.

PowerPivot Gallery is created for you when you install Microsoft SQL Server PowerPivot for SharePoint using the New Farm Configuration 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.

What do you want to do?

Learn about PowerPivot Gallery

Learn about the requirements for creating or using PowerPivot Gallery

Create a PowerPivot Library

Publish an Excel workbook to PowerPivot Gallery libary

Create a new Excel workbook or Reporting Services report in PowerPivot Gallery

Schedule data refresh for published workbooks in PowerPivot Gallery

Customize a PowerPivot Gallery library

Learn about PowerPivot Gallery

PowerPivot Gallery is a SharePoint document library that is available when you install SQL Server PowerPivot for SharePoint in a SharePoint 2010 farm or on standalone server. The library combines an accurate representation of the file contents with facts about document origin. You can see immediately who created the document and when it was last modified. Preview varies depending on whether you use gallery view or theatre view within 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.

  • Theatre view has a different layout. The preview area is centered, with individual worksheets revolving to the front as you hover the mouse over them.

PowerPivot Gallery uses a snapshot service to create thumbnails of a larger, multi-page document. It can read Excel workbooks and Reporting Services report definition (.rdl) files. If you publish a file that the snapshot service cannot read, no preview image will be available for that file.

The snapshot is 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 an Excel workbook in a browser. Preview has a limited surface area. Portions of a workbook or report are trimmed to fit the available space. You can open a workbook or report to view the document in its entirety.

Top of Page

Learn about the requirements for creating or using PowerPivot Gallery

  • You must be a site owner to create a library.

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

  • You must have the Silverlight client control that runs in a browser. Silverlight can be downloaded and installed through Microsoft Update. If you view a PowerPivot Gallery library using a browser that does not have Silverlight, you will be prompted to install it. You must close and then 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 Excel workbooks published in PowerPivot Gallery.

Top of Page

Create a PowerPivot Library

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

  2. Click More create options…

  3. Under Libraries, click PowerPivot Gallery. If you do not see PowerPivot Gallery in the list, one or more of the following conditions might be indicated:

    • PowerPivot for SharePoint is not enabled for your application.

    • You do not have sufficient permissions to add a library to this site.

  4. Enter the name, description, launch, and version preferences. Be sure to include descriptive information that helps users identify this library as rich preview for Excel workbooks and Reporting Services reports. The New library page includes a document template option. PowerPivot Gallery ignores this setting; document templates are not used in the library. You can leave the default selection as-is, or specify none.

  5. Click Create.

Top of Page

Publish an Excel workbook to PowerPivot Gallery libary

Excel 2010 provides several ways to save a file to a SharePoint library. You can use Save As to specify a fully-qualified SharePoint path to a library. Alternatively, in Excel 2010, you can enter part of a server URL and browse a SharePoint site using the Save As dialog box to find a library that you want to use.

  1. Save the file.

  2. On the File menu, click Share, and then choose one of the following options. You can use either approach for sharing your workbook. Both PowerPivot for SharePoint and Excel Services are always used to process and render PowerPivot data in the farm, regardless of how you publish the workbook.

    • Click Save to SharePoint if you want to publish the entire workbook.

    • Click Publish to Excel Services 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.

  3. 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, click Save to open a connection to the server you specified.

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

  5. Click Open to open the library.

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

Top of Page

Create a new Excel workbook or Reporting Services report in PowerPivot Gallery

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 linked data source.

  • Click the downward arrow portion of the new document button to launch Report Builder or Excel 2010.

Report Builder launches as a ClickOnce application that is downloaded from the server and installed on the local workstation on first use. There is no separate advance installation step required.

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

Top of Page

Schedule data refresh for published workbooks in PowerPivot Gallery

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

Top of Page

Customize a PowerPivot Gallery library

PowerPivot Gallery is a document library. 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 extend PowerPivot Gallery to provide preview for other document types besides Excel 2010 workbooks or SQL Server 2008 R2 Reporting Services reports.

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.

Switch to Theatre view or Gallery view

Gallery view is the default. Preview appears to the left, and smaller thumbnails of each workbook appear next to it in a sequential left-to-right order.

Theatre view centers the preview window and lets you rotate through the available sheets.

  1. In a SharePoint site, open PowerPivot Gallery.

  2. In Library Tools, click Library.

  3. In Custom Views, in Current View, select the view you want to use from the list. Predesigned views include PowerPivot Gallery View and PowerPivot Report Theater.

Change PowerPivot Gallery layout

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. Columns settings are ignored. PowerPivot Gallery always shows 'Last modifed' 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.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×