This summer Centigon Solutions released the latest version of their Google Maps add-on for Xcelsius (uh, SAP Crystal Solutions Presentation Design…). Although I liked the first version of this add-on, I really missed an option for showing regional alerts. You still had to use the standard Xcelsius map-components which did support this feature.
In this post I will show the steps you have to take to create a dashboard showing a Google Map with regions that have alert colors and are clickable with some drill-down functionality like the example below.
Update: First check this post concerning changes on the Google Maps API.
[kml_flashembed movie=”http://hackingsap.com/blog/wp-content/uploads/NL_GMaps_demo.swf” height=”600″ width=”600″ /]
1. Download and install the GMaps plugin. You can find the trial at GMapsPlugin.com. Install it from the Add-on Manager within Xcelsius (File-menu).
2. Obtain a free Google Maps API key. You’ll need this key to get Google Maps running. Go to this website and sign up to get your unique key instantly.
3. Drag the GMaps Plugin component to the canvas. You can find this component in the Maps-folder.
4. The component doesn’t show a Google Map yet. The Google Maps API key has to be binded to component first. So, past the API key in one of the spreadsheet cells and bind this cell to the component properties.
After this you will see the Google Map appear in the component.
5. Next we need a so called Shapefile which provides the overlay for a geographical area. These Shapefiles can be found on this website (free!). For this example I downloaded the Shapefile for The Netherlands. After extracting the NL.exe file you’ll see 5 files. Change the .shp extension of the nl.shp file into .txt since some webservers don’t support these .shp files.
6. Now bind the location of the Shapefile to the Gmap component. First select the Shape Data option and bind it to the cell with the path/url to the Shapefile. Also make sure that you selected Shape Data Source within the Shape Options.
Note: The Shapefile will not appear in preview mode within Xcelsius if you are using a relative path.
7. If you hit Preview the Shapefile with the 12 provinces of The Netherlands should be visible on the Google Map.
8. Now we have to find out the numbering of the regions and the corresponding names. This is stored in the nl.dbf file. To open this file you can use an application like the DBF Explorer (free download).
9. Copy the labels for the regions in this order to your Xcelsius spreadsheet and bind them to the component.
10. Put some initial values in the next column and bind these cells to the component.
The spreadsheet now should look something like this:
11. It’s time to add some alert coloring. To keep it simple I only use red (colorcode #9F0D11) and green (#0DA118) in this example. I use an If/then formula to determine which color a region should get with a target of 5.
On the Appearance tab these Alert-cells can be binded.
Hitting Preview will now show the colored Dutch provinces.
12. To make sure that the Google Map is initially pointed to the right location on the world map we have to set the initial coordinates. You can find these coordinates on this site.
13. Finally the map regions should be clickable so we can enable a drill-down to more detailed data. I added some data and created a VLOOKUP function based on the value in cell B4. This cell should be binded as Insertion Destination (General Tab).
The spreadsheet now looks like this:
14. You can find the xlf-file here (via Pay with a Tweet). Make sure you enter your own Google Maps API key and the location of the SHP-file. If the file downloads as a ZIP-file, just rename the .zip into .xlf.
HackingSAP.com - Aug 14, 2010 | Knowledge sharing, New technology, SAP, SAP BusinessObjects Dashboards, Xcelsius
Tagged | Add-on, Centigon Solutions, Crystal Solutions, Dashboards, Geo Analytics, GMaps Plugin, Google, Google Maps, SAP, SAP Crystal Dashboard Design, SCDD, SHP, Xcelsius