Hi
Apologies for resurrecting an old question but I've noticed an issue with the tooltips with this chart.
I've created a chart as below:
declare @sdate date,@edate date,@fedate date,@fyr int,@bnum int,@mact float,@mbud float,@cmax float,@cmax2 float,@myact float,@mybud float;
select @edate=eomonth(max(InvMon)) from [pharma].[dbo].[vw_mon_inv_sales];
select @bnum=paramval from [pharma].[dbo].[tblparams] where [paramType]='budnum';
select @fyr=FiscalYear from [pharma].[dbo].[tbldates] where CurDate=convert(date,getdate());
select @sdate=AccYearSDate from [pharma].[dbo].[tblAccountYears_v2] where [AccYear]=@fyr;
select @fedate=dateadd(month,1,AccYearEDate) from [pharma].[dbo].[tblAccountYears_v2] where [AccYear]=@fyr;
select @mact= max(mval)
from (
select sum(net)/1000000 as mval
from [pharma].[dbo].[vw_mon_inv_sales]
where invmon between @sdate and @edate
group by invmon
) a;
select @mbud = max(mval)
from (
select sum(bval)/1000000 as mval
from [pharma].[dbo].[vw_mon_bud_sales]
where bnum=@bnum and byear=@fyr
group by bper
) a;
select @myact= sum(net)/1000000 from [pharma].[dbo].[vw_mon_inv_sales]
where invmon between @sdate and @edate;
select @mybud = sum(bval)/1000000 from [pharma].[dbo].[vw_mon_bud_sales]
where bnum=@bnum and byear=@fyr and bper<=(select FiscalPeriod from [pharma].[dbo].[tbldates] where CurDate=@edate);
select @cmax = ceiling(iif(@mbud>@mact,@mbud,@mact));
select @cmax2 = ceiling(iif(@mybud>@myact,@mybud,@myact));
select 'dbr.chart','MSColumnDY','',600,175;;
select 'dbr.chart.color', '0x00FF00','0xFF0000' ;
select 'dbr.chart.options','format','SVG';
select 'dbr.chart.options','showLegend',0;
select 'dbr.chart.options','rotateNames',45;
select 'dbr.chart.options','axis','','£M';
select 'dbr.chart.options', 'series_axis', 'Actual YTD', 2;
select 'dbr.chart.options', 'series_axis', 'Budget YTD', 2;
select 'dbr.chart.options', 'scale', 0, @cmax, 1;
select 'dbr.chart.options', 'scale2', 0, @cmax2, 5;
select 'dbr.hidecolumn','adate';
select 'dbr.chart.options', 'label_color', 'Actual YTD', '0x00FF00';
select 'dbr.chart.options', 'label_color', 'Budget YTD', '0xFF0000';
with cte_budper as
(select FiscalPeriod as bper,
max(curDate) as bdate
from [pharma].[dbo].[tbldates]
where FiscalYear=@fyr
group by FiscalPeriod)
select format(eomonth(InvMon),'MMM') as SMonth,
'Actual' as SType,
sum(Net)/1000000 as sVal,
eomonth(InvMon) as adate
from [pharma].[dbo].[vw_mon_inv_sales]
where InvMon>=@sdate
group by eomonth(InvMon)
union
select format(b.bdate,'MMM') as SMonth,
'Budget' as SType,
sum(a.bval)/1000000 as sVal,
eomonth(b.bdate)
from [pharma].[dbo].[vw_mon_bud_sales] a
join cte_budper b on a.bper=b.bper
where eomonth(b.bdate)>=eomonth(@sdate) and a.byear=@fyr and a.bnum=@bnum
group by b.bdate
union
select 'YTD',
'Budget YTD',
sum(bval)/1000000,
@fedate
from [pharma].[dbo].[vw_mon_bud_sales]
where bnum=@bnum and byear=@fyr and bper<=(select FiscalPeriod from [pharma].[dbo].[tbldates] where CurDate=@edate)
union
select 'YTD',
'Actual YTD',
sum(Net)/1000000,
@fedate
from [pharma].[dbo].[vw_mon_inv_sales]
where invmon between @sdate and @edate
order by 4,2;
The chart displays correctly and the tooltips for the individual months are also correct. However, even although the columns for the YTD values are correct the tooltips are reversed.
Any thoughts?
Thanks
Jake
