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
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
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
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,

Reply

You must log in to post.