1. 

   ajdjackson, Member

   Hi

   I'm looking to create a column chart where the the colour of the columns change depending on a value.

   This is where I've gotten to:

   
   
   declare v_color1 varchar(10);
   
   declare v_color2 varchar(10);
   
   declare v_color3 varchar(10);
   
   set v_color1 = '0xFF0000';
   
   set v_color2 = '0x008000';
   
   set v_color3 = '0x0074D9';
   
   select 'dbr.title','';
   
   select 'dbr.hidecolumns','cType';
   
   select 'dbr.chart','Column','Line Clean Time Analysis';
   
   select 'dbr.chart.options','rotateNames', 45;
   
   select 'dbr.chart.options','chart.scale',80;
   
   select 'dbr.chart.color','[bcolour]';
   
   SELECT  a.LC_FPO as "FPO #[FPO]",
   
           time_to_sec(a.LC_TimeTaken)/3600 as "Time Taken[tTime]",
   
           a.LC_CleanType as "Clean Type[cType]",
   
           if(a.LC_CleanType=1,v_color1,if(a.LC_CleanType=2,v_color2,v_color3))  as "[bcolour]"
   
   FROM tblLineClean a
   
           left outer join schedule b on a.LC_FPO=b.`Order`
   
           left outer join (select lab_fpono as lfpo, min(lab_date) as lDate from tbl_fpolab group by lab_fpono) f on a.LC_FPO=f.lfpo
   
   where a.LC_Line=inLine
   
   order by f.ldate,a.LC_FPO;

   The value of [bcolour] will be 1,2, or 3 and depending on the value I'd like a different colour.

   Is this possible?

   Thanks

   Jake
2. 

   myDBR Team, Key Master

   Jake,
   if you update to the latest version, you can also use the new color-chart option which allows for you to pass the color directly in the result set:

   select 'dbr.chart.options', 'color' ,'bcolour';

   The dbr.chart.color-command sets the colors and the order of the colors to be used in the result set. The first element of the result set get's the first color, second will get the second etc. So, what you need to do, is to use the same query for the dbr.chart.color:

   
   
   select 'dbr.chart.color', if(a.LC_CleanType=1,v_color1,if(a.LC_CleanType=2,v_color2,v_color3))
   
   from tblLineClean a
   
       left outer join schedule b on a.LC_FPO=b.`Order`
   
       left outer join (select lab_fpono as lfpo, min(lab_date) as lDate from tbl_fpolab group by lab_fpono) f on a.LC_FPO=f.lfpo
   
   where a.LC_Line=inLine
   
   order by f.ldate,a.LC_FPO;

   If the actual data query would be complex/heavy, you can put the data in temp table and query from that to both dbr.chart.color and to dbr.chart.

   --
   myDBR Team
3. 

   ajdjackson, Member

   Thanks for that.

   I wasn't aware that dbr.chart.color worked like that.

   One other issue I'm getting is that the chart doesn't display in Edge - ok in IE, Firefox and Chrome.

   Inspecting the page shows the <object> tag with data ok where the chart should be.

   Other charts appear OK in Edge - just seems to be this one. Strange!

   The chart code is as follows:

   select 'dbr.title','';

   select 'dbr.hidecolumns','cType'; select 'dbr.chart','Column','Line Change Over Time Analysis'; select 'dbr.chart.options','rotateNames', 45; select 'dbr.chart.options','chart.scale',80; select 'dbr.chart.color', if(a.LC_CleanType=1,v_color2,if(a.LC_CleanType=2,v_color3,v_color1)) from tblLineClean a left outer join schedule b on a.LC_FPO=b.`Order` left outer join (select lab_fpono as lfpo, min(lab_date) as lDate from tbl_fpolab group by lab_fpono) f on a.LC_FPO=f.lfpo where a.LC_Line=inLine order by f.ldate,a.LC_FPO; select 'dbr.chart.options','chartdirector', concat('$xMark1=$c->xAxis->addMark(',s_pos-1,', 0xff0000);'); select 'dbr.chart.options', 'tooltip', ''; select 'dbr.chart.options','axis', '', 'ChangOver, hrs';

   SELECT a.LC_FPO as "FPO #[FPO]", time_to_sec(a.LC_TimeTaken)/3600 as "Time Taken[tTime]", a.LC_CleanType as "Clean Type[cType]", if(a.LC_CleanType=1,v_color1,if(a.LC_CleanType=2,v_color2,v_color3)) as "[bcolour]" FROM tblLineClean a left outer join schedule b on a.LC_FPO=b.`Order` left outer join (select lab_fpono as lfpo, min(lab_date) as lDate from tbl_fpolab group by lab_fpono) f on a.LC_FPO=f.lfpo where a.LC_Line=inLine order by f.ldate,a.LC_FPO;

   Any idea why it won't display?

   Thanks

   Jake
4. 

   myDBR Team, Key Master

   Jake,
   what is the version and the build you are using? Also, what is the chart format you are using?

   Edge behaves differently to other browsers when doing SVG images inside OBJECT-tag. Due to that SVG-images are shown inside IMG-tag in newer versions.

   --
   myDBR Team
5. 

   ajdjackson, Member

   Hi

   I was running 4.9.4 (3387) and have now updated to 3398 but the chart still won't display.

   In environment settings I've selected Image Charts, SVG and embedded.

   This a the tag from the inspector:

   <object width="700" height="350" class="rstable" id="chart1" data="data:image/svg+xml;base64,......

   I've just checked a couple of other charts that display and they are indeed wrapped in image tag and not <object>.

   Other charts display fine - just this one (so far).

   Cheers

   Jake
6. 

   myDBR Team, Key Master

   Jake,
   the reason was that the tooltip was turned off in the chart, and as the imagemap (where the tooltip is) is no loger used, the chart was drawn as an object and Edge has trouble with it. The newest build now use img-tags in Edge, just run the updater and this should fix the issue.

   --
   myDBR Team
7. 

   ajdjackson, Member

   Thanks - that fixed the issue.

   Cheers

   Jake

Reply

You must log in to post.