Creating a Power View Report in Excel

Power View

Power View, is an interactive, web-based data exploration, visualization, and presentation technology. Power View in SharePoint was introduced in SQL SERVER as a feature in the SharePoint integrated mode of Reporting Services. Power View in Excel also includes the features added to Power View in SQL Server 2021 Service Pack 1, such as maps, hierarchies, and themes.

It provides intuitive ad-hoc reporting for business users such as data analysts, business decision-makers, and information workers. They can easily create and interact with views of data from data models based on PowerPivot workbooks published in a PowerPivot Gallery, or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances. Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.

Power View report with multiple views featuring tiles, slicers, a chart filter, and a number of visualizations, including cards, small multiples, and a bubble chart.
Note The two features available in Power View in SharePoint that are not available in Power View in Excel are the ability to develop reports using an Analysis Services multidimensional model as a source and the ability to export the report to Microsoft PowerPoint format. Also, unlike the Power Pivot and Power Query add-ins, which you can install in Excel 2010, the Power View add-in works only with Excel 2013. Like Power View in SharePoint, Power View in Excel requires you to install Silverlight.

Creating a Power View report

Power View reports are built on data models.

As mentioned at the beginning of this topic, Excel has one Data Model per workbook. You can insert a Power View report into a workbook based on this model or on an external data source. By using the external data source option, you can add different Power View reports that rely on separate data sources to the same workbook.

To create a Power View report, click Power View on the Insert tab on the ribbon. A special sheet is displayed in the workbook, with a report design surface and a filters pane. Select fields in the Power View Fields list to add a table to the report design surface. You can click the Design tab on the ribbon to switch to one of the following data visualizations, all of which are also in the Power View in SharePoint version:

• Matrix
• Card
• Bar (stacked, 100% stacked, clustered)
• Column (stacked, 100% stacked, clustered)
• Line
• Scatter
• Pie
• Map

Note Like Power Pivot, Power View is not a new self-service BI feature, although it is new to Excel. Because we have elected to dedicate the majority of this field to new features, we do not repeat the information we provided on this topic in Introducing Microsoft SQL Server 2012. You can learn more about the features added as part of SQL Server 2012 SP1 at https://office.microsoft.com/en-us/excel-help/whats-new-in-power-view-in-excel-2013-and-in-sharepoint-server-HA102901475.aspx#_Toc358038111.

Working with visualizations

While a list of items is great for entering or auditing data, data visualizations are a great way to distill information to what matters most that is understandable quickly. They work by engaging visual parts of our brains, which are inherently designed to detect patterns quickly.

To start a new visualization on the same report, click an empty area of the report and begin selecting fields to add to the new table, which you can switch to a new visualization later. You can also create a visualization by copying an existing visualization and pasting it into the same sheet. After you paste the copy, you can change the fields selected in the bottom section of the Power View Fields list to arrange the visualization that suit your needs, as shown in Figure 4.15. You can also copy and paste visualizations from one sheet to another, but only if you are working with the same data connection on both sheets.

FIGURE 4-15 A Power View report in Excel with multiple visualizations on a single sheet.

Sharing a Power View report

After designing a Power View report in Excel, you can publish it to Excel Services as part of an on-premises SharePoint infrastructure or to Office 365 as part of cloud infrastructure. While publishing your workbook to an on-premises SharePoint installation, you can add it to a standard SharePoint document library or to the Power Pivot Gallery. If you choose the Power Pivot Gallery, the thumbnail image for the Power View report is not displayed in the gallery views, although the Power View report displays normally when you open the workbook from that location.

List of Related Microsoft Certification Courses: