Purpose of Document
This document describes the steps to create a simple IBM Cognos BI map report using the OpenStreetMap map layer.
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
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
Create A Report Using OpenStreetMap
- Start with a blank Report Studio using the Sales and Marketing (cube) PowerCube package from the Cognos BI samples.
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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 7 - Drag and drop an HTML Item from the Toolbox onto the report
- 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
- 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
- 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
- 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>'
- 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
- 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
- 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.
- 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
- Open Data Commons Open Database License
Dig deeper into Big data and analytics on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Crazy about Big data and analytics? Sign up for our monthly newsletter and the latest Big data and analytics news.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.