Geospatial Visualization is one of the key new features of SQL Server 2008 R2 Reporting Services.SQL Server provided geospatial data with SQL Server 2008 and you may have noticed (or perhaps used) the two geospatial data types in SQL Server 2008 : geometry and geography. This support is further extended with Reporting Services 2008 R2 and you can now create Map-Report with the Map Report item which we will demonstrate in this article.
In this article we will examine the following:
- Map report item and sources for spatial data
- Map layers
- Creating a Map Report using shapefiles
- Adding analytical data onto Map Layer
Map Report Item and Sources for Spatial Data
The Map Report Item helps us to combine geospatial data with data to be analyzed, allowing us to set the geospatial data as the background. Spatial data can be provided to the Map Report Item in three ways:
1. Map Gallery Reports
Map Gallery reports are a set of reports installed by Reporting Services . These reports are embedded with spatial data. By default, these reports are stored in <drive>:Program FilesMicrosoft Visual Studio 9.0Common7IDEPrivateAssembliesMapGallery. These reports can be used as the source for maps and will be embedded in the report you create.
2. ESRI Shapefiles
ESRI, Environmental Systems Research Institute, Inc. shapefiles are files that have the spatial data format files such as .shp, .dbf, and .shx. Reporting Services does not require all these types of files when using them as the source for spatial data. It needs only a .shp file that contains geographical or geometrical shapes, and .dbf file that contains attributes for the shapes. If you use shapfiles for drawing the map, you need to make sure that both files are in the same folder.
3. SQL Server spatial data stored in a database
If you have stored spatial data in a geography or geometry data type column in your SQL Server database, you can use them as the source for maps.
Map Layers
Reporting Services maps are layered elements. A Map Report Item can have one or more layers. Each layer can contain spatial data used to draw the map, analytical data, and other properties such as color and size. There are mainly four types of layers:
- Polygon – used to show geographic areas such as countries or states.
- Line – used to show paths and routes.
- Point – used to show specific geographic places.
- Tile – used to display Bing maps in reports.
The initial map layer can be added to the Map Report Item through the Map Wizard. If more layers are required, you can use the New Layer Wizard.
Creating the first Report
If you look at the image above, you will see that first report titled “Sales by Country” shows a world map with several countries in color. Basically, it shows where the company has made sales. Note that this is based on the AdventureWorks2008R2 database (if required it can be downloaded here). Let’s look at a step by step implmentation.
1. Open Microsoft Business Intelligence Development Studio and create a new Report Server Project. Note that this report can also be created with Report Builder 3.0 .
2. Add a new report. Do not use Report Wizard because it does not support creating maps, instead use Report. Name the report SalesByCountry.rdl.
3. Open the toolbar and double click the Map report item. This adds a Map Report Item to the report and opens the New Map Layer wizard. The first page of the wizard is for setting the source for spatial data. There are three options: Map gallery, ESRI shapefiles, SQL Server spatial query.
Since Map Gallery does not contain a report that has a world map, we need to use a shapfile for drawing it. The file used for the demonstration was downloaded from http://www.vdstech.com/map_data.htm (Thanks for Pinal Dave, publishing this address in his post). Go to the site and download world.zip, place the extracted files in one folder. You should see three files: world.dbf, world.shp, and world.shx.
Now select the second radio button and browse to the world.shp file you just downloaded. Once browsed and selected, your screen should be as below:
Click Next to continue.
4. Review the settings in Choose data and map view options . Click Next .
5. There are three map visualizations under Choose Map Visualization. If you select either Color Analytical Map or Bubble Map, you will need to provide a data set that contains data to be analyzed. If you select Basic Map, you can continue without providing a data set. For this demonstration will will add analytical data now even though it could be added later. Select Color Analytical Map and click Next.
6. In Choose the analytical dataset , select the second radio button Add a dataset that includes fields that relate to the spatial data that you chose earlier and click Next .
7. On Choose a connection to a data source , click New to create a data source for analytical data.
8. Name the data source AdventureWorks2008R2 and set the connection to the AdventureWorks2008R2 database.
Click OK and then Next to continue.
9. Design a query window is open. Paste below query and click on Next. The query returns the country code and total sales for each country.
SELECT t.CountryRegionCode, sum(h.TotalDue) TotalDue
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesTerritory t
ON h.TerritoryID = t.TerritoryID
GROUP BY t.CountryRegionCode
10. On the Specify the match fields for spatial and analytical data screen. This allows us to specify the relationship between the spatial data loaded from world.shp file and the data loaded from the query in the previous step. The first grid on the screen allows you to set links between the two data sources. The second grid shows the columns in the spatial data set, highlighting the column we use to match. The third grid contains data from the query, again highlighting the column we use to match. Make the link as below:
If you need to see spatial data columns and data when the report is complete, the easiest way is opening the code of the report through Solution Explorer. Right click on the report in the project and select View Code. This opens the RDL file of the report. If spatial data is embedded in the report there will be a node named MapFields which contains the data .
Click Next to continue.