dbr.chart change bar colour based on data

(7 posts) (2 voices)
  • Started 1 year ago by ajdjackson
  • Latest reply from ajdjackson
  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

    Posted 1 year ago #
  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

    Posted 1 year ago #
  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

    Posted 1 year ago #
  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

    Posted 1 year ago #
  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

    Posted 1 year ago #
  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

    Posted 1 year ago #
  7. ajdjackson, Member

    Thanks - that fixed the issue.

    Cheers

    Jake

    Posted 1 year ago #

Reply

You must log in to post.