Geospatial mapping made simple with Microsoft SQL Server
The new spatial and analytic mapping functionality in Microsoft Report Builder is a great way to generate more dynamic SSRS reports – once you know how to create them.
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.
 |
||||
|
|
|||
|
||||
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.
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.
Start the conversation
0 comments