Mapping in SQL Server Reporting Services

n honor of William Vaugh showing up at our local .NET user group, I am posting a SSRS article about how to add maps to your SQL Server Reporting Service reports. Google Map has static mapping functionality in their API set. Static maps are the ability to call a URL on the Google server and get back a single image of the map. This is different then using the Ajax objects in your web page, since those objects need the javascript engine to excute. With SSRS you don't get a javascript engine and you can't be guareenteed that it will be a web page, the export might be a PDF. Google Maps API for static maps takes a Query string like this example: http://maps.google.com/staticmap?center=40.714728,-73.998672&zoom=14&size=512x512&maptype=mobile\&markers=40.702147,-74.015794,blues%7C40.711614,-74.012318,greeng%7C40.718217,-73.998284,redc\&key=MAPS_API_KEY With good eyes you can see that it sets the center, zoom, and some markets on the map. First thing to do is drop the zoom and center, the map will auto center with the markers. Here are the steps: 1) Create a new Report in SSRS, try to avoid the wizard, however if you can't use some Random T-SQL to get past it. 2) Delete the table or matrix the wizard created for you. 3) Create a Image, using a Web image, and add the URL above. 4) Create a new Data Set, Here is the T-SQL I used to create my markers:

DECLARE @Markers varchar(max) 
DECLARE @latitude float 
DECLARE @longitude float 

SET @Markers = 'markers=' 

DECLARE _cursor CURSOR FOR 
SELECT DISTINCT TOP 50 Latitude, Longitude 
FROM MapTable 
WHERE (NOT Latitude IS NULL) AND (NOT Longitude IS NULL) 

OPEN _cursor 
FETCH NEXT FROM _cursor 
INTO @latitude, @longitude 

WHILE @@FETCH_STATUS = 0 
BEGIN 

IF ((NOT (@latitude IS NULL)) AND (NOT (@longitude IS NULL))) 
SET @Markers = @Markers + CONVERT(varchar(max),@latitude) + ',' + CONVERT(varchar(max),@longitude) + ',tinyred%7C' 

FETCH NEXT FROM _cursor 
INTO @latitude, @longitude 

END 

CLOSE _cursor 
DEALLOCATE _cursor 

SELECT @Markers AS Marker 

5) Click on the Image in your preview window and open the properties. Change the Value from the static URL to this dynamic URL: ="http://maps.google.com/staticmap?size=640x640&maptype=mobile&" + Fields!Marker.Value + "&key=YourKey" Notes: - Google Static Map API only allows you to map 50 markers, that is why the SELECT is TOP 50 - There are two property windows for SSRS reports, you need the one that is dockable, not the pop-up property dialog. The Value property is only in the dockable one. Use the tool bar, choose View, Then Property Window. - You will need to insert your Google Key, note that with static image it isn't related to the web site the image on, since the image might be in a PDF, it is just a way to track your usage. More Info: http://code.google.com/apis/maps/documentation/staticmaps/ {6230289B-5BEE-409e-932A-2F01FA407A92}