Power View in Excel 2013 and Power View in SharePoint Server both provide an interactive data exploration, visualization, and presentation experience for all skill levels. Pull your data together in tables, matrices, maps, and a variety of charts in an interactive view that brings your data to life. Many of the features now in Power View in Excel 2013 were in Power View in SharePoint 2010, and new features have been added to both.

Power View sheets can connect to different data models in one workbook

In Excel 2013, every workbook can contain an internal Data Model that you can modify in Excel, in Power Pivot, and even in a Power View sheet in Excel. A workbook can contain only one internal Data Model, and you can base a Power View sheet on the Data Model in that workbook or on an external data source. A single Excel workbook can contain multiple Power View sheets, and each of the sheets can be based on a different data model.

Each Power View sheet has its own charts, tables, and other visualizations. You can copy and paste a chart or other visualization from one sheet to another, but only if both sheets are based on the same data model.

Modify the internal Data Model without leaving the Power View sheet

Now that you can create Power View sheets and an internal Data Model in an Excel 2013 workbook, if you base your Power View sheet on the internal Data Model, you can make some changes to the Data Model while you’re in the Power View sheet. For example:

From the Field List in Power View in Excel, you can create relationships between different tables in the workbook.

Power View in Excel Services and Excel Online, and in Office 365

When you create Power View sheets in Excel, you can view and interact with them on-premises in Excel Services, and in Office 365. You can only edit Power View sheets in Excel 2013 on a client computer.

In Office 365, you view Power View sheets with Excel Online Data Center, part of SharePoint Online, rather than with Excel Services.

If you save an Excel workbook with Power View sheets to a Power Pivot Gallery, the Power View sheets in the workbook won’t be displayed in the Gallery, but they are still in the file. You’ll see them when you open the workbook.

Power View in Excel 2013 and SharePoint Server: New features

Pie charts

Pie charts are simple or sophisticated in Power View. You can make a pie chart that drills down when you double-click a slice, or a pie chart that shows sub-slices within the larger color slices. You can cross-filter a pie chart with another chart. Say you click a bar in a bar chart. The part of the pie chart that applies to that bar is highlighted, and the rest of the pie is grayed.

Maps

Maps in Power View use Bing map tiles, so you can zoom and pan as you would with any other Bing map. Locations and fields are dots on the map: The bigger the value, the bigger the dot. When you add a multi-value series, you get pie charts on the map.

Hierarchies

If your data model has a hierarchy, you can use it in Power View. For example, the data model could have a hierarchy called Location, consisting of the fields Continent > Country/Region > State/Province > City. In Power View you can add each field one at a time to the design surface, or you can add Location and get all the fields in the hierarchy at once.

If your data model doesn’t have a hierarchy, you can create one in Power View, too. You can put fields in any order in a hierarchy.

You can also use hierarchies from SQL Server Analysis Services tabular models.

Drill up and drill down

Add drill up/drill down to a chart or matrix in Power View so it shows just one level at a time. Report readers drill down for details or drill up for summary data.

When a matrix has multiple fields in the rows or columns, you can set it to show levels, and that collapses the matrix to show only the top, or outermost, level. You double-click one value in that level to expand to show the values under that one in the hierarchy. You click the up arrow to drill back up.

Bar, column, and pie charts work the same way. If a chart has multiple fields in the Axis box, you set it to show levels and you see only one level at a time, starting with the top level. The up arrow in the corner takes you back up to the previous level.

Note Drilling down on either a chart or matrix acts as a filter on that object. In the Filter pane, you see that it filters the values in the object as you drill down, and removes the filter as you drill back up.

Backgrounds and background images

You can set the background of each view from white to black, with a number of gradient options. On darker backgrounds the text changes from black to white so it stands out better.

You can also add background images to each sheet or view. Browse to an image file on your local machine or elsewhere and apply it as the background image for a sheet or view. You can then set it to fit, stretch, tile, or be centered, and set its transparency from 0% (invisible) to 100% (not at all transparent). The workbook or report stores a copy of the image.

Hyperlinks

Add a hyperlink to a text box in a sheet or view. If a field in your data model contains a hyperlink, add the field to the sheet or view. It can link to any Internet or email address

In Power View in Excel and in edit mode for a Power View report in SharePoint, you follow the hyperlink by clicking it while holding down the Ctrl key.

You just click the link for an Excel workbook in Excel Services or Office 365, or for a Power View in SharePoint report in reading and full-screen modes.

Printing

You can print Power View sheets in Excel and views in SharePoint. In both cases, what you print is what you see on the sheet or view when you send it to the printer. If the sheet or view contains a region with a scroll bar, the printed page contains the part of the region that is visible on the screen. If a sheet or view contains a region with tiles, then whichever tile is selected is the one that prints.

Support for right-to-left languages

Power View in both Excel and SharePoint now supports right-to-left languages.

Power View in SharePoint now has options to set the default direction for new views and the direction for a specific existing view. Unless you change it, the direction is the same as the direction for SharePoint.

Power View in Excel takes the setting for default direction from Excel. You can change those settings. In Excel, go to File > Options > Advanced and look for Default direction. In the same dialog box you can also change the direction for a specific sheet, without changing the default direction.

Power View changes how it handles integers

In Power View, to convert a table to a chart at least one data column needs to be aggregated.

By default, Power View for SharePoint 2010 aggregated decimal numbers, but treated integers as categories rather than aggregating them. A data model designer either in Power Pivot or in SQL Server Data Tools could set defaults for integers, but this was the default behavior.

In Power View in Excel 2013 and in SharePoint Server, Power View aggregates both decimal numbers and integers by default. A data model designer can still specify other default behavior, but that is the default.

Power View backward- and forward-compatibility

Power View RDLX files in SharePoint are backward compatible, meaning that if you saved a Power View file in with the SQL Server 2012 Reporting Services add-in, you can open and save it in Power View in SharePoint 2010 or SharePoint 2013 with the SQL Server 2012 Service Pack 1 (SP 1) Reporting Services add-in. The reverse isn’t true: You can’t open a newer-version Power View RDLX file in a version of SharePoint with an older SQL Server Reporting Services add-in.

Power View and data models

Power View and SQL Server Analysis Services data models are forward- and backward-compatible with each other:

You can base a Power View file in SharePoint 2010 with the SQL Server 2012 Reporting Services add-in on an Excel 2013 data model or on a SQL Server 2012 SP 1 Analysis Services tabular model, and vice versa. However, some features such as hierarchies and KPIs are only available if you base a Power View report in SharePoint Server with the SQL Server 2012 SP 1 Reporting Services add-in on an Excel 2013 data model or on a SQL Server 2012 SP 1 Analysis Services tabular model.

Power View and Excel Services

Power View and Excel Services are backward- but not forward- compatible:

SharePoint 2013 is backward-compatible with Excel 2010 Power Pivot workbooks. If you upload an Excel 2010 Power Pivot workbook to SharePoint 2013, you can open it in Excel Services and also base a Power View report on it.

SharePoint 2010 is not forward-compatible with Excel 2013 workbook Data Models. If you upload an Excel 2013 workbook with a Data Model to SharePoint 2010, it may not work properly in Excel Services and you can’t base a Power View report on it.