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}
Posts
