Reporting Services 2008 R2: Geospatial Visualization – Part I
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:
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.
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
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.
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.
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
- 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
Microsoft Business Intelligence Development Studio and create a new Report
Server Project. Note that this report can also be created with Report Builder
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.
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:
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
analytical data now even though it could be added later. Select Color Analytical Map
and click Next.
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
Click OK and then Next to continue.
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
INNER JOIN Sales.SalesTerritory
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 .
Pages: 1 2