Cognos 10 and google maps 1: add a map with markers with cognos data to a report

Here are the steps:

QUERY

Create a new report with a query that returns at least the states data.  you can also include the address if you have it.   the sample below only includes the city, state and country.  I am using a js file and the content has been added to the end of this post.   

here is the final query that was built:

I will explain the fields:

     From Database:

             Awardee_City_NM:  the name of the city   

             Awardee_State_CD: State code or name

             Awardee_Zip_CD:  zip code

             Key:  unique id of the Awardee

     User Created:

             ElementSeparator:  simple formula to add a comma to the array of address

             Data Item1:  count field (in sql terms and identity)

             Data Item2:  total count of rows

             Lat: not used in this sample.

             Long: not used in this sample

            

After building a simple query of the address information add the following fields:

ElementSeparator, Data Item1, Data Item2.

Data Item1 field is a simple incremental column of the data.

              running-count (1)

Data Item2 field is the total count of the data.

        count ([KEY]for report)

ElementSeparator field is a simple if statement to add the comma or not. 

                  IF ( [Data Item1] = [Data Item2]) THEN

                 ( '' )

                 ELSE

                 ( ', ' )

REPORT

Now we have everything we need to create the report with markers.

Add the following to the report page in this order:

HTML control, Repeater (add HTML control inside), HTML control

• the first HTML control will have our script tag to build the javascript.  script below.  put your key that you got from google and replace  your_key_here with your key.   Make sure the source type is Text.

<script type="text/javascript" src="http://maps.googleapis.com/maps/api/js?key=your_key_here&sensor=false"></script>

<script  type="text/javascript" src="http://localhost/googlescripts/maps.js"></script>

<div id="map" style="width: 800px; height: 600px"></div>

<script  type="text/javascript">

var addresses =[

• the repeater will need to be associated to our query.  We need to set the rows per page to be very high so that we get all the data to display on our map.   Also select all the fields for the properties or just the ones that we are going to need like everything but the  lat and long fields. 

• HTML control inside the repeater.  Make sure the source type is Report Expression.

'"''' + [Query1].[AWARDEE_CITY_NM]  + ''',''' + [Query1].[AWARDEE_STATE_CD] + ''',''USA''"'  + [Query1].[ElementSeparator]

           

• The third HTML control contains the function call to the js file below.   Make sure the source type is Text.

];
LoadMapAndAddMarker(addresses, 'map', 5);

</script>

RUN the report and see your map with markers on your address.  If you want to add the address line just add it to the HTML control before the city data.  

Have fun!

This is the maps.js file content:

function LoadMapAndAddMarker(addresses, maptag, zoomlevel)

{

var address;

var latlng = new google.maps.LatLng(38.5, -98.35);

var options = {

center : latlng,

zoom : zoomlevel,

mapTypeId : google.maps.MapTypeId.ROADMAP

};

// Creating the map

var map = new google.maps.Map(document.getElementById('map'), options);

var geocoder = new google.maps.Geocoder();

//function AddMarker(address) {

for (var i = 0; i < addresses.length; i++) {

address = addresses[i];

geocoder.geocode( {'address' : address}, function(results, status) {

if (status == google.maps.GeocoderStatus.OK) {

//map.setCenter(results[0].geometry.location);

var marker = new google.maps.Marker( {

map : map,

position : results[0].geometry.location

});

AddclickInfoWindowListener(marker, results[0].formatted_address, 'click', map, i);

}

});

//}

}

}

function AddclickInfoWindowListener(marker, address, action, map, index){

var infowindow;

if (!infowindow) {

infowindow = new google.maps.InfoWindow();

}

infowindow.setContent(address);

google.maps.event.addListener(marker, action, function()

{ infowindow.open(map, marker); });

}