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.

Finished report

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

Projection dialog

 

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

Optimization dialog

 

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:

Second report

 

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.

 

 

sample_map_reports.zip

Comments (8)

  1. Richard Lautmann says:

    Nice post Simon. One more thing I found useful was that ability to remove parts of the map, and just show the countries that is of importance. In some cases, it could mean you actually don't need to drill to another report.

    An example: mybinote.blogspot.se/…/httpblogs.html

  2. Graham Angell says:

    Very good post and easy to follow.  I knew nothing about map functionality in SSRS before I started reading this and now I am well on my way to creating my first map report.  Thanks.

  3. Great to hear that I could help and good luck with your reporting

  4. Tim says:

    Hey Simon,

    Have you ever tried putting a pair of Bar Graphs displaying say sales for a current month and for the previous month over spatial locations? Would you have any tips on how I could do such a thing?

  5. Ravi says:

    we have developed a map report, one with olap cube(multidimensional) data source and same  map report with olap cube(tabular modeling)  data source. we don't have problem with multidimensional, but while taking data souce as olap cube(tabular modeling)  map report not generating it's throwing an error. it's saying that stampfp column data type not matched

  6. Simon Lidberg says:

    Tim I believe that this would require a custom mapping  component there are a couple on codeplex

  7. Mohamed says:

    Hi Simon, I have uploaded the sample_map_reports.zip .rdl files into my home page but the connection string is set to Data Source=.;Initial Catalog=Spatial. Does the report requires a special spacial database?

    Thanks

    Mohamed

  8. Simon Lidberg says:

    Hi Mohamed, the database used in this example is the one that was created in the following blog post blogs.msdn.com/…/bi-data-on-maps-in-sql-server-2012.aspx