Power View in Excel 2013 and Power View in SharePoint 2013 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.
In Excel 2013, every workbook can contain an internal Data Model that you can modify in Excel, in PowerPivot, and even in a Power View sheet in Excel. You can base a Power View in Excel 2013 sheet on the Data Model in the same workbook or on an external data source – another workbook, or a tabular model deployed to a SQL Server 2012 Analysis Services (SSAS) instance. A single Excel workbook can contain multiple Power View sheets, and each of the sheets can be based on a different data model. A workbook can contain only one internal Data Model, but Power View sheets in a workbook can link either to that internal model or to an external data model.
- To base a Power View sheet on an external data model, insert a blank Excel worksheet in the workbook, and on the Data tab > Get External Data (by creating a connection or using an existing connection).
- If you want to reuse a connection you already have in this workbook, click Existing Connections, select the connection you want to use, and click Open.
Note If you get external data while in the PowerPivot window, it is automatically added to the internal Data Model, and so it isn’t a separate data model.
- Provide the needed information in the Data Connection Wizard.
- In the last step, the Import Data dialog box, click Power View Report.
Excel opens a new Power View sheet with the external data model in the Field List.
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.
- If the Data Model in Excel has calculated fields, you can create KPIs based on those fields, and then add them to your Power View report.
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.
On-premises, Power View isn’t supported on Excel Web App. In Office 2013, administrators can choose whether users view files with Excel Services or Excel Web App. If the administrator chooses to view workbooks with Excel Web App, you can’t view Power View sheets when viewing Excel workbooks in the browser.
In Office 365, you don’t view Power View sheets using Excel Services. Instead you view them with Excel Web App Data Center, part of SharePoint Online.
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.
- Slicer filtering the report to breads
- Tile flow navigation for tiles, currently on croissant
- Card in a tile container, filtered to the current tile (croissant)
- Line chart in tile container showing quantities consumed and server, filtered to croissants January to December
- Multiples, filtered to breads and sorted in descending order by quantity served
- Column chart filtered to breads, showing quantities served and consumed
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.
You can add key performance indicators (KPIs) (key performance indicator (KPI): A quantifiable measure for gauging business objectives. The KPI gauges the performance of the value, defined by a base measure, against a target value, also defined by a measure or by an absolute value.) to your Power View report to show progress toward goals if the data model your Power View report is based on has them.
If your data model has a hierarchy, you can use it in Power View. For example, the data model could have a hierarchy of Continent, Country/Region, State/Province, and City. In Power View you can add one field at a time, or you can add 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. Just adding multiple fields to the Rows or Columns box for a matrix creates a hierarchy for that matrix. Adding multiple fields to the Axis box of a chart creates a hierarchy for that chart.
You can put fields in any order in a hierarchy. It needn’t be a natural hierarchy like Category > Subcategory > Product. It could be Category > Year > Continent.
The advantage of having the hierarchy in a data model is that you can use it in every Power View report you build on that data model. You can only create a hierarchy in the Data Model in Excel by going to the PowerPivot window
When you have a matrix with 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. To use the example in the Hierarchies section, if you set Power View to show levels, then you see only continent names in the matrix. Double-clicking a continent—say, Asia —shows the countries/regions in Asia, with an arrow to go back to continents. Double-clicking a country—say, India—shows the states/provinces of India, again with an arrow to go back up to countries/regions.
If you open the Filter pane, you see that drilling down on either a chart or matrix is acting as a filter on that object. It is filtering the values in the object as you drill down, and removing the filter as you drill back up. It is only filtering that object, though. It isn’t filtering anything else on the sheet.
Power View has new report themes. When you change the theme, the new theme applies to all the Power View views in the report or sheets in the workbook.
Power View for SharePoint Server 2010 featured eight basic accent themes that controlled chart colors.
Power View in Excel 2013 and in SharePoint Server 2013 now features 39 additional themes with more varied chart palettes as well as fonts and background colors.
You can also change the text size for all of your report elements.
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 view. Browse to an image file on your hard disk or elsewhere and apply it as the background image for a view. You can then set it to fit, stretch, tile, or be centered on the view, and set its transparency from 0% (invisible) to 100% (not at all transparent). The report stores a copy of the image.
You can combine background and image for a variety of effects.
Add a hyperlink to a text box in a view and link it 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. In reading and full-screen modes in Power View in SharePoint, you just click the link.
You can print Power View views in SharePoint and in Excel. In both cases, what you print is what you see on the view when you send it to the printer. If the 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 view contains a region with tiles, then whichever tile is selected is the one that prints.
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. You can also change the direction for a specific sheet in the same dialog box, without changing the default direction.
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 PowerPivot 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 2013, 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. To change your data into a chart, at least one data column needs to be aggregated.
Power View RDLX files in SharePoint are backward compatible, meaning that if you saved a Power View file in SharePoint 2010 with the SQL Server 2012 Reporting Services add-in, you can open and save it in Power View in SharePoint 2013 with the SQL Services 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 older versions of SharePoint and SQL Server Reporting Services.
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 2013 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 are backward- but not forward- compatible:
- SharePoint 2013 is backward-compatible with Excel 2010 PowerPivot workbooks. If you upload an Excel 2010 PowerPivot workbook to SP15, 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.
