Google Maps Mashup

Former Member · ‎09-27-2013

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.

...

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.

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 :smile: .

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 :smile: !