Crosstab dbr.sum issue

(3 posts) (2 voices)
  1. ajdjackson, Member

    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;

  2. myDBR Team, Key Master

    It's easier if you open a support ticket with SQL export of the report output. This way we can replicate the report without the underlying tables and data.

    The OQty-column in dbr.hrd looks bit odd, but should not affect the dbr.sum.

    --
    myDBR Team

  3. ajdjackson, Member

    Hi

    I did the sql export but had a look through it before sending.

    I spotted the issue.

    I had, for some reason, used format() instead of dbr.colstyle. The integer had been converted to a string.

    Now fixed.

    Thanks

    Jake


Reply

You must log in to post.