Hi
I've created the following crosstab.
Everything working as it should except the dbr.sum for the HTar column. Instead of getting the total for that column I'm only getting the last entry. The HQty column is summing as expected.
Any thoughts?
Cheers
Jake
select 'dbr.hdr','MO','SKU','Prod','Class','OQty'; select 'dbr.cellstyle', 'TAch', 'style'; select 'dbr.hidecolumn','Hr'; select 'dbr.hidecolumn', 'style'; select 'dbr.sum','HQty','HTar'; select 'dbr.summary.options','limit_summary_level','=0'; select 'dbr.colstyle','Per','[min-width:80px;max-width:80px;font-weight:bold;white-space: nowrap;overflow: hidden; text-overflow: ellipsis;]'; select 'dbr.colstyle','MO','[min-width:120px;max-width:120px;font-weight:bold;white-space: nowrap;overflow: hidden; text-overflow: ellipsis;]'; select 'dbr.colstyle','SKU','[min-width:80px;max-width:80px;font-weight:bold;white-space: nowrap;overflow: hidden; text-overflow: ellipsis;]'; select 'dbr.colstyle','Prod','[min-width:275px;max-width:275px;font-weight:bold;white-space: nowrap;overflow: hidden; text-overflow: ellipsis;]'; select 'dbr.colstyle','Class','[min-width:120px;max-width:120px;font-weight:bold;white-space: nowrap;overflow: hidden; text-overflow: ellipsis;]'; select 'dbr.colstyle','OQty','[min-width:80px;max-width:80px;font-weight:bold;white-space: nowrap;overflow: hidden; text-overflow: ellipsis;]'; select 'dbr.colstyle','TAch','[color:white; text-align:center; font-size:1.2em;]';
select x.hr as "Hour[Hr]", x.hr_int as "Period[Per]", a.moid as "Manu.Order[MO]", a.prodid as "SKU[SKU]", a.proddesc as "Product[Prod]", a.ClassificationDescription as "Class[Class]", a.oqty as "Order Qty[OQty]", a.iqty as "Hrly Qty[HQty]", a.htar as "Hrly Tar[HTar]", a.tach as "% of Tar[TAch]", a.cqty as "Cum.Qty[C]", a.pcomp as "% Complete[PComp]", if (a.cqty>0,if(a.tach>100,'background-color:green',if(a.tach>80,'background-color:orange','background-color:red')),"") as "style" from tblhours x left outer join (select f.chr as hr, f.cmo as cmo, f.iqty as iqty, f.cqty, g.moid as moid, g.prodid, g.proddesc, h.ClassificationDescription, g.oqty, format(60/g.ratemin,0) as htar, (f.iqty/(60/g.ratemin))*100 as tach, ((f.cqty+g.pqty)/g.oqty)*100 as pcomp from tmp_oee_hour f join tmp_oee g on f.cmo=g.mo join wm_classifications h on g.class=h.Classification where g.ac=v_line and f.cshft="D") a on x.hr=a.hr where x.hr between 7 and 18 order by x.hr;
