IBM Business Analytics Proven Practices

Using OpenStreetMap In IBM Cognos BI Reports

Product(s): IBM Cognos BI

Comments

Content series:

This content is part # of # in the series: IBM Business Analytics Proven Practices

Stay tuned for additional content in this series.

This content is part of the series:IBM Business Analytics Proven Practices

Stay tuned for additional content in this series.

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
    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
    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
    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
    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
    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
    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
    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
    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.
    <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="//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
    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
    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
    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
    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
    Figure 13 – Initial report output showing a world map with markers for each country in the list

Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

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