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:

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.

Continues…

Pages: 1 2




Array

One Response to “Reporting Services 2008 R2: Geospatial Visualization – Part I”

  1. Any idea why I do not have the map option in the report item?

    I am using MS Visual Studio 2008 V. 9.0.30729.1 SP – .NET Frame Work V. 3.5 SP1

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |