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