Since Webi is capable of displaying web content in a cell, we can integrate html and javascript into Webi reports. as a demo, I have integrated Google Maps
into a Webi report to map contact addresses for consultants.

In order to pass multiple dimensions to each map marker, I created a variable that concatenates 2 dimensions into 1 string. I did this with 2 dimensions, but
it can be done for any number of dimensions.

  

We start by defining a variable that concatenates the Contact Name dimension with the Contact Postal Code, and then concatenates that string with the
previous row’s result. To do this we use the build in function “Previous”

Var Concat Contact Name=If IsNull(Previous(Self))Then [Contact Name]+” * “+[Contact Postal Code] Else [Contact Name]+” * “+[Contact Postal Code]+”;”+Previous(Self)

As you can see, I separate the 2 data points with a ‘*’ character for easy parsing in the map code. I am also checking to see if Previous(Self) is null so
that the last entry doesn’t end with a semicolon.

The result is a list of Contacts and their Postal Codes. We have to do it this way because the Javascript is run in a table cell, which is context sensitive, so we need all data in a list to avoid #multivalue

We then do some Webi magic by taking the Max of Contact Name in each unique Postal Code and filtering on that variable.

Details on how and why we had to do this can be found in the following blog: http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html

     Var Max Contact Name=Max([Contact Name]In([Contact Postal Code]))

     Var Max Concat Contact Name=[Var Concat Contact Name]Where([Contact Name]=[Var Max Contact Name])

We can now pass the varirable “Var Max Concat Contact Name” to the javascript and parse out each entry to pass to the map.

    <script type=\”text/javascript\”>

        …

        var multi = ‘”+[Var Max Concat Contact Name]+”‘;

        …

     </script>

An example of another Webi Mashup can be found here: http://bihappyblog.com/2012/01/20/google-maps-in-webi-4-0-mashup/.
I ended up embedding the HTML and javascript into a cell instead of calling a separate html file, but the basics are there.

How it works:

Google maps provides us with an easy to use API for creating and formatting the maps: https://developers.google.com/maps/documentation/javascript/

1)When the report loads, after all data is retrieved from the database and the report rendered, the javascript is executed.

2)We begin by creating the map and setting it’s default view to Road map and centering it on canada at a chosen zoom level.

3)We then parse the data out of the “[Var Max Concat Contact Name]” variable we created and put each entry into an array index.

4)Since the way Google Maps translates addresses (or in this case postal codes) into geographic coordinates is through a web-service, we are presented
with the problem of asynchronicity. Quite often the javascript will finish execution before we get any more than 5 or 6 coordinates back from the
web-service. To get around this, I write a function which gets a postal code, calls the web-service, and then handles the web-service return value with a
call-back function.

Another issue I had to work around is throttling the webservice. If too many calls to the webservice are made at once, an error is returned instead of
coordinates. To handle this I delay each service call by 100ms and increase this value each time an error is returned.

5)As each coordinate is returned, create a marker, create an infowindow for the marker and add an event listener, then add the marker to the map.

MAP2.png

By setting the table of contacts nad postal codes as an input control to the map window, we can filter and show 1 marker at time 🙂 .

Notes:

  • Since each time the report is refreshed or altered the map is redrawn/processed, it would be ideal to store the coordinates in a database with
    the contact addresses.
  • There is a limitation on the number of webservice requests you can call in a given day, this reinforces the first bullet above.
  • The map is only visible in a web environment, so saving the report as a pdf/excel would render a blank window.
  • This is an ideal Dashboard application.

Feel free to ask me questions, I left a lot out of this blog post.

***UPDATE***

I’ve received some requests for the full javascript code, so I’ve attached it to this post.

The code is still a little buggy as it is mostly just a proof of concept.

Here are the definitions for the Webi dimensions I use:

AddressFilter=If [ShowDetails] = 1 Then [Contact Name] + ” * “+[Contact Postal Code] Else “”

ShowDetails=If Count([Contact Postal Code];All) > 1 Then 0 Else 1

Var Max Contact Name=Max([Contact Name]In([Contact Postal Code]))

Var Concat Contact Name=If IsNull(Previous(Self))Then [Contact Name]+” * “+[Contact Postal Code] Else [Contact Name]+” * “+[Contact Postal Code]+”;”+Previous(Self)

Var Max Concat Contact Name=[Var Concat Contact Name]Where([Contact Name]=[Var Max Contact Name])

MaxCat=Max([Client Asset Summary Total Value Amount]ForEach([Contact Postal Code]))

Dimension relationships:

Contact Name is Unique, many contacts may have the same postal code. Client Asset Summary Total Value Amount is a dollar value associated with each Contact Name.

Have Fun 🙂 !

To report this post you need to login first.

33 Comments

You must be Logged on to comment or reply to a post.

  1. Thomas Evans

    Hi Ryan, thank you for sharing this idea. Are you perhaps able to share the full API code used? I assume it’s similar to the other mashup link you shared, however I have had issues with that as it’s written on the API v2 and I cannot get a key anymore.

    Additonally, I found issues with the concat name + post code variable you gave – WEBI doesn’t seem to like +”;”+ any ideas?

    The more you can share the better 🙂

    Many thanks.

    Thomas

    (0) 
  2. SUBBARAO MUCHERLA

    Hi White,

    Its an “awesome thought” Integrating  MAP…    we can use this idea in many ways….

    Thanks for sharing such an wonderful Document ℹ .

    Regards

    Subbarao

    (0) 
      1. WILLIAM MARCY

        Be sure you have a valid key for Google Maps API. There are multiples versions of API (V2, V3)…and you’ve to copy it in your Javascript on your report.

        Maybe your proxy is blocking calls to the API or another issue, use IE instead of others web browsers with right security settings.

        (0) 
        1. Thomas Evans

          Hi William, I know this server has no internet access at all. I was hoping it would run client side but looks like it’s server side as I built some custom toggles into my HTML and they are rendering – just nothing else, and I can’t even open some other simple web page in an html cell.

          (0) 
          1. Ryan White Post author

            You will definitely need an internet connection. While the JavaScript runs on the local server, it calls web services that need an internet connection.

            (0) 
    1. Ryan White Post author

      A few things to check:

      1)Make sure you are viewing the report in the Java web environment, the javascript will not work in other environments.

      2)Make sure you have a valid google API key for version 3 (version 2 will likely not work)

      3)If the map still does not appear, try launching Developer tools in your browser and click on the Script tab and reload the window. If there are any javascript errors, that will be the problem you need to fix.

      Let me know if the above fixes your problem.

      (0) 
      1. Thomas Evans

        Hi Ryan,

        Checked the above, definitely in web and have a valid V3 key.

        Developer tools gives me some errors in the console, although I can’t even make a simlple HTML cell with returns the page with <a href=”www.google.com”</a> or any website for that matter..  Any ideas?

        (0) 
      2. Abhi SAP

        Thanks for your quick reply.

        1) I debugged the javascript in developer console and looks like the variables “Addressfilter” and “Var Max Concat ContactName” are not passing values to javascript. I have my javascript code defined in an empty cell (read as ‘HTML’).

        2) I’m not sure whether I can use API V3 key for reports developed for customer.

        Thanks,

        Abhi

        (0) 
        1. Ryan White Post author

          Hi Abhi, If the variables aren’t passing anything in to the map, the issue is likely in the Report side of things. It can be tricky playing with the variables definitions to get them outputing the datat the way you want.

          If you want to use google maps in a production environment, you’ll likely want to pay for the Google API for Business which can accomodate 100000 requests per day opposed to the free key which only allows you 2500 per day.

          (0) 
          1. Abhi SAP

            Thanks Ryan for the information.

            Here is the javascript I’m using. can you help me pass these cntry variable to the javascript.

            <!DOCTYPE html>

            <html>

              <head>

                <meta name=”viewport” content=”initial-scale=1.0, user-scalable=no”>

                <meta charset=”utf-8″>

                <title>Geocoding service</title>

                <style>

                  html, body, #map-canvas {

                    height: 100%;

                    margin: 0px;

                    padding: 0px

                  }

                  #panel {

                    position: absolute;

                    top: 5px;

                    left: 50%;

                    margin-left: -180px;

                    z-index: 5;

                    background-color: #fff;

                    padding: 5px;

                    border: 1px solid #999;

                  }

                </style>

                <script src=”https://maps.googleapis.com/maps/api/js?v=3.exp&sensor=false“></script>

                <script type=”text/javascript”>

            var geocoder;

            var map;

            function initialize() {

              geocoder = new google.maps.Geocoder();

              var latlng = new google.maps.LatLng(-34.397, 150.644);

              var mapOptions = {

                zoom: 8,

                center: latlng

              }

              map = new google.maps.Map(document.getElementById(‘map-canvas’), mapOptions);

              codeAddress();

            }

            function codeAddress() {

              var address = document.getElementById(‘address’).value;

              var test = ‘”+[Cntry]+”‘; 

              var n = test.search(“;”);

              var list = new Array();

              if (n!=-1)

                list = test.split(“;”);

              else

                 list[0] = test;   

               

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

            {  

               geocoder.geocode( { ‘address’: list[i]}, function(results, status) {

                if (status == google.maps.GeocoderStatus.OK) {

                  map.setCenter(results[0].geometry.location);

                  var marker = new google.maps.Marker({

                      map: map,

                      position: results[0].geometry.location

                  });

                } else {

                  alert(‘Geocode was not successful for the following reason: ‘ + status);

                }

              });

            }

            }

            setTimeout(initialize,5000);

                </script>

              </head>

              <body>

                <div id=”panel”>

                  <input id=”address” type=”textbox” value=”dallas” onchange=”codeAddress()”>

                  <input type=”button” value=”Geocode” onclick=”codeAddress()”>

                </div>

                <div id=”map-canvas”></div>

              </body>

            </html>

            Thanks,

            Abhi

            (0) 
            1. Ryan White Post author

              The way webi cells work when read as html, the entire contents need to encapsulated in Quotes so that all contents make up a long string. You also need to be carefult of special characters such as Tabs or non breaking spaces.  When you want to specify a string variable, either use single quotes, or use \” so that the String is not terminated as far as WEBI is concerned. Try the code below and let me know if it works…

              =”<!DOCTYPE html>

              <html>

                <head>

                  <meta name=\”viewport\” content=\”initial-scale=1.0, user-scalable=no\”>

                  <meta charset=\”utf-8\”>

                  <title>Geocoding service</title>

                  <style>

                    html, body, #map-canvas {

                      height: 100%;

                      margin: 0px;

                      padding: 0px

                    }

                    #panel {

                      position: absolute;

                      top: 5px;

                      left: 50%;

                      margin-left: -180px;

                      z-index: 5;

                      background-color: #fff;

                      padding: 5px;

                      border: 1px solid #999;

                    }

                  </style>

                  <script src=\”https://maps.googleapis.com/maps/api/js?v=3.exp&sensor=false\”></script>

                  <script type=\”text/javascript\”>

              var geocoder;

              var map;

              function initialize() {

                geocoder = new google.maps.Geocoder();

                var latlng = new google.maps.LatLng(-34.397, 150.644);

                var mapOptions = {

                  zoom: 8,

                  center: latlng

                }

                map = new google.maps.Map(document.getElementById(‘map-canvas’), mapOptions);

                codeAddress();

              }

              function codeAddress() {

                var address = document.getElementById(‘address’).value;

                var test = ‘”+[Cntry]+”‘; 

                var n = test.search(‘;’);

                var list = new Array();

                if (n!=-1)

                  list = test.split(‘;’);

                else

                   list[0] = test;   

                 

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

              {  

                 geocoder.geocode( { ‘address’: list[i]}, function(results, status) {

                  if (status == google.maps.GeocoderStatus.OK) {

                    map.setCenter(results[0].geometry.location);

                    var marker = new google.maps.Marker({

                        map: map,

                        position: results[0].geometry.location

                    });

                  } else {

                    alert(‘Geocode was not successful for the following reason: ‘ + status);

                  }

                });

              }

              }

              setTimeout(initialize,5000);

                  </script>

                </head>

                <body>

                  <div id=\”panel\”>

                    <input id=\”address\” type=\”textbox\” value=\”dallas\” onchange=\”codeAddress()\”>

                    <input type=\”button\” value=\”Geocode\” onclick=\”codeAddress()\”>

                  </div>

                  <div id=\”map-canvas\”></div>

                </body>

              </html>”

              (0) 
                1. Ryan White Post author

                  Hi Abhi, to troubleshoot, first see if you can get the map appearing without passing it anything (just hard code a value). If it works, then the problem is in your variable, otherwise it’s the javascript.

                  Let me know and we’ll go from there.

                  (0) 
  3. Mikhail Budilov
    (0) 
  4. Ronald Odiyar

    Hi Ryan/Readers,

    Thanks for the great post! It inspried me and below is a snapshot of what I built 🙂

    However, I do have one problem when I print or download as PDF the google map is not displayed. Is there any work around to get Webi to print the map along side the other graphs and charts?

    Thanks!

    Ronald

    Webi-Google-Maps.PNG

    (0) 
    1. Ryan White Post author

      What you are experiencing is one of my biggest frustrations. Any web content that renders in a Frame or table cell will not print. I’ve spent a good amount of time searching for a fix, and the only thing I came up with is embedding a pdf writing library to do a custom print of the content. I have not tried this however so I’m not sure it would work either.

      I’ve had to resign to the fact that any javascript or html output is only viable for dashboards or on-screen viewing.

      If you come up with any solutions to this PLEASE let me know 🙂

      (0) 
    2. Dag Rossum

      Could you share how to get the map to show more than a single data point. Keep getting #multivalue everytime i trie to select more than one data point… 🙁

      (0) 
  5. Derek Dang

    Hi Ryan,

    This is an amazing post. I downloaded your script text and it does work to display the Google map in the Webi.

    But I did not understand what the [Line1],[Line2] and [Line3] are. They should be the attributes or measures defined in the Webi report, but I did not find the definition in your post.

    Thanks,

    Derek

    (0) 
  6. Dag Rossum

    Hi Ryan,

    Fantastic post! Got it working last night! 😎

    The only problem I have, is that the map is only dispayed when I have filtered on a Contact name. When set to “select all”, I end up with #multivalue in the table cell.

    How did you get your map to show multiple Contacts at the same time without getting #mulitvalue in the table cell?

    Thanks,

    Dag

    (0) 

Leave a Reply