By accident I heard one of our sales people talk about showing data from a geographical location on a report. I immediately jumped in the discussion and told about the new Spatial datatype of SQL server and the reporting data region Map. Of course when talking to Sales people the immediatly asked for a demo. In this blog post my findings about Spatial data, Reporting map control and Bing maps. My starting point was Robert Brucker’s reporting blogpost, and try to recreate his demo. Had it up and running in no time.
The first thing i had to do was create a Geography data type, since this is the base of the Reporting map control. This was pretty easy since i had some test data with Latitude and Longitude as float in a table. All i had to do to converting these to a geography datatype was:
update Location set GeoPostion = geography::Point(Latitude, Longitude, 4326)
There are several ways of different ways to add point data in SQL Server 2008, i found them at this blog post: http://blog.colinmackay.net/archive/2008/02/07/1812.aspx
Next up was creating some kind of representable chart, i used the steps as described by Robert Brucker to create a report of my data, the one thing that immediatly caught my attention is that the Reporting map control using Bing maps is data aware. The Bing map automatically centers and zooms in at my locations (i used the Person table from Adventure Works to shoot this screenshot, as there are persons from around the globe he centers on the world). I created a view of the data so i can change its source on a later stage:
Next up was choosing the visualization of the data, i wanted to create a analytical overview so i chose Bubble map:
Since i wanted to show some analytical data i changed the query to point to my Dutch data and added an amount field (which i filled ranomly with values 15 to 2000) using a top 50 of the Netherlands, you can see the data awareness again, the map switched to the Netherlands only.
In the data visualization step i selected the Bubble size property along with a colour scheme to represent my data:
Resulting in the following map in reporting services, so no programming necessary (except the Query of course):
It would be great if users could zoom in on the data .. luckily the data is a layer in reporting services so the data points are reporting objects where you can set properties like an Action:
So i turned my view into a stored procedure with a parameter and using the data awareness of the reporting control i called my own report with a reporting parameter that selected the top 50 in a region (the Id they clicked)
Using the same report for master and detail, this results in the following map when clicked:
Conclusion: Reporting with geographical data has become very very easy and offers some great possibilities to report developers.