IBM BluemixDevelop in the cloud at the click of a button!

IBM Business Analytics Proven Practices: Using OpenStreetMap In IBM Cognos BI Reports

Product(s): IBM Cognos BI

Using OpenStreetMap in IBM Cognos BI reports as an alternative to built-in mapping, Google Maps or Bing Maps.

Share:

Yuri Kudryavcev, Solutions Architect, PMSquare

Yuri Kudryavcev is a Solutions Architect working for PMSquare, an IBM Cognos consultancy with offices in Australia and Singapore. Yuri has been working with Cognos products for more than 11 years, blogs at ykud.com and is an IBM Information Champion for past 3 years



25 February 2014

Also available in Russian

Introduction

Purpose of Document

This document describes the steps to create a simple IBM Cognos BI map report using the OpenStreetMap map layer.

Applicability

The technique outlined in this document was validated in,

  • IBM Cognos 10.1.1 using the Sales and Marketing (cube) package
  • IBM Cognos 10.2.1 using the Sales and Marketing (cube) package

Caveats

This technique uses Leaflet.js library for map rendering and uses only out-of-the-box Cognos BI functionality. Changes to the Cognos BI Cascading Style Sheets (CSS) and/or the Leaflet API changes may cause the technique to become obsolete in later versions.


Using OpenStreetMaps in IBM Cognos BI reports

There are several ways to include map visualizations in an IBM Cognos report but most of them require either licensing the map content, as is the case with the built-in MapInfo capability and ESRI plugin, or licensing API usage as is the case of well-documented Javascript-based maps such as Google Maps and Bing Maps. The APIs for both Google Maps and Bing Maps were once free to use but this may no longer be the case and as of time of writing (late 2013) using any of these maps in IBM Cognos reports requires additional licensing and fees.

A viable alternative is to use OpenStreetMap which is licensed under the Open Data Commons Open Database License (ODbL). This license allows free usage with proper attribution. Mapquest provides free-to-use tile servers under the same condition. In this document we’ll demonstrate how to create a simple Cognos BI report with OpenStreetMap using the Leaflet JavaScript library for mobile-friendly interactive maps. Links to more information about OpenStreetMap, ODbL and Leaflet JavaScript library can be found in the Resources section at the end of this document.


Create A Report Using OpenStreetMap

The report we will create here will be similar to report described in IBM Technote #1367426 - JavaScript: Adding Google Maps to Cognos reports.

  1. Start with a blank Report Studio using the Sales and Marketing (cube) PowerCube package from the Cognos BI samples.
  2. From Toolbox pane, drag and drop a Table object and set the table size to one row and two columns when the Insert Table dialog appears. We’ll be placing the map on the left and a list of countries on the right.
    Figure 1 – Insert Table dialog specifying two columns and one row
    Figure 1 – Insert Table dialog specifying two columns and one row
  3. Drag and drop a List object into the right column of the table we just added. In the Object and Query Name dialog that appears, in the Name field enter the value List_RetailerCountries and the Query Name field enter the value qry_RetailerCountry.
    Figure 2 - Object and Query Name dialog specifying a query name and value for the new List object
    Figure 2 - Object and Query Name dialog specifying a query name and value for the new List object
  4. From the Source pane, drag and drop the Retailer country level from onto the newly created list.
    Figure 3 – Drag the Retailer country level to the list report
    Figure 3 – Drag the Retailer country level to the list report
  5. We’ll now add the latitude and longitude of country capitals as data items. Right-click anywhere on the list and from the menu, select Go to Query.
    Figure 4 – Right-click menu with Go to Query option selected
    Figure 4 – Right-click menu with Go to Query option selected
  6. Back to the Toolbox pane, drag and drop a new Data Item into the Data Items area.
    Figure 5 - Drag a new Data Item from the Toolbox to the Data Items area
    Figure 5 - Drag a new Data Item from the Toolbox to the Data Items area
  7. Give the data item the name Latitude and copy the following code fragment to it,
    #
    /*
    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 ('Korea') then (37.5665)
    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
  8. Drag and drop a second data item, give it the name Longitude and copy the following code fragment to it,
    #
    /*
    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 ('Korea') then (126.978)
    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
  9. We will now add the map and the HTML Item that will add markers for the country capitals on the map. To do this we need to go to Page Explorer and select Page 1.
    Figure 6 – Page Explorer in Report Studio
    Figure 6 – Page Explorer in Report Studio
  10. From the Toolbox, drag and drop a new HTML Item to left column of the table. This HTML Item will contain the JavaScript for the map.
    Figure 7 - Drag and drop an HTML Item from the Toolbox onto the report
    Figure 7 - Drag and drop an HTML Item from the Toolbox onto the report
  11. This step is not required but since it’s best to keep report readable, select the created HTML Item and in the Properties pane, enter the text Map into the Description property.
    Figure 8 – The Properties pane to change the Description property for the selected HTML Item
    Figure 8 – The Properties pane to change the Description property for the selected HTML Item
  12. Double click on the HTML Item and copy the following Leaflet JavaScript code. This code initially renders your map and adds a function that will be called in the list (added on later steps) to draw the markers for each of the countries. This code doesn’t depend on any Cognos BI JavaScript functions and therefore is Cognos BI version independent. You can substitute map tile providers (MapQuest and Cloudmade) and add a link to a drill-through reports by uncommenting specific parts of the code.

    Click to see code listing

    <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://ec2-54-253-4-37.ap-southeast-2.compute.amazonaws.com: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();	
    			}
    }
    
    //Reloading map borders to avoid missing tiles due to Cognos & Leaflet CSS conflicts
    setTimeout('map.invalidateSize(false);',1000);
    </script>
  13. Now we need to draw markers for the country capitals and to do this we need to drag another HTML Item from the Toolbox to any position the list. In this example we’ll do it to the right of the Retailer country column.
    Figure 9 - Drag and drop an HTML Item from the Toolbox onto the report
    Figure 9 - Drag and drop an HTML Item from the Toolbox onto the report
  14. Since we want to call the draw marker function for every row, we need the HTML that will trigger the function to be dynamic. In the Properties pane for the HTML Item, change HTML Source property from Text to Report Expression.
    Figure 10 - The Properties pane to change the Source Type property for the selected HTML Item
    Figure 10 - The Properties pane to change the Source Type property for the selected HTML Item
  15. Double click on the HTML Item and copy the following code. Note that we are using the query name, qry_RetailerCountry, that was set in Step 3.
    '<a href="#" onClick="displayInfoLatLng( ''' + number2string
     ([qry_RetailerCountry].[Latitude]) + ''', ''' + number2string
     ([qry_RetailerCountry].[Longitude]) + ''', ''' +
     [qry_RetailerCountry].[Retailer country] +''')"> Show on Map</a> 
    <script> displayLocationLatLng('''  + number2string
     ([qry_RetailerCountry].[Latitude]) + ''', ''' + number2string
     ([qry_RetailerCountry].[Longitude]) + ''', ''' +
     [qry_RetailerCountry].[Retailer country] + ''' ); </script>'
  16. We need to change the properties of the list to include Latitude and Longitude data items for HTML we’ve just created to work. Select the List object and in the Data section, double-click on the Properties property.
    Figure 11 - The Properties pane to change the Properties property for the List object
    Figure 11 - The Properties pane to change the Properties property for the List object
  17. Select both the Latitude and Longitude data items that we created in Steps 7 and 8.
    Figure 12 – Select the data items for the Properties property of the List object
    Figure 12 – Select the data items for the Properties property of the List object
  18. Still in the Properties pane for the List object, set the Rows Per Page property to 100 so all countries we are using will be displayed.
  19. Run the report and the result should look similar to the one shown in Figure 13 – a zoomed out map of the world with marker balloons on all the countries specified in the list.
    Figure 13 – Initial report output showing a world map with markers for each country in the list
    Figure 13 – Initial report output showing a world map with markers for each country in the list

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


  • Bluemix Developers Community

    Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.

  • Big data and analytics

    Crazy about Big data and analytics? Sign up for our monthly newsletter and the latest Big data and analytics news.

  • DevOps Services

    Software development in the cloud. Register today to create a project.

  • IBM evaluation software

    Evaluate IBM software and solutions, and transform challenges into opportunities.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=964093
ArticleTitle=IBM Business Analytics Proven Practices: Using OpenStreetMap In IBM Cognos BI Reports
publish-date=02252014