Chart Type Question

(4 posts) (2 voices)
  • Started 2 years ago by ajdjackson
  • Latest reply from myDBR Team

Tags:

  1. ajdjackson, Member

    Hi

    I wish to create the following chart:

    a value for each day of the month as a bar and then I'd like a running total of these values as line or spline curve.

    I've the query running fine but I can't seem to find a chart type that I can use to do the above.

    Does one exist?

    Cheers

    Jake

    Posted 2 years ago #
  2. myDBR Team, Key Master

    You can use MSColumnLineDY (or MSColumnSplineDY if you update). You will have to calculate running total by yourself though.

    --
    myDBR Team

    Posted 2 years ago #
  3. ajdjackson, Member

    Hi

    I tried your suggestion but it didn't produce what I was after.

    I have only 1 column per day and I wanted to add a cumulative line/curve to this simple bar chart.

    I've been playing around with this myself and have managed to get a chart which does what I wish but is it the best way?

    I created a string of the cumulative daily value:

    declare cum varchar(500); SET @runtot:=0; select

    group_concat(@runtot := @runtot + a.Hours)

    into cum from (select day(b.daymonth) as "Day", ifnull(sum(if ((timediff(a.lab_end,a.lab_start))>=0, ((time_to_sec(timediff(a.lab_end,a.lab_start)))/3600)*a.lab_numops,(((time_to_sec(timediff("24:00:00",a.lab_start)))/3600) + (time_to_sec(timediff(a.lab_end,"00:00:00")))/3600)*a.lab_numops)),0) as "Hours" from calendar b left outer join tbl_fpolab a on b.daymonth = a.lab_date group by b.daymonth) a;

    I then added the following chart options:

    select 'dbr.title',""; SELECT 'dbr.chart', 'Column','Monthly \'Productive\' Hours'; select 'dbr.chart.options', 'chartdirector', ' $linelayer = $c->addLineLayer(); $linelayer->moveFront(); $linelayer->setLineWidth(3); $linelayer->setUseYAxis2();

    '; select 'dbr.chart.options', 'chartdirector', concat("$linelayer->addDataSet(array(",cum,"),0xFF0000, \"Target\");"); select 'dbr.chart.options','axis', 'Day of the Month', 'Daily Hours Used', 'Cumulative Hours Used'; SET @runtot:=0; select a.Day as "Day[Day]", a.Hours as "Prod.Hours[PHrs from (select day(b.daymonth) as "Day", ifnull(sum(if ((timediff(a.lab_end,a.lab_start))>=0, ((time_to_sec(timediff(a.lab_end,a.lab_start)))/3600)*a.lab_numops,(((time_to_sec(timediff("24:00:00",a.lab_start)))/3600) + (time_to_sec(timediff(a.lab_end,"00:00:00")))/3600)*a.lab_numops)),0) as "Hours" from calendar b left outer join tbl_fpolab a on b.daymonth = a.lab_date group by b.daymonth) a;

    Cheers

    Jake

    Posted 2 years ago #
  4. myDBR Team, Key Master

    Still might be easier just to use a multiseries chart where one of the series is shown as a column and another one as a line / spline. myDBR has these charts inbuilt so you do not need to do them manually.

    Multiseries column + cumulative spline

    --
    myDBR Tea,

    Posted 2 years ago #

Reply

You must log in to post.