How To Display Data From a Cognos 8 Report in Google Maps


IBM’s Cognos 8 Report Studio already has built-in maps that allow you to create geographic representations of your data, but they are not without limitations, the most notable being that there are only about a half dozen maps included.   The world-level maps contain every location, but not down to the level of granularity you often need (zip-code level for example).  There are U.S.-only maps that go down to the zip code, but they don’t include U.S. Posessions (ex: Puerto Rico) You can develop custom maps, but the software required to generate the MapInfo files is quite expensive and outside of the range of many project budgets.

One solution is to integrate your reports with Google Maps via the Google Maps API.  The API is capable of marking any location in the world, by a variety of means, including zip-codes, city names, or even latitude and longitude.  The interactivity is about as intuitive as it gets, and you can add markers to highlight specific map points or polygons to outline and color areas of any shape (a state for example)

This article follows-up on the example provided by Rayudu Vecha and Sridhar Munireddy in their developerWorks article; Integrate Google Maps with Cognos 8.  In it, they explain how to call the Google Maps API from within a Cognos 8 Report Studio report and insert hard-coded markers.  Here, I will show you how to expand that to dynamically populate markers on top of the Google Map.

The basic technique is fairly simple.  I take the single HTML Item that Vecha and Munireddy use (enhanced a bit for my particular needs) and then divide it into four separate HTML Items, with one of them nested inside a repeater object to handle an unknown number of rows from a query. Optionally, the third HTML can be embedded inside a singleton to allow it to retrieve from the database a value that will be put into a JavaScript variable.

When the report is run, the four separate HTML items act as a single block of HTML code, but displayed within the Cognos 8 report viewer.

What You’ll Need:
  • A Cognos 8 Business Intelligence server
  • A Google Maps API Key  for the Cognos 8 BI server’s domain (directions for obtaining this are below)
  • Basic development skills for Cognos 8 Report Studio
  • A fundamental understanding of JavaScript
  • A database that contains the latitude and longitute of the locations you wish to mark on the map.

The Steps:
  1. You are going to need a Google Maps API key.

    1. Go to http://code.Google.com/apis/maps/signup.html
    2. Enter the domain name at which you access Cognos 8 (ex: http://cognos.mycompany.com)
    3. Click the Generate API Key button.This should create a key that looks similar to this:

      ABQIAAAAN28cRTOPRc8HwC3fHtsmKhTtWTE_GChODK0XVX0wNep29RbXARRXke_fSEx94NZZnhOisv8S0tZqVg
    4. Copy the API key displayed and save it for future use.
    5. If you can, store this key somewhere in your reporting database.  This will help if you plan to later migrate the report to another server (ex: from development to production)
  2. Open Report Studio and create a new report using the blank template.
  3. From the insertable objects toolbox, add a Repeater object to the body of the report.
  4. Right-click on the new repeater object and select Go To Query.
  5. Add to the query the Data Items you want to display.This data will be used to assemble a Javascript array (in a subsequent step).You will also need to include two columns containing the latitude and longitude of each marker.

  6. If you plan to have more than one row in the query (i.e. more than one marker on the map) you will need a column that contains a comma for all but the first row.  This comma will be used as a separator for the elements in the Javascript array.  To Do this:
    1. Add a Data Item to the query named Row. Set the expression to:

      rank ([Location])

      (replacing “[Location]” with a column you’ll be sorting on)

    2. Add another Data Item named ElementSeparator. Set the expression to:

      IF ( [Row] =  1 ) THEN
      ( ” )
      ELSE
      ( ‘, ‘ )


  7. Return to the page (Page1).
  8. Click on the repeater and edit the Data->Properties.Click check-boxes by each data item that you will want to use.  This will allow you to use them in a report expression.

  9. Change the repeater’s Data->Rows Per Page to 99999(otherwise, Report Studio will try to create a new page for every 20 rows in your repeater)

  10. You may also need to define the Data->Grouping and Sorting of the repeater.
  11. Add an HTML Item immediately to the left of the repeaterThis will contain the start of the Javascript

    1. The source type will be the default: Text
    2. Set the HTML text to the following:
      <html>

      <body>

      <head>

      <title>Static -> Dynamic Google Map with Markers</title>

      </head>

      <style type=”text/css”>

      .message {

      font-size: 80%;

      padding: 2px;

      font-weight: bold;

      text-align: center;

      width: 550px;

      height: 450px;

      background-color: #ffcc00;

      border: 1px solid white;

      position: relative;

      }

      </style>

      <script type=”text/javascript”>

      var locations = [


  12. Add a second HTML Item inside the repeaterThis will be the assembly of the array containing your data

    1. Set the source type to: Report Expression
    2. Set the report expression to the following, modifying it to fit your data:

      [Query1].[ElementSeperator] + ‘{name: ”’ +

      [Query1].[Location] + ”’, url: ”’ + [Query1].[URL] +  ”’, headcount: ‘ + number2string ( [Query1].[Headcount] ) + ‘, hires: ‘ + number2string ( [Query1].[Hires] ) + ‘, terminations: ‘ + number2string ( [Query1].[Terminations] ) + ‘, promotions: ‘ + number2string ( [Query1].[Promotions] ) + ‘, lat: ‘ + number2string ( [Query1].[Latitude] ) + ‘, lng: ‘ + number2string ( [Query1].[Longitude] ) + ‘} ‘

      The resulting data would look something like this (line breaks and indentation added for readability):

      {
      name: ‘Location 1’,
      url: ‘http://url.for.location.1&#8217;,
      headcount: 123,
      hires: 1,
      terminations: 2,
      promotions: 3,
      lat: 38.88944,
      lng: -77.035341

      } ,

      {

      name: ‘Location 2’,

      url: ‘http://url.for.location.2&#8217;,

      headcount: 456,

      hires: 7,

      terminations: 8,

      promotions: 9,

      lat: 43.878996,


      lng: -103.459811

      }



  13. Immediately to the right of the repeater, add a third HTML Item
    (Note: if you stored the API Key in your database, add a Singleton with a query that retrieves it and put the HTML Item inside the singleton, and use a report expression to define the text)

    1. The source type will be the default: Text
    2. Set the HTML text to the following:

      ];

      var strMapKey = ‘

      ABQIAAAAN28cRTOPRc8HwC3fHtsmKhTtWTE_GChODK0XVX0wNep29RbXARRXke_fSEx94NZZnhOisv8S0tZqVg‘;

      (substituting your own API Key)

  14. Add a fourth HTML Item immediately to the right of the third one.This will contain the end of (and the bulk of) the Javascript

    1. The source type will be the default: Text
    2. Set the HTML Text to the following:

      var map = null;

      var bounds = null;

      var currentMarker = null;

      var mapDiv = null;

      var containerDiv = null;

      var clickedX = 0;

      var clickedY = 0;

      var isLoaded = false;

      /**

      * Called after script is asynchronously loaded in.

      * Creates the GMap2, GMarker objects and performs actions according to

      * what the user did to trigger the map load (search, zoom, click etc).

      */

      function loadMap() {

      if (GBrowserIsCompatible()) {

      mapDiv.style.background = ‘#fff’;

      mapDiv.style.cursor = ”;

      map = new GMap2(mapDiv, {logoPassive: true});

      bounds = new GLatLngBounds();

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

      bounds.extend(new GLatLng(locations[i].lat, locations[i].lng));

      }

      var latSpan = bounds.toSpan().lat();

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      map.addControl(new GLargeMapControl());

      //map.setZoom(2);

      var mapControl = new GMapTypeControl();

      map.addControl(mapControl);

      var newBounds = map.getBounds();

      var newLatSpan = newBounds.toSpan().lat();

      if (latSpan/newLatSpan > .90) { map.zoomOut(); }

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

      var marker = createMarker(i);

      var latlng = marker.getLatLng();

      var pixel = map.fromLatLngToDivPixel(latlng);

      if (Math.abs(pixel.x -clickedX) < 12 && Math.abs(pixel.y -clickedY) < 20){

      //GEvent.trigger(marker, ‘click’);

      }

      map.addOverlay(marker);

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      }  } }

      function zoomToAll() {

      map.setCenter(bounds.getCenter(), map.getBoundsZoomLevel(bounds));

      }

      /**

      * Creates a marker for the given location.

      * @param {Number} ind

      * @return {GMarker}

      */

      function createMarker(ind) {

      var value= 103037;

      var location = locations[ind];

      var marker = new GMarker(new GLatLng(location.lat, location.lng));

      /**

      * When the user clicks on the marker while zoomed out, it zooms in (in case there is

      * another marker nearby that would not be visable when zoomed out)

      * When they click on the marker while zoomed in, it opens an Info Window that displays

      * details about the location

      */

      GEvent.addListener(marker, ‘click’, function() {

      marker.html = [‘<b>’, location.name, ‘</b>’,

      ‘<ul>’,

      ‘<li>Headcount: ‘, location.headcount, ‘</li>’,

      ‘<li>New Hires: ‘, location.hires, ‘</li>’,

      ‘<li>Terminations: ‘, location.terminations, ‘</li>’,

      ‘<li>Promotions: ‘, location.promotions, ‘</li>’,

      ‘</ul>’,

      ‘<a  href=”‘ + location.url + ‘”  target=”_blank “> Detail Listing</a>’].join(”);

      currentMarker = marker;

      if ( map.getZoom() <= 12 ) {

      map.setCenter( marker.getLatLng(), 15 );

      }

      else {

      marker.openInfoWindowHtml(marker.html);

      }

      });

      return marker;

      }

      /**

      * Formats location info into a URL-friendly version for maps url.

      * @param {Object} location

      * @return {String}

      */

      function formatAddressForMaps(location) {

      var address = location.street + ‘ ‘ + location.city + ‘ ‘ + location.state + ‘ ‘

      + location.zip;

      return escape(address.replace(‘ ‘, ‘+’));

      }

      function _cel(elementType, id) {

      var element = document.createElement(elementType);

      element.id = id;

      return element;

      }

      /**

      * Loads in the Maps API script. This is called after some sort of user interaction.

      * The script loads asynchronously and calls loadMap once it’s in.

      */

      function loadScript() {

      if (!isLoaded) {

      isLoaded = true;

      var div = document.createElement(‘div’);

      div.className = ‘message’;

      div.innerHTML = ‘Loading…’;

      div.style.left = (800/2 – 53) + ‘px’;

      div.style.top = 800/2 + ‘px’;

      mapDiv.appendChild(div);

      var script = document.createElement(‘script’);

      script.type = ‘text/javascript’;

      script.src = ‘http://maps.google.com/maps?file=api&v=2&#8217; +

      ‘&async=2&callback=loadMap&key=’ + strMapKey;

      document.body.appendChild(script);

      }

      }

      /**

      * Sets up the gadget by setting CSS and click events.

      */

      function loadMapGadget() {

      containerDiv = document.getElementById(‘container’);

      mapDiv = document.getElementById(‘map’);

      mapDiv.onclick = function (e) {

      clickedX = (window.event && window.event.offsetX) || e.clientX;

      clickedY = (window.event && window.event.offsetY) || e.clientY;

      loadScript();

      };

      loadScript();

      mapDiv.style.cursor = ‘pointer’;

      var urlString = [‘http://maps.google.com/staticmap?markers=’%5D;

      var markerString = [];

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

      markerString.push(locations[i].lat + ‘,’ + locations[i].lng + ‘,red’);

      }

      urlString.push(markerString.join(‘|’));

      urlString.push(‘&size=800×600’);

      urlString.push(‘&key= ‘ + strMapKey);

      mapDiv.style.background = ‘url(\” + urlString.join(”) + ‘\’)’;

      }

      </script>

      </head>

      <body onload=”loadMapGadget();”>

      <div id=”container”>

      <div id=”map” style=”width: 800; height: 600; overflow:hidden”></div>

      </div>

      </body>

      </html>



  15. Save and test the report.

Conclusion:

Integrating Cognos 8 Report Studio data into Google Maps might not be quite as straight-forward as using the built in maps, but it’s not too difficult and the results are sure to impress your report consumers.  This is also a great starting point for learning to integrate Cognos 8 with other web services.

Advertisements

27 Responses to How To Display Data From a Cognos 8 Report in Google Maps

  1. John says:

    Can you please change the font in your last HTML item block to a monospaced font and indent the code? I am new to scripting/coding, and properly formatted code is more readable and therefore more helpful. Thanks, great site btw!

  2. Hi Bob,

    Are you able to provide a small data set that conforms to your code so that we can test it without modifying the code and getting lost in debugging errors we have introduced ourselves?

    Lots of great info on your site.

    Alan

    • Bob Reddert says:

      Sorry, but I don’t have any data readily available. I originally wanted to do the report against one of the Cognos sample databases, but none of them had the longitude and latitude necessary to make it work.

  3. still have no response from the report to google maps, a lovely blank screen

    • Bob Reddert says:

      Try deleting or disabling the HTML item before, and the two HTML items after the repeater. Then run the report to see the raw text for the locations. Make sure that the locations include the name, LAT and LNG elements as I show in the example (the remaining elements are optional). Make sure you don’t have any extra or missing comas, and that there are opening and closing braces for each location. Basically, make sure that the locations array is well-formed.

  4. WS says:

    Can you explain how the repeater works? How does the grouping & Sorting work? I was able to create a report with the google maps without the repeater, but whenever I ad the repeater it stops working. When I remove everything exept for the repeater, my locations in the repeater look just fine.

    • Bob Reddert says:

      WS,

      The repeater, as the name suggests, repeats for each row in the repeater’s query. The query should return a row for each pair of coordinates that you want to display on the map, so the repeater concatenates the

      I had to use the repeater’s grouping property because my data had the same location multiple times. For each location, I was displaying the number of 4 measures, so I needed to TOTAL the measures to get the total for the location. As for sorting, I didn’t need to sort my locations, but I can imagine there would be situations where this would be beneficial.

      I’m not sure how you got it to work without the repeater, unless you were only showing one map point. As I said, the repeater is what I used to assemble the coma delimited list of coordinates.

      -Bob

      • WS says:

        thank you for your quick reply. what i mend with creating the report without the repeater is the following:
        i run the repeater without the rest of the html items, which gave me a string with the lat and lng for my locations. i pasted this string in a html item and put this html item in instead of the repeater between the other html items to run the report. I now got the map with my locations when running the report. but when i put the repeater back (instead of the html item with the sting with locations), it does not work (i get a blank screen). So to me it seems that the repeater does what it is suppose to, but I don’t get the map to show. So I was wondering what I did wrong. Do you always need to use grouping when the data has the same location multiple times? Or is it not necessary to use grouping? If it is necessary, then the grouping should make the locations unique? what did you use in the grouping, [Query1].[Location] ?

      • Bob Reddert says:

        WS,

        Odd. Are you sure you’re using a “Repeater” object, and not a “Repeater Table” object?

        Thanks,
        Bob

      • WS says:

        Yes I am using a “Repeater” object, the grouping should not matter? Then I guess it is going wrong somewere else.

      • Bob Reddert says:

        WS,

        No, the only issue you might run into without grouping is having multiple points in exactly the same place on the map.

        -Bob

      • WS says:

        Hi Bob, In the mean while I got the map to work. But there are still some things I don’t understand. I use a text box prompt to filter the locations I want to see in the map, and that goes fine. Then I replaced the textbox prompt by a value prompt, and in this value prompt I use a table with locations to pick from. At this point the map does not show anymore when running the report. When I look at the source code in the report, the html code for creating the map is not there, do you know for what reason the html code does not show anymore?
        Thanks

      • Bob Reddert says:

        WS,

        I don’t know what the issue might be, but the first place to look is the query that is used in the repeater. Try adding a list to your report that shows you the results of that query and make sure you’re getting back the locations specified in the prompt.

        -Bob

      • WS says:

        I did try to see the result in a list, looks good. I think it has something to do with the numer of rows per page for the value prompts. but I’m not sure when I need to change what.

      • Bob Reddert says:

        WS,

        I don’t know how the rows per page would be affected by switching a prompt from a text prompt to a value prompt. Nevertheless, there are a few places where you may need to change the rows per page:
        – There is a Rows per page promperty in the repeater.
        – When testing there is a Rows per page property under Run->Run Options
        – When running the report, you may need to set the properties of the report in Cognos Connection under Properties->Report (tab)->Advanced Options

        -Bob

  5. Michael says:

    I actually implement a google map in cognos 10 with mashup service. basically i read the data from another report like reading something from web service. In that case, the map doesn’t work with the prompt page. I am wondering you method would be ok with prompt page, isn’t it? thanks.

  6. Michael says:

    Hi Bob,

    I tried the google map with promps. it gave me an error say “google is undefined”.
    I actually have the script at the beginning like

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

    do you have any idea about this error?

    Cheers,
    Michael

  7. Anqi says:

    Hello!

    Will the array locations be able to handle 6000 rows of data? Will the map load?

    Thanks for sharing!

    regards

    • Bob Reddert says:

      I’ve never tried it with more than 100 locations, and I don’t recall any documented limits. Regardless, I’m sure there are practical limits. To manage that, you can limit which locations are displayed at any given time with prompts and filters.

  8. Anand says:

    Bob,

    I am having the difficulty in integrating the google map with prompts. It works fine without prompts, but when I add the prompt in the Query, nothing is displayed…

    Anand.

    • Bob Reddert says:

      Anand,

      The simple act of adding a prompt should not have any impact. To confirm this, take the report that worked (without the prompt) and add very simple prompt such as “1 = ?TestPrompt?”, run it, and fill in the TestPrompt with “1”. If it still works, then you know that it’s not the addition of prompts that is causing the issue.

      The issue is almost certainly that the prompt you added changed the query’s result set somehow. The best way to debug this is to test the query (instead of testing the report). I think you’ll find that the prompt you added is somehow resulting in empty query results, or in values that make no sense to the Google Maps API.

      Good luck,
      Bob

  9. Billy says:

    Hi Bob,

    I followed the exact steps mentioned over here, but the report does not display any map.Below is the data, and the final tabular output for the query 1. Data Item1(code, just in case to visualize format) is what i placed in the repeater object (using HTML object).

    Country HotelState Hotel City RowCount ElementSeperator Latitude Longitude Data Item1

    United States Delaware Wilmington 1 39.7458 75.5467 {State:”Delaware”,City:”Wilmington”,Country:”United States”,lat:39.7458,lng:75.5467}

    United States Kansas Wichita 2 , 37.6889 97.3361
    , {State:”Kansas”,City:”Wichita”,Country:”United States”,lat:37.6889,lng:97.3361}

    United States Virginia Virginia Beach 3 , 36.8506 75.9779
    , {State:”Virginia”,City:”Virginia Beach”,Country:”United States”,lat:36.8506,lng:75.9779}

    United States New Jersey Trenton 4 , 40.2237 74.7640
    , {State:”New Jersey”,City:”Trenton”,Country:”United States”,lat:40.2237,lng:74.7640}

    Environment: Cognos 10.2.1
    Database: SQL Server
    Output: Report Generates Blank Output
    Updated Google Maps API Key

    However, i have also tried Rayudu Vecha and Sridhar Munireddy developerWorks article, and it works fine, but the problem comes when i need to mark dynamic cities over the map.
    We have around 50 cities for USA region and all should be displayed with a ballon marker on the map.

    If you can check the report XML and let me know the correction, it would be great.

    • Bob Reddert says:

      Billy,

      There are too many variables for me to test. However, I did notice that your coordinates for Wilmington actually point to somewhere in China. They should probably be “lat:39.7458,lng:-75.5467” (please note the sign on the longitude).
      You’ll also want to make sure that the quotations are single-quotes or double-quotes, and not the smart-quotes displayed. WordPress trips me up on that all the time.

      Best of luck to you.

      Thanks,
      Bob

  10. Richard says:

    Hi Bob,

    Have you attempted the same with ArcGIS JavaScript API?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: