ajdjackson, Member
Hi
I'm looking a bit of advice/direction please.
In the past I have been able to replicate the SVG expample but I would like to create something different now.
What I would like to do is to have a map of the US and depending on the number of customers in each state have the states shaded with different colour - see below.
Is this possible?
Thanks
Jake
myDBR Team, Key Master
Instead of Google Maps you could just use a SVG image. See a demo.
--
myDBR Team
ajdjackson, Member
That looks great - thanks.
Please will you share the f_color_scale function.
What I would also like to have is a tool-tip that in your example would show the population for each state when moused-over.
Many thanks again - I wasn't aware of this.
Jake
myDBR Team, Key Master
The source code for the f_color_scale-function, a tooltip via SVG text-element and sample code how to add linked reports to external SVG image has been added to the demo.
--
myDBR Team
ajdjackson, Member
Hi
Many thanks for your great work with this - much appreciated.
I'm getting an issue where only about 1/3 of the states are being filled and having a tool-tip assigned.
Looking at the inspector is see the following:
If you look at id=Texas you can see that it's not getting the fill color or the title(tooltip) assigned.
I have amended your code to this; mainly only to get the data and initially remove the onclick as I don't know what's in the 'sp_DBR_single' sp.
declare v_tot_claims int;
declare v_map_url varchar(200);
select sum(28Day) into v_tot_claims from tblrh_analysis where year(period)=year(curdate());
create temporary table state_claims (State varchar(100), claims int, primary key(State));
insert into state_claims select trim(replace(replace(cast(state as char(100)), ' ', ''),'"','')), sum(round(Quantity/28,0)) from tblrh_cop_dc_mth where year(InvMonth)=year(curdate()) group by 1;
insert into state_claims select trim(replace(replace(cast(state as char(100)), ' ', ''),'"','')), sum(round(Quantity/28,0)) from tblrh_cop_ev_mth where year(InvMonth)=year(curdate()) group by 1 on duplicate key update claims=claims+values(claims);
-- select * from state_claims;
select 'dbr.javascript', '
function add_svg_color_tooltip(map_svg, id, text, color) { var node = document.createElementNS("http://www.w3.org/2000/svg", "title"); var textnode = document.createTextNode(text); node.appendChild(textnode); elem = map_svg.getElementById(id); elem.style.fill = "#"+color; elem.appendChild(node); } ';
select 'dbr.title', 'US Population';
select 'dbr.text', 'Data is added to a SVG chart (color, tooltip and a myDBR link)
You can hover your mouse on top of a state and click on it ', 'comment';
select 'dbr.html','<object id="us_map" type="image/svg+xml" data="user/images/Map_of_USA_States_with_names_white.svg"></object>';
select 'dbr.html', '<script> var map = document.getElementById("us_map"); map.addEventListener("load", function() { var map_svg = map.getSVGDocument(); ';
select 'dbr.html', concat("add_svg_color_tooltip(map_svg, '",state, "',","'Population ",format(claims,0),"','",f_color_scale( 'FFF8F8', 'FF0000', claims/v_tot_claims ),"');") from state_claims;
select 'dbr.html', '}); </script>';
set v_map_url = 'https://commons.wikimedia.org/wiki/File:Map_of_USA_States_with_names_white.svg'; select 'dbr.text', concat('Map courtesy of ',v_map_url,'
Gigillo83, original of 70.29.208.129 / CC BY-SA (https://creativecommons.org/licenses/by-sa/4.0)'), 'comment';
A sample of the data in the temporary table state_claims:
As you can see Texas is in the data - in fact it is the state with the most claims (highest ratio).
Could this be happening due to the map loading before the data is ready? If so how do you fix this?
Many thanks for all your effort here.
Jake
myDBR Team, Key Master
Note that in the Wikimedia's SVG map, Washington state is drawn with two paths with respective id's Washington1 and Washington2, where the Washington1 shows small parts of the coastline.
Could be that your code does try to set values for element id Washington and fails there. You can pretty much ignore the Washington1 and just use Washington2.
Whenever you have problems with JavaScript code, check the browser's debugger for errors. You can also debug the JavaScript code in the debugger to see what is going on.
The onclick-code in the demo was there just to demostrate how you can add linked reports to external objects like the SVG chart. You can use the code to drill down to state level with your data.
--
myDBR Team
ajdjackson, Member
Great thanks for the hint - got it working now!
It wasn't Washington but Louisana - wrong spelling in the data.
I'll have to build in some data cleansing to spot this.
Again many thanks fro all your help with this.
HAGWE
Jake
ajdjackson, Member
Good afternoon
I came across a wee quirk - not a biggie.
I've the report running as expected with the tool-tips and the click popups. This report is called from within an accordion. On IOS Safari the click event is not working - nothing happens.
However if I run the report standalone on IOS Safari the click popups work. So the accordion is somehow preventing the popup.
After a bit of Googling about IOS and on-click events I tried adding cursor:pointer (elem.style.cursor = "pointer";) to the elements but this didn't make any difference.
Any thoughts?
Thanks
Jake
myDBR Team, Key Master
It is a behavior between Safari (both desktop and mobile) and the accordion.
When the page is loaded, Safari loads the page and the onclick-handlers are assigned. Then the result set is used to generate those handlers is removed. Now when the accordion containing the SVG is opened, Safari reloads the object SVG image and at that point, no handlers are assigned as we removed the respective result set.
It is an easy fix though:
- Change the resultclass code for clicks-result set to be 'clicks noshow'.
- Do not remove the clicks result set at the end of add_clicks-function
This way the clicks-result set will be in the DOM (just not shown) and so the onclick-handlers are assigned every time the SVG chart is reloaded.
--
myDBR Team
ajdjackson, Member
Great - now works a treat!
Many thanks - excellent support as per usual.
Jake
You must log in to post.