Thursday, June 23, 2011

Integrating Google Maps with Cognos - Sample

Steps:
  1.  Login with your google account into the below url.
  2. https://www.google.com/accounts/ServiceLogin?hl=en&continue=http://www.google.co.in/
  3. Sign up for the Google Maps API using the following URL:
  4. http://code.Google.com/apis/maps/signup.html
  5. Enter the IP address of the Cognos server where the reports will be running.
  6. Click on the Generate API Key button to generate a unique key. The unique key that is generated is only valid for the server that you entered earlier to generate the key, and it will be used in the script for working with the Google Maps API. Please remember that the key is only valid for a single Cognos server IP.
  7. Create a new Blank report using Go Sales(Query) Package.
  8. From the insertable objects toolbox, add a Repeater object to the body of the report.
  9. Right-click on the new repeater object and select Go To Query.
  10. 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).
  11. 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:
    • Add a Data Item to the query named RowCount. Set the expression to:
    • rank([Address 1])
    • Add another Data Item named ElementSeparator. Set the expression to:
    • If ( [RowCount] = 1 ) THEN
      ( ” )
      ELSE
      ( ‘, ‘ )
  12. Return to the page (Page1).
  13. 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.
  14. 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)
  15. Create a table with 1 row and 2 columns.
  16. Place the repeater in the second column.
  17. Add an HTML Item to the left of the table. This will contain the start of the JavaScript.
    • Source Type : Text
    • Set the HTML text to the following
    • <html>
      <script src="http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAx7vAbzXFBJGQEupKGA51dBQTTvU48baM3A06e5jSe_wpl0bp3RShVke5jiOCbv1e64rD40-_wur7QA" type="text/javascript">
      </script>
  18. Add a second HTML Item immediately to the left of repeater.
    • Source Type : Text
    • HTML text to following:
    • <div id="map" style="width: 700px; height: 400px"></div>
      <script type="text/javascript">
      var map = new GMap2(document.getElementById("map"));
      var geocoder = new GClientGeocoder();
      var locations=[
  19. Add a third HTML Item inside the repeater. This will be the assembly of the array containing your data.
    • Source Type : Report Expression
    • Set the report expression to the following, modifying it to fit your data:
    • [Query1].[ElementSeparator] + '{Address:"' + [Query1].[Address 1] + '",City:"' + [Query1].[City] + '",Country:"' + [Query1].[Country] + '"}'
  20. Immediately to the right of the repeater, add a fourth HTML Item
    • Source Type : Text
    • Set the HTML text to the following:
    • ];
      function showAddress(i, j) {
                if (!geocoder)
               {
                     return;
               }
               var address = locations[i].Address.concat(",".concat(locations[i].City));
               var html="<B>";
               html=html.concat(locations[i].Address);
               html=html.concat(", ");
               html=html.concat(locations[i].City);
               html=html.concat("</br></br>");
               html=html.concat(locations[i].Country);
               html=html.concat("</B></br>");
               html=html.concat("<a href=/"http://172.25.119.224:8080/ibmcognos/cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=CAMID(%22%3a%3aAnonymous%22)%2ffolder%5b%40name%3d%27My%20Folders%27%5d%2ffolder%5b%40name%3d%27My%27%5d%2freport%5b%40name%3d%27Prompt_B%27%5d&ui.name=Prompt_B&run.outputFormat=&run.prompt=false&p_City=">
               html=html.concat(locations[i].City);
               html=html.concat("\" target=\"_blank \"> Click here to view Sales Report </a>");
               var customIcon = new GIcon(G_DEFAULT_ICON);
               customIcon.image = "http://maps.google.com/mapfiles/marker_yellow.png";
               geocoder.getLatLng(address, function(point) {
                         if (!point) {
                                 return;
                         }
                         map.setCenter(point, 1);
                         var marker = new GMarker(point, {icon: customIcon});
                         GEvent.addListener(marker, "click", function() {
                                  map.openInfoWindowHtml(point,html);
                         });
                         if(j == 1)
                         {
                                 map.openInfoWindowHtml(point,html);
                         }
                         map.setZoom(3);
                         map.addOverlay(marker);
                         map.addControl(new GSmallMapControl());
                         map.addControl(new GMapTypeControl());
                         map.checkResize();
            });
          }
               function loadMapGadget()
          {
                      for (var i = 0; i < locations.length; i++)
                     {
                            showAddress(i, 0);
                     }
          }
          </script>
          <body onload="loadMapGadget();">
          </body>
          </html>
  21. Add a list to the first column of the table. Add a HTML item to the list.
    • Source Type : Report Expression
    • Set Report Expression to the following:
    • '<a href="#" onClick="showAddress('''+ number2string ([Query1].[RowCount]-1) + ''', 1)" >' + [Query1].[Address 1] + ", " + [Query1].[City] + ' </a>'
  22. Goto Page properties. Under Data, Select the Query and in properties add all the DataItems.
  23. Save and run the report.
Report Page
Report Output

1 comment: