BI data on maps in SQL Server 2012 part 2
In my previous post http://blogs.msdn.com/b/querysimon/archive/2012/06/17/bi-data-on-maps-in-sql-server-2012.aspx I described how to create a Reporting Services report that contains custom map data.
One of the drawbacks with the mapping component in SSRS is that you cannot zoom interactively in your reports. One way to solve this shortcoming is to use linked reports that uses parameters to drill down to a sub report that contains data on a more granular level.
In my first post I created a report that contains a map that shows the world.
One thing that I have done in this report is that I have changed the Map Viewport properties to use a different projection. This is to ensure that the map does not get distorted. For more information about the projection to use there is information in the help file http://msdn.microsoft.com/en-us/library/ee240813.aspx
I have also changed the resolution setting of the map to ensure that do not get an error message that says “The number of map point elements for map ’Map1’ exceeds the maximum limit for the map. The remaining points do not appear in the map.” More information on this problem can be found on the following page: http://technet.microsoft.com/en-us/library/ee240843.aspx#Spatial
In the Reporting Services report I have also defined some properties on the Polygon layer. One is to show the Continent name as the tool tip for the shape.
Map Polygon properties dialog
I have also specified that an action exists on each polygon. This action points to a second report that contains the countries and has the continent as a parameter.
Map Polygon action
The second report looks like this:
As you can see it takes a continent as the parameter. This is the information that is sent from the first report and is used to filter the second one.
One problematic thing when you would like to build reports like this is that by default the zoom level and the zoom center will be off when you change the continent
Report zoom level and center
The reason for this is that the map for Europe as an example contains information that is spread all over the globe, as an example there are islands in the West Indies that belong to France. One solution to this would be to remove these elements from the map. If this is not possible you can change the map view center and zoom options.
I have done this in functions so that the zoom options changes based on the continent that I would like to show:
Center and zoom options
My code for the View center X percent is the following:
=IIf(Parameters!continent.Value="Europe", 65, (IIf(Parameters!continent.Value="Asia", 50, (IIf(Parameters!continent.Value="North America", 30,(IIf(Parameters!continent.Value="Oceania", 85,50)))))))
For the View center Y percent I have specified the following:
=IIf(Parameters!continent.Value="Europe", 40, (IIf(Parameters!continent.Value="Asia", 50, (IIf(Parameters!continent.Value="North America", 41, 50)))))
For the zoom level I have specified the following code
=IIf(Parameters!continent.Value="Europe", 200, (IIf(Parameters!continent.Value="Asia", 130, (IIf(Parameters!continent.Value="North America", 200, (IIf(Parameters!continent.Value="Oceania", 300, 120)))))))
By using this code I get a map that contains a picture that looks ok for each continent in the with the appropriate zoom level. This is the results for the report
Finished country report
Hopefully I have in these two posts shown you how you could use Reporting Services in SQL Server 2012 to display map data for nice looking reports. If you need more advanced maps with real zooming and panning I would recommend that you take a look at either 3rd party tools available on the market, or have a look at the spatial projects available on Codeplex http://www.codeplex.com/site/search?query=spatial&ac=8
By the way for those of you that would like to download the reports I have attached them to the post.