Geospatial mapping made simple with Microsoft SQL Server

Part 1 | Part 2 | Part 3

One of the most exciting features with Report Builder 3.0 is the new map functionality that lets you add maps made up of one or more layers that together display spatial and analytical data. The maps can also integrate with Microsoft Virtual Earth to provide a data visualization tool that is both interesting and informative.

Although you can create a map that contains only spatial data, the most effective maps are those that include analytical data as well. For example, you might want to create a map that shows the total amount of sales in each county in a U.S. state. The sales amounts would represent the analytical data and the mapped county locations would represent the spatial data.

A Report Builder map is made up of one or more layers. Each layer can be configured as one of the following types:

• Polygon -- Displays outline areas for regions such as cities, states, or countries.
• Point -- Displays specific points on a map.
• Line -- Displays paths or routes between points.
• Tile -- Provides a background that consists of a Microsoft Virtual Earth (Bing) map.

When you create a map, you do so by adding one layer at a time. You can then configure each layer to meet your specific needs. Also, the configuration options tend to vary from one layer type to the next, although there are a number of similarities.

What else can you do with Report Builder 3.0? Check out Robert Sheldon's two previous series on adding charts and gauges to SSRS reports.

In this series I will demonstrate how to create a map that includes a polygon layer, point layer, and tile layer with Report Builder 3.0. The map will display the continental United States and the locations of sales people with over $1 million in sales. In addition to the three layers, the map will include the legends, tooltips, and color-coding necessary to show the amount of sales for each person and how those sales compare to one another.

Setting up the data source and dataset

To demonstrate how to create this map, I first created a report and added a data source and data set. The data source connects to the AdventureWorks2008R2 sample database on a local instance of SQL Server 2008 R2. I named the data source the same as the database.

I installed a local instance of the November 2009 CTP release of SQL Server 2008 R2 on a Windows Server 2008 machine. The installation includes the database engine and SQL Server Reporting Services (SSRS). I also used the AdventureWorks sample databases provided for the SQL Server 2008 R2 release and installed the November CTP release of Report Builder 3.0 on the same server.

After I set up the data source, I created a dataset named SalesPeople. The dataset uses the following Transact-SQL statement to retrieve data through the AdventureWorks2008R2 data source:

SELECT
  (p.FirstName + ' ' + p.LastName) AS FullName,
  a.City,
  RTRIM(st.StateProvinceCode) AS StateProvinceCode,
  sp.SalesYTD,
  a.SpatialLocation
FROM
  Sales.SalesPerson sp
  INNER JOIN Person.Person p
     ON sp.BusinessEntityID = p.BusinessEntityID
  INNER JOIN Person.BusinessEntityAddress bea
     ON bea.BusinessEntityID = p.BusinessEntityID
  INNER JOIN Person.Address a
     ON a.AddressID = bea.AddressID
  INNER JOIN Person.StateProvince st
     ON st.StateProvinceID = a.StateProvinceID
WHERE
  p.PersonType = 'SP'
  AND st.CountryRegionCode = 'US'
  AND SalesYTD > 1000000;

The query retrieves a list of sales people in the U.S. who have over $1 million in sales. In addition to retrieving the names of the sales staff, I retrieved the city, state, total sales, and spatial location for each sales person. The spatial data is retrieved from the SpatialLocation column, which is configured with the geography data type, one of the new SQL Server 2008 spatial data types. Also, notice that I used the RTRIM function to trim the StateProvinceCode column. Later on, you'll see why it is necessary to remove the trailing spaces.

After you've created a report and set up the data source and dataset, you're ready to add a map to your report. Note that while a map is often only one element among other elements in a report, here I will focus only on adding the map. To learn about adding other elements to a report or for details about setting up a data source or dataset, see the Report Builder 3.0 Help or check out my previous articles on working with gauges and Charts.

Continue to part two

ADDING MAPS IN REPORT BUILDER 3.0
 Part 1: Geospatial mapping
 Part 2: Adding a polygon layer
 Part 3: Finalizing maps 

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at www.rhsheldon.com.