Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating Power View Reports in Excel 2013


By:   |   Last Updated: 2013-08-27   |   Comments (12)   |   Related Tips: More > Microsoft Excel Integration

Problem

In my last tip, I talked about Power View, a new tool for intuitive ad hoc reporting which provides an interactive data exploration, visualization, and presentation experience to the business users. I demonstrated how you can configure and create Power View reports in SharePoint. In this tip we cover how to use Power View for Excel.

Solution

Power View in Excel 2013 is a new Add-in for intuitive ad hoc reporting which provides an interactive data exploration, visualization, and presentation experience to the business users or users with all skill levels. It allows the user to create different types of reports including tables, matrices, maps, and a variety of charts in an interactive view based on the data model (a data model is a collection of tables or entities and their relationships that reflects the real-world business scenarios like a sale is related to product sold to a customer etc.) internal to Excel or external data sources.

A Power View report in Excel can be based on a data model internal to an Excel workbook or external data sources including PowerPivot, tabular data model, etc...

A single Excel workbook can contain multiple Power View reports or sheets, and each of these reports or sheets can be based on a different data models. Each Power View report or sheet can have its own charts, tables, and other visualizations as required. Even Copy-Paste operations work well across different reports or sheets of the workbook if these two reports or sheets are based on same data model.

Please note, Power View and PowerPivot are only available in the Office Professional Plus and Office 365 Professional Plus editions. To start using the Power View add-in, we first need to enable it as mentioned below.

Enabling Power View Add-in Excel 2013

To enable Power View Add-in go to File and then click on the Options menu item as shown below:

Enabling Power View Add-in Excel 2013

From the Excel Options, click on the Add-ins page as shown below, select COM Add-ins from the Manage combo-box and click on the Go button.

click on the Add-ins page

Now check the radio button next to Power View and then click on OK and then OK again.

Now check the radio button

If you try inserting a Power View sheet without actually enabling the Power View Add-in, you will be prompted with this message, click on the Enable button to enable the Add-in. This is another way of enabling the Add-in and you don't necessarily need to do it by going through the steps as mentioned above.

Creating Power View Reports in Excel 2013

In this demonstration, I am going to use AdventureWorksDW2012 database to pull data into PowerPivot data model first (though this is not necessary, you can create reports based on the data in workbook or data pulled from a database directly) and then create Power View reports based on that model. Please go to the PowerPivot tab and then click on the Manage icon as shown below:

Creating Power View reports in Excel 2013

In the PowerPivot window, click on the From Database icon and then click on From SQL Server menu item as shown below:

click on the From Database icon and the click on From SQL Server menu item

Clicking on the above will launch the Table Import Wizard as shown below, you need to specify a Server Name and credentials to connect to the specified server. Click on Test Connection to check if you can connect to the specified source.

Clicking on above will launch Table Import Wizard

On the next screen of the wizard, you can specify if you want to pull data directly from tables or views or if you want to write a query to pull data from the source. As I selected to pull data from tables or views, here you can see a list of tables or views which I can now include to bring data from: 

you can see list of tables or views which I can now include to bring data from

Here is the data model which I created for demonstration purposes, as you can see it is quite a simple model (for simplicity in demonstration though you can create a complex model and it works fine) with FactResellerSales, DimProduct, DimProductSubCategory and DimProductCategory.

Here is the data model which I created for demonstration purpose

Now return to Excel, go to the Insert tab and then click on the Power View icon as show below:

Now return to Excel, go to Insert tab and then click on Power View

The Power View designer will open and on the right side of the designer you will notice all the entities from the selected model listed under Power View Fields as shown below:

Now Power View designer will open

Now you can drag or check\select fields to do ad-hoc reporting. I selected Product Category and Sales Amount (to analyze sales amount by product category) and this is how this report should look like.

Now you can drag or check\select fields to do ad-hoc reporting

Its just a click away, if we want to change the report type; simply select the table and from the Design tab select from the varieties of options available. For example, in my case I chose Pie Chart as shown below:

Its just a click away, if we want to change the report type

And here is the result of the above selection, in just one click the tabular report turned to a pie chart report.

in just one click the tabular report turned to pie chart report

Now I want to add drill down capability to my report. I want to have a pie chart report as above but clicking on any part of the pie at the Product category level, I want to drill down to the Product Sub Category level and then to the Product level. Again its quite simple and easy, just drag Product Sub Category under Product Category and Product under Product Sub Category in the Color section of Power View Fields as shown below:

Now I want to add drill down capability to my report

Now click on the any part of the pie or product category in the chart and it will take you to another pie chart of product sub categories under the selected product category as you can see below:

click on the any pie or product category in the above chart which will take you to another pie chart of product sub categories

Likewise, clicking on the any of the pie or product sub category of the above chart will take you to another pie chart of products under the selected product sub category as you can see below:

Likewise, clicking on the any of the pie or product sub category of the above chart will take you to another pie chart

In order to drill up, you can click on the Drill up icon, which is shown below, and this is available on the right top corner of the report.

ou can click on the Drill up icon

Creating Power View Map Reports in Excel 2013

Now let's create some cool Power View map reports. For this demonstration, I have pulled FactResellerSales, DimResellers, DimGeography and DimProduct as you can see below:

Creating Power View map reports in Excel 2013

Simply drag Sales Amount and Country from the Power View Fields list and your report should look like as shown below:

Simply drag Sales Amount and Country from the Power View Fields list

To turn the above tabular report to a map report, simply go to Design tab and then click on Map icon as shown below:

simply go to Design tab and then click on Map icon

That's all and here is your first map report for the sales. Hovering on any of the circles will provide the detail information as shown below; also the size of the circle indicates the value with respect to the other values for the report:

Hovering on any of the circles will provide the detail information

What we see in the above report is total sales amount country wise; lets change it to show total value but dividing among different colors of the products. To achieve this, we just have to drag Color attribute from DimProduct dimension to Color section in the Power View Field list as shown below:

What we see in the above report is total sales amount country wise

And this is how the report should look; as you can see each pie of the chart represents the sales amount for each product color.

as you can see each pie of the chart represents sales amount for each product color

You can also follow a similar approach, as discussed above, to create a drill down map report as well. So basically, what I want is to drill down from country to state and then to city for analyzing sales amount. Here is what you can see after drillling down from country (US) to state wise sales amounts.

what I want is to drill down from country to state and then to city for analyzing sales amount

Here is what you can see after drillling down from state (Washington) to city wise sales amounts.

Here is what you can see after drill down from state
Next Steps


Last Updated: 2013-08-27


get scripts

next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, February 16, 2015 - 8:31:50 AM - Jeremy Kadlec Back To Top

Jack,

Feel free to spread the word about MSSQLTips.com.

Thank you,
Jeremy Kadlec
Community Co-Leader


Sunday, February 15, 2015 - 12:36:49 PM - Jack Thompson Back To Top

Good day,

Am writing up some notes for my students.  I teach Excel and other apps and usually use Logical Operations or AxzoPress texts.

 

Am wondering if I can provide my students your website address ??  Great explanations on there !!

 

Thanks,


Thursday, January 22, 2015 - 1:08:15 PM - Lerina Garza Back To Top

I have a Power View that has 4 charts in the same tab. Sometimes the drill up button is missing after I've drilled down into one of the charts. When this occurs all of the icons there are missing, the extend, drill, filter. I can't get them to return. I end up closing the document and reopening it. Any sugestions?

 

Thanks,


Monday, December 22, 2014 - 9:26:53 AM - Mani Jacob Challeyil Back To Top

Hi 

Is there any way we can get Pagination for the powerview reports ?

 


Tuesday, January 28, 2014 - 11:08:32 PM - Sai Krishna P Back To Top

Hi,

When I create multiple Power Views, the size of the file is growing large [Since Database is having huge amount of data],  not an ideal scenario to share with any of the end users.

Any pointers how I can share the Power view template to end users via Email or a SharePoint link in the email , so that sheet should start fetching data on end user machine

Thanks in advance.


Saturday, November 16, 2013 - 11:03:36 PM - Arshad Back To Top

Hi Alain,

These links might be helpful for you.

http://technet.microsoft.com/en-us/library/hh213579.aspx

http://technet.microsoft.com/en-us/library/hh759325.aspx

http://technet.microsoft.com/en-us/library/hh759324.aspx


Saturday, November 16, 2013 - 12:12:10 AM - avinash reddy munnangi Back To Top

 

 

its great post 

awesome learning from your tips,i exactly looking for power view

 


Tuesday, November 05, 2013 - 2:46:55 AM - Alain Niz Back To Top

Good Job Mr Arshad Ali,

Where can I find the complete tutorials about powerview?

Documents or web sites?

 

Best regards


Wednesday, October 09, 2013 - 10:17:29 AM - Satish Back To Top

Hi Arshad,

Is the powerview can be used in open office also ??


Monday, October 07, 2013 - 10:30:40 PM - RAJ Back To Top

Thanks....Ali..!! for  and useful tips... 


Wednesday, August 28, 2013 - 10:37:11 AM - Arshad Ali Back To Top

Yes you can share it with customer on SharePoint site, please refer this tip for more details:

http://www.mssqltips.com/sqlservertip/3005/teach-business-users-how-to-create-power-view-reports-with-sql-server-data-sources/

You can also export to PowerPoint for presentation sharing.

 


Wednesday, August 28, 2013 - 9:20:43 AM - AnneR Back To Top

This is helpful, but how do we give this to our users\customers?

Can we make a PDF?

Can we put it on a web site?


Learn more about SQL Server tools