Hi
Thanks for that.
I've another issue regarding cross-tabs that I can't figure out.
Any reason why the below doesn't give a cross-tab?
Thanks
Jake
select 'dbr.resultclass','vaddclock';
select 'dbr.text','Value Added by £ Labour','head';
select 'dbr.crosstab','pDate','Mts';
set daymon = 1;
while (daymon <= day(maxrevdate)) do
if dayname(date_add(date_format(concat(inYear,"-",inMon,"-01"),"%Y-%m-%d"), interval daymon-1 day)) not in ("Friday","Saturday") then
select 'dbr.crosstab.col',date_format(date_add(date_format(concat(inYear,"-",inMon,"-01"),"%Y-%m-%d"), interval daymon-1 day),"%d-%a");
end if;
set daymon=daymon+1;
end while;
select 'dbr.colstyle','Mts','[min-width:70px;max-width:70px;]%0.2f';
select 'dbr.havg','Mts';
select 'dbr.colstyle','mType','[min-width:200px;max-width:200px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;text-align:right;font-weight:bold;]';
select 'dbr.colstyle','mType1','[min-width:200px;max-width:200px;white-space:nowrap;overflow:hidden;text-overflow: ellipsis;text-align:left;font-weight:bold;]';
select 'dbr.css','.vaddclock tr.summary_row {display:none;}';
select 'dbr.css','.vaddclock thead.tableFloatingHeaderOriginal {display:none;}';
select 'dbr.css','table.vaddclock {margin-bottom:0;}';
select 'dbr.css','.vaddclock td:last-child {font-weight:bold;}';
select 'dbr.css','.vaddclock .title {margin-left:10%;}';
select 'dbr.search',0;
select 'dbr.calc','[Mts]','([Rev]-[mats])/[lCost]';
select 'dbr.hidecolumns','Rev';
select "Value Added per £ Labour" as "[mType]",
date_format(a.lDate,"%d-%a") as "Date[pDate]",
null as "£[Mts]",
"Value Added per £ Labour" as "[mType1]",
b.Rev as "[Rev]",
sum(a.dCost) as "[lCost]",
b.mCost as "[mats]"
from daily_lab a
join dailyprodtsum b on a.lDate=b.dpDate
where a.lDate <= maxrevdate
group by a.lDate
order by a.lDate;
