Tuesday, February 21, 2012

SSRS: World Maps for Reporting Services

The map visualization tool in Reporting Services 2008 R2 is a great feature that introduces multiple ways of visualizing data on a map. Some of the possibilities are:

  • Coloring countries based on some data
  • Showing bubbles or markers over countries that vary in color, shape or size based on data
  • Marking exact coordinates on the map based on geospatial data
Figure 1 - Example of USA map, where color of states depict sales value and size of circles represents count (or importance) of customers
Unfortunately however, Reporting Services only comes with a USA map and its states. 

Figure 2 - USA maps only
Surely there will be the need for maps of other geographical locations, most importantly the whole world. I would suggest the following 2 places for this:

  1. MapGallery on Codeplex (http://mapgallery.codeplex.com/).
    Besides having the world map, there are also other geographical locations.

    How to use: Maps downloaded from this location are in .RDL format. To be able to use the map from Report Builder/Business Intelligence Development Studio on your workstation, move the file to the MapGallery folder (typically "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery\" for Business Intelligence Development Studio and "C:\Program Files\Microsoft SQL Server\Report Builder 3.0\MapGallery" for Report Builder 3.0). If you have a Report Server and want anyone that loads the Report Builder 3.0 through the ClickOnce, then the map must be in the server, typically in "C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\ReportingServices\ReportServer\ReportBuilder\RptBuilder_3\MapGallery".

    The map will now be listed in the map gallery (see Figure 2).

    Downside: I found that the world map from this site contains missing geographical locations unfortunately.

  2. World Countries Map from Blue Marble Geographics ( http://www.bluemarblegeo.com/products/worldmapdata.php?op=download)

    How to use: They offer a free shapefile of the world map. Once the archive is downloaded, you can extract it locally or upload the files on the report server using the report manager so anyone can use it. Then when creating a new map, you choose the "ESRI shapefile" (2nd option in Figure 2) and browse to the SHP file.

    This map I have so far found more detailed.

Finally you can visualize data on a world map.

Figure 3 - Some sample data over a world map using AdventureWorksDW
Tip: Country names/codes, continents and states in your data must exactly match with the ones found in the map data. If you are using an RDL file for the map, you can edit the file in WordPad and modify the names to fit your data. For SHP files, you probably need an SHP editor, otherwise you'd have to change your data to fit the map data. From my experience, I found common mismatches such as United States of America and USA, United Arab Emirates and Utd. Arab Emirates, and so on.

Tags: reporting services ssrs report builder maps map gallery esri shapefile import