THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Excel 2013 Power View Maps

If you know how to use Excel, you can be productive in minutes with the new features of Excel 2013. Don’t be intimidated. Follow these simple steps and produce something snazzy!

The Excel file used in this example comes from the following SQL Server query which was run against the AdventureWorks2012 database:

SELECT Purchasing.Vendor.Name, Person.Address.City, Person.StateProvince.Name AS State
FROM   Purchasing.Vendor
INNER JOIN Person.BusinessEntityAddress
ON Purchasing.Vendor.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID
INNER JOIN Person.Address
ON Person.BusinessEntityAddress.AddressID = Person.Address.AddressID
INNER JOIN Person.StateProvince
ON Person.Address.StateProvinceID = Person.StateProvince.StateProvinceID

You can create your own data. Just create a simple, three column table like the one shown in the screen captures below. You only need a few lines of data. There’s no need to use the Excel file I used, but if you want to use it, it is attached at the bottom of this post and you can download it.

The reason I showed the query to create the example Excel file is because future posts will use those tables.

Click the INSERT tab and then click Power View.


Figure 1. Click Power View on the INSERT tab of Excel 2013.

If you haven’t previously enabled the Power View add-in, click Enable when you see the following prompt:


Figure 2. Click Enable to enable the Power View add-in.

Now you are ready to design your first Power View report.


Figure 3. Excel 2013 Power View Design view.

I added a title of Vendor Locations. This changed the active tab from DESIGN back to POWER VIEW and also reset the focus from the table to the title. After entering the title, reset the focus to the table by clicking it. Then go to the Power View Fields and uncheck the State and Vendor Name columns.


Figure 4. Power View after adding a title and unchecking State and Vendor Name from the Range.

You’re now ready to make your first map. Click the DESIGN tab and then click Map on the DESIGN toolbar. You’ll see a privacy warning yellow bar informing you that you need to have your data geocoded by Bing. Click the Enable Content button in the yellow bar to allow this.


Figure 5. Click Map to make a map showing where the vendors are located.

Use the handles to resize the map.


Figure 6. Map showing the cities where the vendors are located.

You can use the Filters pane to restrict the cities on the map. Click City to expand it and choose the cities you want to display. In this example, only cities that end with a Y are displayed. Notice that by moving the mouse over a dot on the map, the name of the city is displayed.


Figure 7. Map with a filter applied.

There’s much more to learn about Power View, but as you have seen here, you can easily get impressive results in only a few minutes.

Published Saturday, November 24, 2012 8:54 PM by John Paul Cook


Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



CFRandall said:

Fun post! Thanks for the walkthrough.

November 26, 2012 6:54 PM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement