Got a chance to play with maps in Cognos reports for a recent PoC and learned quite a lot in the process. Most notably, I integrated OpenStreetMap in Cognos report, so I’ll demonstrate how to do it. Looks like this:

Cognos and maps overview

So you’re up to doing some maps in Cognos reports? I was always convinced (and still mostly) am maps are rarely a good graphic medium apart from “wow”-effect, but it’s quite magical to look at the same data on a map, get’s even to me -)

And my general feeling is that if location data is really important to your organisation, you would definitely have a serious GIS system in-house and wouldn’t be looking for “just a map report” in Cognos -).

I’ve met some strikingly well used location analytics, for example one of my retail customers was using a dataset with car purchase history and using owners addresses & car price to colour suburbs for selecting retail outlet positions, that was very impressive feat. You should contact guys from ESRI if you want to hear a proper location analytics pitch, but I’m still thinking that it’s just a quick way to score some points with users, but then again, if it’s a quick way and an easy one, why avoid it?

There are quite a few options you can go with if you want to embed maps in your reports:

• Serious solutions
  • Built-in MapInfo maps
  • ESRI
• Javascript maps in reports, most notably:
  • Google Maps
  • Bing Maps
  • OpenStreetMap (OSM)

And since it takes time for these things to sink in: “Google Maps and Bing Maps are not free to use”. OSM maps are ;)

Serious solutions

Let’s start with heavy-weights. Go this way only if you really have the itching mapping need (if so you probably have one of them in-house already) and a bit of a budget, both come with a price tag. And that’s the reason I don’t have any experience with them apart for demos -)

MapInfo

Cognos has a built-in mapping capability based on MapInfo maps ever since Visualizer days. It ships with a few default maps (world map, US states) and you can use MapInfo Professional to create gst files for additional maps or you can buy them from MapInfo directly.

ESRI

ESRI has a full-blown over-the-top Cognos Integration (you get ESRI maps in Report Studio Toolbox and in most other studios) with no code-writing required. Good client and easy to use once you’ve set it up, if you plan to do a lot with location-based reports, ask for a demo and a quote.

Heaps more on their site

Javascript maps

More affordable (to a certain degree) solution is to use one of the web mapping service providers and integrate Cognos report data with a map on-flight using Javascript.

There are heaps of examples for both Google Maps and Bing (see links below), but I must repeat that both of them are not-free-to-use. You can check the legal clauses yourself (Bing and Google Maps), but the only way to qualify for free usage is to have Free and Public access under a specified quota of map calls. You need to contact Google or Microsoft for correct pricing, but judging by internet rumours it start with 10k$ per year.

Google Maps

Google maps have an interesting story, they were free for about 6 years and became the de-facto standard for maps in web applications, but then started charging developers from January 2012, causing massive upheaval, even after price was significantly cut-down (80% drop is sure sign of original error in my book). This lead to the rise of OpenStreetMap, massive projects like Foursquare made the switch to OSM instead of Google.

While Google maps were free, a lot of Google Maps and Cognos examples got published, here are some of them:

With coordinates set in latitude and longitude:

http://www.ibm.com/developerworks/data/library/techarticle/dm-1005googlemapscognos/

With client-side GeoCoding:

http://www-01.ibm.com/support/docview.wss?uid=swg21367426

http://www.ironsidegroup.com/2011/10/17/embed-google-maps-in-ibm-cognos-reports/

With polygons

http://sebastianmai.blogspot.com.au/2010/01/google-maps-polygon-overlay-with-ibm.html

With embedded report inside by using CMS:

http://www-01.ibm.com/support/docview.wss?uid=swg21430941

OpenStreetMap

OpenStreetMap is a crowd-sourced map of the whole world, meaning that it’s updated by users all the time (I just saw a bypass in remote town show up on OSM before Google Maps were updated), which is free to use and licensed so that it will be free to use in the future as well.  There’s nothing fully free in this world, so there are some caveats I’ll highlight along the way, but, in general, you can use OSM without paying a dime and you can even host your map server inside your network (making it available in case your users don’t have internet access / you want to limit web traffic / or are security paranoid).

Sadly, there’s no Cognos + OSM recipes out there yet, so I’ll try to correct this.

Before we proceed, we’ll need a bit of common map related-lingo.

Terminology

Geocoding

In the world of maps you operate with geographic coordinates (latitude and longitude) of a point, but in your data it’s usually street address or a postcode. Converting  addresses to geographic coordinates is called geocoding. It’s absolutely vital to geocode all locations you want to show before you do any report development. You can do online geocoding, but it’ll be terribly ineffective (geocoding will be called by every client running the report), slow (image the time it takes to convert you 1000 stores to points) and wouldn’t work at all (most online geocoders limit the load, Google geocoder wouldn’t allow more than ~10 requests per second so geocoding 1000 points would be just impossible).

It’s best to do your geocoding during ETL and store results in the dimension so you can easily use them in map reports you require.

And another licensing warning, you can legally display google geocoder results only on google maps, so it’s best to use one Nominatim (unsurprisingly, based on OSM data) if you want to use the coordinates anywhere else later.

Tiles

When you see a map in a webpage, it’s actually composed of multiple small square images called tiles. Map servers know what set of tiles to show user based on level of detail and map position. Here’s a fantastic explanation from Bing team.

You can imagine the amount of traffic tile serving requires, so there are only a few “free” tile servers (most notably, MapQuest I’m using in this example). You can register for free at CloudMade and try out their tiles, some people like them better.

If you have really high usage (or are absolutely paranoid), you can build your own tile server from OSM absolutely for free. If you do this, outside connection for maps will no longer be required, your maps will render from this server. Then you set up weekly updates to get map changes and you’re absolutely set to go.

You can even make your own tiles using free Tilemill and draw your own region / country borders or anything you would like. Game developers use this to draw “zombie” haunted city, overlaying it with real addresses and streets.

And while we’re at it GADM looks like a fantastic collection of official region definitions, there’s definitely the most detailed (60 Mb of shape files) “official” map of Vietnam that I could find for free.

Markers

Markers are the points that you put on the map and it’s quite easy to use non-standard icons, making the map a lot more appealing. You can conditionally change marker icons / colours.

Here’s a good collection of free markers.

Using OpenStreetMap in Cognos

And, finally, the main dish.

I’ll base this example on “sales and marketing” cube from standard cognos samples and what we’ll do will resemble Ironside recipe, but without geocoding, we’ll use predefined coordinates.

We’ll build a list of countries and draw a marker for each of the countries capital. I’m using Leaflet.js for mapping, a really nice mapping library. OpenLayers would be another option, but leaflet looked easier )

How to do the report (or just grab the report definition xml).

• start with 2 column table
• create a list to the right and dragging  Retailer country level to it
• define latitude and longitude with following expressions longitude:

      #
    /*
    Calculated longitude of country capitals
    */
    #
    case (caption([Retailer country]))
    when ('Australia') then (133)
    when ('Austria') then (13.3333)
    when ('Belgium') then (4)
    when ('Brazil') then (-55)
    when ('Canada') then (-95)
    when ('China') then (105)
    when ('Denmark') then (10)
    when ('Finland') then (26)
    when ('France') then (2)
    when ('Germany') then (9)
    when ('Italy') then (12.8333)
    when ('Japan') then (138)
    when ('Mexico') then (-102)
    when ('Netherlands') then (5.75)
    when ('Singapore') then (103.8)
    when ('Spain') then (-4)
    when ('Sweden') then (15)
    when ('Switzerland') then (8)
    when ('United Kingdom') then (-2)
    when ('United States') then (-97)
    else
    (0)
    end
  

  lattitude:

  #
  /*
  Calculated latitude of country capitals
  */
  #
  
  case (caption([Retailer country]))
  when ('Australia') then (-27)
  when ('Austria') then (47.3333)
  when ('Belgium') then (50.8333)
  when ('Brazil') then (-10)
  when ('Canada') then (60)
  when ('China') then (35)
  when ('Denmark') then (56)
  when ('Finland') then (64)
  when ('France') then (46)
  when ('Germany') then (51)
  when ('Italy') then (42.8333)
  when ('Japan') then (36)
  when ('Mexico') then (23)
  when ('Netherlands') then (52.5)
  when ('Singapore') then (1.3667)
  when ('Spain') then (40)
  when ('Sweden') then (62)
  when ('Switzerland') then (47)
  when ('United Kingdom') then (54)
  when ('United States') then (38)
  else
  (0)
  end
  

  • drag an html item to the list with the following html code. This html will register your countries capitals on the maps (by calling the function) and will centre map on the country you select when clicking “Show on the map“

  <a href="#" onClick="displayInfoLatLng( ''' + number2string ([q_RegionData].[Latitude]) + ''', ''' + number2string ([q_RegionData].[Longitude]) + ''', ''' + [q_RegionData].[Retailer country] +''')"> Show on Map</a>
  <script> displayLocationLatLng('''  + number2string ([q_RegionData].[Latitude]) + ''', ''' + number2string ([q_RegionData].[Longitude]) + ''', ''' + [  q_RegionData].[Retailer country] + ''' ); </script>
  

  • Drag an html item to the right column with following code. This is you map and you can play with tile servers too (change the lines in the middle):

  <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7/leaflet.css" />
   <!--[if lte IE 8]>
       <link rel="stylesheet" href="http://cdn.leafletjs.com/leaflet-0.7/leaflet.ie.css" />
   <![endif]-->
  
    <script src="http://cdn.leafletjs.com/leaflet-0.7/leaflet.js"></script>
  
   <div id="map" style="height: 400px; width: 550px;"></div>
  
  <script>
  
  //Center map somewhere, Australia is a good starting point ;-)
  var map = L.map('map').setView([-24.766785, 134.824219], 2);
  // Choose a map provider below
  // CLOUDMADE
  /**
  L.tileLayer('http://{s}.tile.cloudmade.com/your_application_key/997/256/{z}/{x}/{y}.png', {
      attribution: 'Map data &copy; <a href="http://openstreetmap.org">OpenStreetMap</a> contributors, <a href="http://creativecommons.org/licenses/by-sa/2.0/">CC-BY-SA</a>, Imagery © <a href="http://cloudmade.com">CloudMade</a>',
      maxZoom: 18
  }).addTo(map);
  /**/
  //MAPQUEST
  // try replacing map with osm or sat in url below, it'll change the tiles
  var mapquestUrl = 'http://{s}.mqcdn.com/tiles/1.0.0/osm/{z}/{x}/{y}.png',subDomains = ['otile1','otile2','otile3','otile4'],mapquestAttrib = 'Data, imagery and map information provided by <a href="http://open.mapquest.co.uk" target="_blank">MapQuest</a>,<a href="http://www.openstreetmap.org/" target="_blank">OpenStreetMap</a> and contributors.';
  var mapquest = new L.TileLayer(mapquestUrl, {maxZoom: 18, attribution: mapquestAttrib, subdomains: subDomains});
  mapquest.addTo(map);
  /**/
  
  function displayInfoLatLng(lat, lng, countryName) {
    displayMapLatLng(lat, lng, countryName, 1);
  }
  
  function displayLocationLatLng( lat, lng,   countryName)
  {
    displayMapLatLng(lat, lng, countryName, 0);
  }
  
  function displayMapLatLng(lat, lng, countryName,  displayInfo) {
        var latlng = new L.LatLng(lat, lng);
        var contentString = '<div id="content">'+
                '<b>' + countryName + ' </b> <br>'
      //        Link to report with parameter
      //        +'<a href="http://cognosserver:80/ibmcognos/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27sales_and_marketing%27%5d%2freport%5b%40name%3d%27drillDown_Report%27%5d&ui.name=drillDown_Report&run.outputFormat=&p_p_Country=' + countryName + '" target="_blank">Show report</a>'
    ; 
  
        var marker = new L.Marker( latlng);
        marker.bindPopup(contentString);
        marker.addTo(map);
        if (displayInfo == 1) {
          marker.openPopup();
        }
  }
  
  setTimeout('map.invalidateSize(false);',1000);
  </script>
  

If everything is correct, you’ll see result like this:

Here’s the report specification (10.2.1, but you can change version in first line of XML, 11.0 to 10.0 or anything and import it in earlier versions).

Take a look at Leaflet examples to see what else is easily achievable with it. I enabled mobile device location finder in the PoC with 1 line of code, but truth be said, that impressed only myself -)

Anything doable with Google maps can be converted to OSM, so if you’re interested to see how one of the existing recipes translates to OSM write me a comment and I’ll write about it. Potentially:

• Conditional markers
• Opening a report from a marker click in a new page (that’s already in the code above)
• Opening a report from a marker click in an iframe
• Drawing regions

Overall, that was an interesting dive in a new area!