Adding Google Maps to reports

Friday 23 August 2013 @ 5:00 am

I feel like a kid with a new toy.  Ido Millet of Millet Software shared with me a method for taking any address in a report and using it to generate a map on that same report. The technique relies on a combination of two different features:

1) Crystal’s ability to show a dynamic image based on a path to that image
2) Google’s ability to generate a map image based on a URL with parameters

All you have to do is to write a formula that uses the address fields in a report to generate a Google compatible URL. He then set that URL to be the dynamic path for the image. The Crystal image object goes to that URL to fetch the image, which causes Google to generate the map image and send it back to the report. Here is a simple example of the formula:

"http://maps.google.com/maps/api/staticmap" &
"?center=Leesburg, VA" &
"&zoom=11&size=512x512" &
"&maptype=roadmap" &
"&markers=color:0x99FFFF|label:X|525 East Market St, Leesburg, VA" &
"&sensor=false"

Notice that Both the “Center” value and the address value in “Markers” are both hard coded, but could just as easily be formula fields.

Also note that you are not limited to one marker. Here is a slightly more sophisticated version with four formulas. The first is just the city state combined for use in the ‘center’ property. The second generates each single address. The third builds a marker string for each address and appends them into one string. The last combines everything to create the URL, including all of the markers:

// CityState
{Customer.CITY} & ', ' & {Customer.STATE}


//Address
{Customer.ADDRESS1} & ' ' & {@CityState} & ' ' & {Customer.ZIP}

//Markers Accum
WhilePrintingRecords;
StringVar Markers;
NumberVar Counter := Counter + 1;
Markers := Markers & "&markers=color:0x99FFFF|label:"
& Totext (Counter,0) & "|" & {@Address} ;
Counter

//Image URL
WhilePrintingRecords;
StringVar Markers;
"http://maps.google.com/maps/api/staticmap" &
"?center=" & {@Address CityState} &
"&size=512x512" &
"&visual_refresh=true" &
"&maptype=roadmap" & Markers &
"&sensor=false"

Once you have the last formula written you can use it to generate a map.  Drop any image on the report.  Right-click on that image and select Format Graphic and go to the Picture tab.  There you will find a condition button called “Graphic”.  Open that formula button and use the last formula field above as the entire formula.  When you preview the image should turn into a Google Map.

A couple of points:

I created a counter in the Accum formula so that the label for each marker has a unique number. If you want to use letters (A, B, C, etc) then replace this:

Totext (Counter,0)

with this:

CHR (Counter+64)

This changes 1 into A, 2 into B, etc.

Also, note that you can specify any color that you want. There are some basic colors you can specify by name (Red, Blue, Purple, etc), or you can pull up a color chart and specify an color code like I have above.

For more information on this you can refer to the Google Map API documentation:

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)






One Response to 'Adding Google Maps to reports'

  1. IdoMillet - September 2nd, 2013 at 3:46 pm

    Here is a link to an image showing what a Crystal report using this technique looks:
    http://screencast.com/t/Y0UF0fnwxJnN

    As the image shows, the same technique can be used to display QR Codes.


Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server