dbr.chart question

(9 posts) (2 voices)
  • Started 1 year ago by ajdjackson
  • Latest reply from ajdjackson
  1. ajdjackson, Member

    Hi

    I've created a simple MSColumn chart.

    The x-axis are the months of the year and the y-axis are the actual and budget sales for each month.

    I've create a couple of variables which total actual and budget sales for the year and I wish to add these to chart.

    I've got as far as this:

    SELECT 'dbr.chart', 'MScolumn', ''; select 'dbr.chart.options','legend.position', 'bottom';

    select 'dbr.hidecolumn',4;

    select date_format(x.budDate,"%b") as "Month[Mon]", "Budget", sum(ifnull(x.budQty,0)*c.bSP) as "Bud. Sales[BSales]", x.budDate from budgets x join budget_tmp c on x.budProdID = c.bProdID /*left outer join sum_sales_order_tmp e on x.budProdID = e.InvItem and x.budDate = e.InvDate*/ where year(x.budDate) = inYear group by last_day(x.budDate)

    union select date_format(d.aDate,"%b") as "Month[Mon]", "Actual", sum(ifnull(d.aSales,0)) as "Act. Sales[ASales]", d.aDate from act_sales d where year(d.aDate) = inYear group by last_day(d.aDate)

    union select "Total", "Budget", totbudsales, LAST_DAY(date_add(date_format(concat(inYear,"-12-01"),"%Y-%m-%d"), INTERVAL 1 MONTH))

    union select "Total", "Actual", totactsales, LAST_DAY(date_add(date_format(concat(inYear,"-12-01"),"%Y-%m-%d"), INTERVAL 1 MONTH))

    order by 4;

    This produces the chart ok but the as the last pair of values are totals for the year the individual months are small columns in relation to the yearly total ones.

    What I would like to do is to have a secondary y-axis and have the yearly total pair of columns assigned to it.

    Is this possible?

    Many thanks

    Jake

    Posted 1 year ago #
  2. myDBR Team, Key Master

    So basically you would want to combine two column charts as one (one for months, one for totals) so that they would have separate Y-axis? It is not that common to show totals and individual items at the same chart as different scales might confuse user.

    Anyways, you would need a chart like 'MSColumnDY' which does not exists at the moment. Adding one would be relatively easy though, if you need one.

    --
    myDBR Team

    Posted 1 year ago #
  3. ajdjackson, Member

    Hi

    Yes that sounds just like what I need.

    I did notice that there is a MSColumnLineDY but no MSColumnDY so one would be great.

    Many thanks

    Jake

    Posted 1 year ago #
  4. myDBR Team, Key Master

    Jake,
    the latest build includes the MSColumnDY (and MSColumnDY3D chart). The build also has optimized Excel export.

    --
    myDBR Team

    Posted 1 year ago #
  5. ajdjackson, Member

    Hi

    Thanks for doing this.

    I've looked at the example in the demo but I can't seem to get it to work :(

    This is what I've got:

    [code]
    SELECT 'dbr.chart', 'MScolumnDY', '';
    select 'dbr.chart.options','title_font_size', '18px';
    select 'dbr.chart.options','title_color', '0x1254B8';
    select 'dbr.chart.options','legend.position', 'bottom';
    select 'dbr.chart.options', 'scale', 0, 1500000, 100000;
    select 'dbr.chart.options', 'scale2', 0, 12000000, 1000000;

    select 'dbr.hidecolumn',4;

    select
    date_format(x.budDate,"%b") as "Month[Mon]",
    "Budget",
    sum(ifnull(x.budQty,0)*c.bSP) as "Sales",
    x.budDate
    from budgets x
    join budget_tmp c on x.budProdID = c.bProdID
    where year(x.budDate) = inYear
    group by last_day(x.budDate)

    union
    select
    date_format(d.aDate,"%b") as "Month[Mon]",
    "Actual",
    sum(ifnull(d.aSales,0)) as "Sales",
    d.aDate
    from act_sales d
    where year(d.aDate) = inYear
    group by last_day(d.aDate)

    order by 4;
    {/code]

    What I'm trying to do is to plot the Monthly Actual v Budget Sales and then have the last 2 columns showing the yearly Actual and the total budget sales for the year.

    Cheers

    Jake

    Posted 1 year ago #
  6. myDBR Team, Key Master

    Jake,
    you do not have the yearly actual and the total budget sales for the year in the query anywhere. Just the items for the y-axis 1.

    You also do not need to use dbr.hidecolumn with charts.

    --
    myDBR Team

    Posted 1 year ago #
  7. ajdjackson, Member

    DOH!

    Still not getting it right. It must be how I'm structuring the query.

    I've created two variables - totbudsale and totactsales - prior to creating the chart.

    declare totbudsales float; declare totactsales float;

    select sum(ifnull(x.budQty,0)*c.bSP) into totbudsales from budgets x join budget_tmp c on x.budProdID = c.bProdID where year(x.budDate) = inYear;

    select sum(ifnull(d.aSales,0)) into totactsales from act_sales d where year(d.aDate) = inYear;

    SELECT 'dbr.chart', 'MScolumnDY', ''; select 'dbr.chart.options','title_font_size', '18px'; select 'dbr.chart.options','title_color', '0x1254B8'; select 'dbr.chart.options','legend.position', 'bottom'; select 'dbr.chart.options', 'scale', 0, 1500000, 100000; select 'dbr.chart.options', 'scale2', 0, 12000000, 1000000;

    select date_format(x.budDate,"%b") as "Month[Mon]", "Budget", sum(ifnull(x.budQty,0)*c.bSP) as "Sales", x.budDate from budgets x join budget_tmp c on x.budProdID = c.bProdID /*left outer join sum_sales_order_tmp e on x.budProdID = e.InvItem and x.budDate = e.InvDate*/ where year(x.budDate) = inYear group by last_day(x.budDate)

    union select date_format(d.aDate,"%b") as "Month[Mon]", "Actual", sum(ifnull(d.aSales,0)) as "Sales", d.aDate from act_sales d where year(d.aDate) = inYear group by last_day(d.aDate)

    union select "Total", "Budget", totbudsales, LAST_DAY(date_add(date_format(concat(inYear,"-12-01"),"%Y-%m-%d"), INTERVAL 1 MONTH))

    union select "Total", "Actual", totactsales, LAST_DAY(date_add(date_format(concat(inYear,"-12-01"),"%Y-%m-%d"), INTERVAL 1 MONTH))

    order by 4;

    Cheers and thanks

    Jake

    Posted 1 year ago #
  8. myDBR Team, Key Master

    When you have a double Y-chart, myDBR will by default put the last set in the second Y-axis. You can however place the sets to the axis using the series_axis-option.

    An example.

    --
    myDBR Team

    Posted 1 year ago #
  9. ajdjackson, Member

    Great - working now.

    Seeing your example indetail really helped.

    Thanks

    Jake

    Posted 1 year ago #

Reply

You must log in to post.