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
    2. Enter the domain name at which you access Cognos 8 (ex:
    3. Click the Generate API Key button.This should create a key that looks similar to this:

    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
      ( ” )
      ( ‘, ‘ )

  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:



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


      <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;



      <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 = ‘


      (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()) { = ‘#fff’; = ”;

      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());


      var mapControl = new GMapTypeControl();


      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.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.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>’,, ‘</b>’,


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

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

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

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


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

      currentMarker = marker;

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

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


      else {




      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.state + ‘ ‘


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


      function _cel(elementType, id) {

      var element = document.createElement(elementType); = 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…’; = (800/2 – 53) + ‘px’; = 800/2 + ‘px’;


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

      script.type = ‘text/javascript’;

      script.src = ‘; +

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





      * 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(); = ‘pointer’;

      var urlString = [‘’%5D;

      var markerString = [];

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

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




      urlString.push(‘&key= ‘ + strMapKey); = ‘url(\” + urlString.join(”) + ‘\’)’;




      <body onload=”loadMapGadget();”>

      <div id=”container”>

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




  15. Save and test the report.


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.

Finding Shortcuts That Point To A Report In Cognos 8

If you’ve worked with Cognos 8 for any length of time, you’ve undoubetedly had it happen; You’re changing a report and need to know all the shortcuts that point to it within the Cognos Connection portal.

To date, there’s no way to find this within Cognos Connection, however a little digging into the content store database should do the trick.

This technique will get you the names of the shortcuts, but not the locations in Cognos Connection.  You’ll need to do a search to locate the shortcuts.

The Steps:

  1. Create a new shortcut pointing to the target report in question and give it a unique name (ex: My_Temp_Shortcut)
  2. Execute the following SQL Statement against the content store database:
    WHERE ONB2.NAME =  'My_Temp_Shortcut'

A Stitch in “Time”

Central to nearly every dimensional data model is one or more date
dimensions (aka time dimensions).  The benefits of the date (or time) dimension have been discussed at length since the genesis of dimensional modeling  in the 1990’s, so I won’t rehash them here. But for the uninitiated:

A date dimension is a table that contains a single row for each date represented in your fact table.  In addition to the surrogate key and an actual date, they also contain various attributes of dates (ex: day of week, day of month, quarter name, month name, etc) as well as attributes for representing multiple calendars (ex: fiscal calendar, academic calendar, etc.).  While it is true that many of these values can be calculated using built-in dbms date functions, these functions perform slowly and are different for each database vendor.

Relative Time Attributes

One set of attributes that I think are often overlooked are relative time attributes.  Relative to what? Usually to the current date (either today or the last date that the fact tables were updated).  Let’s start with the typical date dimension


CalendarDate: DATE
DateDescription: VARCHAR(40)
Year: VARCHAR(4)
Month: VARCHAR(10)
(various other attributes)

Now if, from this table, I needed to filter on yesterday, it wouldn’t be too difficult to do a little math to find that date for yesterday.

  CalendarDate = ( GetDate()-1 )

But what if I needed all the records from last week? How about last quarter?  What if I wanted to compare last quarter to the same quarter from last year?  Some of these calculations can be infuriatingly difficult, and don’t forget that the SQL necessary for SQL Server won’t be the same as the SQL for Oracle (or DB2 or mySQL…).

Enter; Relative time attributes. Let’s start with the most basic, RelativeCalendarDate:


CalendarDate: DATE
DateDescription: VARCHAR(40)
Year: VARCHAR(4)
Month: VARCHAR(10)
(various other attributes)
RelativeCalendarDate: INTEGER

This integer column will be populated with a 0 for the current date, -1 for yesterday, -2 for the date before, and so on.  For handling future dates, it would also have a 1 for tomorrow, 2 for the day after, and so on. So every row in the table will have a number representing the number of days from to day, negative going back in time, positive going forward.

You would then add similar relative attributes for every level of granularity you represent in the date dimension:


CalendarDate: DATE
DateDescription: VARCHAR(40)
Year: VARCHAR(4)
Month: VARCHAR(10)
(various other attributes)
RelativeCalendarDate: INTEGER
RelativeCalendarWeek: INTEGER
RelativeCalendarMonth: INTEGER
RelativeCalendarQuarter: INTEGER
RelativeCalendarYear: INTEGER
RelativeFiscalYear: INTEGER

So, similar to RelativeCalendarDate, RelativeCalendarWeek would have 0 for all 7 rows in the current week, -1 for all 7 rows in last week, -2 for all 7 rows in the prior week, +1 for all 7 rows in next week, etc..

An Example

Assuming that today was July 2, 2010, your week starts on Mondays, and your fiscal calendar begins in July, your table would look something like this:



(var.) Relative


















6/25/2010 Friday,
June 25, 2010
-7 -1 -1 -1 0 -1
6/26/2010 Saturday,
June 26, 2010
-6 -1 -1 -1 0 -1
6/27/2010 Sunday, June 27, 2010 -5 -1 -1 -1 0 -1
6/28/2010 Monday, June 28, 2010 -4 0 -1 -1 0 -1
6/29/2010 Tuesday, June 29, 2010 -3 0 -1 -1 0 -1
6/30/2010 Wednesday,
June 30, 2010
-2 0 -1 -1 0 -1
7/1/2010 Thursday, July 01, 2010 -1 0 0 0 0 0
7/2/2010 Friday, July 02, 2010 0 0 0 0 0 0
7/3/2010 Saturday, July 03, 2010 1 0 0 0 0 0
7/4/2010 Sunday, July 04, 2010 2 0 0 0 0 0
7/5/2010 Monday, July 05, 2010 3 1 0 0 0 0
7/6/2010 Tuesday, July 06, 2010 4 1 0 0 0 0
7/7/2010 Wednesday, July 07, 2010 5 1 0 0 0 0
7/8/2010 Thursday, July 08, 2010 6 1 0 0 0 0
7/9/2010 Friday, July 09, 2010 7 1 0 0 0 0

With the addition of relative time attributes to your date dimension, comparing ranges of dates at any level of granularity becomes simple. For example, let’s compare last month to the same month from last year.

  FactTable F
  INNER JOIN dimDates D ON F.DateSKey = D.DateSKey
  D.RelativeCalendarMonth IN (-1, -13)

Keeping Them Up-To-Date

Of course, to make the relative dates useful, they’ll need to be updated every day.  Below we see a SQL statement that will take care of this for SQL Server. You can run this on a nightly schedule, preferably right at midnight if your warehouse is available 24 hours a day.  This SQL can be adapted to work with any DBMS.

  RelativeCalendarYear = DATEDIFF(yyyy, GETDATE(), CalendarDate), -- RelativeYears
  RelativeCalendarQuarter = DATEDIFF(q, GETDATE(), CalendarDate), -- RelativeQuarters
  RelativeCalendarMonth = DATEDIFF(m, GETDATE(), CalendarDate), -- RelativeMonths
  RelativeCalendarWeek = DATEDIFF(ww, GETDATE(), CalendarDate), -- RelativeWeeks
  RelativeCalendarDay = DATEDIFF(d, GETDATE(), CalendarDate) -- RelativeDays
  DateSKey > 0

You’ll note the Where clause at the end.  In my date dimensions, I have rows with negative surrogate key values which represent such things as “No Date” (eliminating the need for nulls), “Unknown Date”  (another replacement for null values) and “Invalid Date” (because some transactional systems I’ve pulled from allow 31
days in February).  If you don’t have rows with negative surrogate keys, then the Where clause wouldn’t be necessary for you.

Things To Try

  • The concept should work well for even more granular time dimensions.  If shifts are part of your time dimension, or even hours, then you should be able to create relative time attributes for these periods of time as well.
  • I had considered adding named attributes as well (ex. RelativeCalendarDate = -1, RelativeCalendarDateName = “Yesterday”), but I was unable to come up with a comprehensive naming scheme. If you can think of one, please let me know.


As you can see, relative time attributes are easy to add to any data warehouse and are simple to maintain. They’ll reduce your SQL development time when working with dates, and in many cases they’ll improve the performance of your queries. They are not entirely intuitive, however with a brief explanation, your query developers will pick up the concept easily.

In the Beginning…

I’ve been developing data warehouses for more than a decade now.  Back when there was no such thing as an “ETL Tool”.  Mustering up by best “crotchety old man voice”; I remember when ETL meant developing Oracle functions by hand (yes, they were actually chiseled in stone). Years before I first heard of dimensional modelling, I was creating denormalized tables to feed Cognos PowerPlay cubes, and I’ve been working with the Cognos Business Intelligence tools ever since.

Over those years, I often had to come up with creative solutions to many of the obstacles I had faced. Some were used once and forgotten. Some became part of my standard tool kit and using them became second nature.  But what I was never good at was sharing these solutions with the rest of the world.  It’s not that I’m niggardly (I certainly share my ideas with my colleagues), but I’ve always known I should put them out there for the world to use, and to improve upon.

This new blog is my attempt to do that.  I plan to publish a  few in a short amount of time to get things started, and then to put out one or two a month. I don’t know how I’m going to come up with things worthy of publishing that frequently, but I figure if I set that expectation, then it’ll motivate me to identify things worth sharing that I would have otherwise missed.

I welcome constructive comments on all of my posts, and together, I hope we can come up with some really useful, and occasionally interesting BI techniques.