THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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.

image

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:

image

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

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

image

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.

image

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.

image

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

Use the handles to resize the map.

image

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.

image

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

Attachment(s): PowerViewData.zip

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

Comments

 

CFRandall said:

Fun post! Thanks for the walkthrough.

November 26, 2012 6:54 PM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Opinions expressed in John's blog are strictly his own and do not represent Microsoft in any way.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement