SQL Server 2008 R2 Reporting Services - The Word is But a Stage (T-SQL Tuesday #006)
- by smisner
Host Michael Coles (blog|twitter) has selected LOB data as the topic for this month's T-SQL Tuesday, so I'll take this opportunity to post an overview of reporting with spatial data types. As part of my work with SQL Server 2008 R2 Reporting Services, I've been exploring the use of spatial data types in the new map data region. You can create a map using any of the following data sources:
Map Gallery - a set of Shapefiles for the United States only that ships with Reporting Services
ESRI Shapefile - a .shp file conforming to the Environmental Systems Research Institute, Inc. (ESRI) shapefile spatial data format
SQL Server spatial data - a query that includes SQLGeography or SQLGeometry data types
Rob Farley (blog|twitter) points out today in his T-SQL Tuesday post that using the SQL geography field is a preferable alternative to ESRI shapefiles for storing spatial data in SQL Server. So how do you get spatial data? If you don't already have a GIS application in-house, you can find a variety of sources. Here are a few to get you started:
US Census Bureau Website, http://www.census.gov/geo/www/tiger/
Global Administrative Areas Spatial Database, http://biogeo.berkeley.edu/gadm/
Digital Chart of the World Data Server, http://www.maproom.psu.edu/dcw/
In a recent post by Pinal Dave (blog|twitter), you can find a link to free shapefiles for download and a tutorial for using Shape2SQL, a free tool to convert shapefiles into SQL Server data.
In my post today, I'll show you how to use combine spatial data that describes boundaries with spatial data in AdventureWorks2008R2 that identifies stores locations to embed a map in a report.
Preparing the spatial data
First, I downloaded Shapefile data for the administrative boundaries in France and unzipped the data to a local folder. Then I used Shape2SQL to upload the data into a SQL Server database called Spatial. I'm not sure of the reason why, but I had to uncheck the option to create a spatial index to upload the data. Otherwise, the upload appeared to run successfully, but no table appeared in my database.
The zip file that I downloaded contained three files, but I didn't know what was in them until I used Shape2SQL to upload the data into tables. Then I found that FRA_adm0 contains spatial data for the country of France, FRA_adm1 contains spatial data for each region, and FRA_adm2 contains spatial data for each department (a subdivision of region).
Next I prepared my SQL query containing sales data for fictional stores selling Adventure Works products in France. The Person.Address table in the AdventureWorks2008R2 database (which you can download from Codeplex) contains a SpatialLocation column which I joined - along with several other tables - to the Sales.Customer and Sales.Store tables. I'll be able to superimpose this data on a map to see where these stores are located. I included the SQL script for this query (as well as the spatial data for France) in the downloadable project that I created for this post.
Step 1: Using the Map Wizard to Create a Map of France
You can build a map without using the wizard, but I find it's rather useful in this case. Whether you use Business Intelligence Development Studio (BIDS) or Report Builder 3.0, the map wizard is the same. I used BIDS so that I could create a project that includes all the files related to this post. To get started, I added an empty report template to the project and named it France Stores. Then I opened the Toolbox window and dragged the Map item to the report body which starts the wizard. Here are the steps to perform to create a map of France:
On the Choose a source of spatial data page of the wizard, select SQL Server spatial query, and click Next.
On the Choose a dataset with SQL Server spatial data page, select Add a new dataset with SQL Server spatial data.
On the Choose a connection to a SQL Server spatial data source page, select New.
In the Data Source Properties dialog box, on the General page, add a connecton string like this (changing your server name if necessary):
Data Source=(local);Initial Catalog=Spatial
Click OK and then click Next.
On the Design a query page, add a query for the country shape, like this:
select * from fra_adm1
Click Next. The map wizard reads the spatial data and renders it for you on the Choose spatial data and map view options page, as shown below.
You have the option to add a Bing Maps layer which shows surrounding countries. Depending on the type of Bing Maps layer that you choose to add (from Road, Aerial, or Hybrid) and the zoom percentage you select, you can view city names and roads and various boundaries. To keep from cluttering my map, I'm going to omit the Bing Maps layer in this example, but I do recommend that you experiment with this feature. It's a nice integration feature.
Use the + or - button to rexize the map as needed. (I used the + button to increase the size of the map until its edges were just inside the boundaries of the visible map area (which is called the viewport). You can eliminate the color scale and distance scale boxes that appear in the map area later.
Select the Embed map data in this report for faster rendering. The spatial data won't be changing, so there's no need to leave it in the database. However, it does increase the size of the RDL. Click Next.
On the Choose map visualization page, select Basic Map. We'll add data for visualization later. For now, we have just the outline of France to serve as the foundation layer for our map. Click Next, and then click Finish.
Now click the color scale box in the lower left corner of the map, and press the Delete key to remove it. Then repeat to remove the distance scale box in the lower right corner of the map.
Step 2: Add a Map Layer to an Existing Map
The map data region allows you to add multiple layers. Each layer is associated with a different data set. Thus far, we have the spatial data that defines the regional boundaries in the first map layer. Now I'll add in another layer for the store locations by following these steps:
If the Map Layers windows is not visible, click the report body, and then click twice anywhere on the map data region to display it.
Click on the New Layer Wizard button in the Map layers window.
And then we start over again with the process by choosing a spatial data source.
Select SQL Server spatial query, and click Next.
Select Add a new dataset with SQL Server spatial data, and click Next.
Click New, add a connection string to the AdventureWorks2008R2 database, and click Next.
Add a query with spatial data (like the one I included in the downloadable project), and click Next.
The location data now appears as another layer on top of the regional map created earlier. Use the + button to resize the map again to fill as much of the viewport as possible without cutting off edges of the map. You might need to drag the map within the viewport to center it properly.
Select Embed map data in this report, and click Next.
On the Choose map visualization page, select Basic Marker Map, and click Next.
On the Choose color theme and data visualization page, in the Marker drop-down list, change the marker to diamond. There's no particular reason for a diamond; I think it stands out a little better than a circle on this map. Clear the Single color map checkbox as another way to distinguish the markers from the map.
You can of course create an analytical map instead, which would change the size and/or color of the markers according to criteria that you specify, such as sales volume of each store, but I'll save that exploration for another post on another day.
Click Finish and then click Preview to see the rendered report. Et voilà...c'est fini.
Yes, it's a very simple map at this point, but there are many other things you can do to enhance the map. I'll create a series of posts to explore the possibilities.
Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
