Hi
Yes I'm hiding all the datacolumns when the user selects summary option.
It's a huge crosstab with 15 coulmns per month and a fair bit of processing to get the values.
So I thought that as it had to do the processing for both detailed and summary views I'd try to use the same report.
Cheers
Jake
select 'dbr.title',concat(inYear," Budget v Actual Analysis");
select 'dbr.subtitle','click Bud. Mats column for BOM Cost. Budget Materials are uplift by 8% from the BOM Cost';
select 'dbr.resultclass','budget';
select 'dbr.crosstab','Mon';
select 'dbr.crosstab.col','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec';
select 'dbr.hdr','Cust';
select 'dbr.calc','BMats','[BQty]*[BCost]';
select 'dbr.calc','SAVar','[ASales]-[BSales]';
select 'dbr.calc','PSAVar','[BSales]<>0?(([ASales]-[BSales])/[BSales])*100:0';
select 'dbr.calc','MAVar','[AMats]-[BMats]';
select 'dbr.calc','PMAVar','[BMats]<>0?(([AMats]-[BMats])/[BMats])*100:0';
select 'dbr.calc','AVadd','[ASales]-[AMats]';
select 'dbr.calc','BVadd','[BSales]-[BMats]';
select 'dbr.calc','AValVar','[AVadd]-[BVadd]';
select 'dbr.calc','AQtyVar','[AQty]-[BQty]';
select 'dbr.lockcolumns','SP';
select 'dbr.hidecolumn','BCost';
select 'dbr.colstyle','BMats','%0.0f; ;';
select 'dbr.colstyle','BQty','%0.0f; ;';
select 'dbr.colstyle','BSales','%0.0f; ;';
select 'dbr.colstyle','[ASales]','%0.0f; ;';
select 'dbr.colstyle','[SP]','%0.5f; ;';
select 'dbr.colstyle','[BCost]','%0.5f; ;';
select 'dbr.colstyle','AMats','%0.0f; ;';
select 'dbr.colstyle','PSAVar','%0.1f; ;[color:red;](%0.1N)';
select 'dbr.colstyle','PMAVar','%0.1f; ;[color:red;](%0.1N)';
select 'dbr.colstyle','SAVar','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.colstyle','MAVar','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.colstyle','AVadd','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.colstyle','BVadd','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.colstyle','AQty','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.colstyle','AQtyVar','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.colstyle','AValVar','%0.0f; ;[color:red;](%0.0N)';
select 'dbr.css','.budget .summary_level1 {background-color:#eff5f5;font-weight:bold;}';
select 'dbr.css','.budget .summary_level0 {font-weight:bold; background-color: rgb(3, 255, 247); border-top:2px solid black; border-bottom:2px solid black;}';
select 'dbr.css','table tr td:nth-child(4) {border-right:1px solid #000;border-left:1px solid #DDD;}';
select 'dbr.css','table tr td:nth-child(14n+19) {border-left:1.5px solid #000;}';
select 'dbr.css','table tr td:nth-child(14n+8) {border-left:1px solid #DDD;}';
select 'dbr.css','table tr td:nth-child(14n+12) {border-left:1px solid #DDD;}';
select 'dbr.css','table tr td:nth-child(14n+16) {border-left:1px solid #DDD;}';
select 'dbr.sum','ASales','BSales','BMats','SAVar','AVadd','BVadd','AQty','BQty','AQtyVar','AMats';
select 'dbr.hsum','ASales','BSales','BMats','AVadd','BVadd','BQty','AQty','AMats';
select b.CustName as "Customer[Cust]",
x.budProdID as "Item Code[iCode]",
a.`Item Description` as "Description[iDesc]",
case when a.`Last Cost Entered` <> 0 then a.`Last Cost Entered`
when f.Prod_SP <> 0 then f.Prod_SP
when c.SPrice <> 0 then c.SPrice
else 0
end as "SP[SP]",
date_format(x.budDate,"%b") as "Month[Mon]",
ifnull(d.QInv,0) as "Act. Qty[AQty]",
ifnull(x.budQty,0) as "Bud. Qty[BQty]",
if(x.budDate<=last_day(now()), null,0) as "Act. Qty Var [AQtyVar]",
ifnull(d.SVal,0) as "Act. Sales[ASales]",
ifnull(x.budQty,0)*if (a.`Last Cost Entered` = 0, ifnull(c.SPrice,0),a.`Last Cost Entered`) as "Bud. Sales[BSales]",
if(x.budDate<=last_day(now()), null,0) as "Sales Var.[SAVar]",
if(x.budDate<=last_day(now()), null,0) as "% Sales Var[PSAVar]",
e.MMats as "Act. Mats[AMats]",
if(x.budQty>0,null,0) as "Bud. Mats[BMats]",
if(x.budDate<=last_day(now()), null,0) as "Mats Var.[MAVar]",
if(x.budDate<=last_day(now()), null,0) as "% Mat Var.[PMAVar]",
ifnull(d.SVal,0)-ifnull(e.MMats,0) as "Act ValAdd[AVadd]",
ifnull(x.budQty,0)*if (a.`Last Cost Entered` = 0, ifnull(c.SPrice,0),a.`Last Cost Entered`)-(ifnull(x.budQty,0)*ifnull(y.BOMCost,0)) as "Bud ValAdd[BVadd]",
if(x.budDate<=last_day(now()), null,0) as "Act.ValAdd Var[AValVar]",
ifnull(y.BOMCost*mat_up,0) as "BOM[BCost]"
from tblbudgets x
left outer join
(select a.Parent_code as Prod,
sum(if(z.LPrice is null,b.`Last Cost Entered`,z.LPrice)*a.QuantityPer) as BOMCost
from bom_detail a
join `item master` b on a.Component_Code = b.`Item Code`
left outer join (
SELECT m1.ItemCode as Item, m1.Price as LPrice
FROM podetails m1 LEFT JOIN podetails m2
ON (m1.ItemCode= m2.ItemCode AND m1.LineID < m2.LineID)
WHERE m2.LineID IS NULL) z on a.Component_Code = z.Item
where a.Component_Code <> "LABOUR"
group by a.Parent_code) y on x.budProdID = y.Prod
left outer join `item master` a on x.budProdID = a.`Item Code`
left outer join customer b on x.budCustID = b.`Customer Code`
left outer join (SELECT m1.ItemCode as SPCode, m1.Price as SPrice
FROM invdetails m1 LEFT JOIN invdetails m2
ON (m1.ItemCode= m2.ItemCode AND m1.LineID < m2.LineID)
WHERE m2.LineID IS NULL)
c on x.budProdID = c.SPCode
left outer join (select a.ItemCode as IItem, last_day(b.InvoiceDate) as IDate, sum(a.LineTot) as SVal ,sum(a.QtyInvoiced) as QInv
from invdetails a
join invheader b on a.InvNumber = b.InvNumber
where b.InvoiceDate >= "2017-01-01"
group by a.ItemCode,last_day(b.InvoiceDate)) d on x.budProdID = d.IItem and x.budDate = d.IDate
left outer join (select ItemCode as MItem, last_day(InvoiceDate) as MDate, sum(Mats) as MMats from cum_order_tmp group by ItemCode,last_day(InvoiceDate)) e on x.budProdID = e.MItem and x.budDate = e.MDate
left outer join tblbudgets_sps f on x.budProdID=f.ProdID
where year(x.budDate) = inYear
union
select c.CustName as "Customer[Cust]",
a.ItemCode as "Item Code[iCode]",
a.Description as "Description[iDesc]",
ifnull(g.Prod_SP,ifnull(f.SPrice,0)) as "SP[SP]",
date_format(b.InvoiceDate,"%b") as "Month[Mon]",
sum(ifnull(a.QTYinvoiced,0)) as "Act. Qty[AQty]",
0 as "Bud. Qty[BQty]",
0 as "Act. Qty Var [AQtyVar]",
sum(a.LineTot)+ifnull(h.CNNet,0) as "Act. Sales[ASales]",
0 as "Bud. Sales[BSales]",
0 as "Sales Var.[SAVar]",
0 as "% Sales Var[PSAVar]",
e.MMats as "Act. Mats[AMats]",
0 as "Bud. Mats[BMats]",
0 as "Mats Var.[MAVar]",
0 as "% Mat Var.[PMAVar]",
ifnull(sum(a.LineTot),0)-ifnull(e.MMats,0) as "Act ValAdd[AVadd]",
0 as "Bud ValAdd[BVadd]",
0 as "Act.ValAdd Var[AValVar]",
ifnull(y.BOMCost,0) as "BOM[BCost]"
from invdetails a
join invheader b on a.InvNumber = b.InvNumber
join customer c on b.Customer = c.`Customer Code`
left outer join (select ItemCode as MItem, last_day(InvoiceDate) as MDate, sum(Mats) as MMats
from cum_order_tmp
where ItemCode not in ("MISC","TRANS") group by ItemCode,last_day(InvoiceDate)) e on a.ItemCode = e.MItem and last_day(b.InvoiceDate) = e.MDate
left outer join
(select a.Parent_code as Prod,
sum(if(z.LPrice is null,b.`Last Cost Entered`,z.LPrice)*a.QuantityPer) as BOMCost
from bom_detail a
join `item master` b on a.Component_Code = b.`Item Code`
left outer join (
SELECT m1.ItemCode as Item, m1.Price as LPrice
FROM podetails m1 LEFT JOIN podetails m2
ON (m1.ItemCode= m2.ItemCode AND m1.LineID < m2.LineID)
WHERE m2.LineID IS NULL) z on a.Component_Code = z.Item
where a.Component_Code <> "LABOUR"
group by a.Parent_code) y on a.ItemCode = y.Prod
left outer join (SELECT m1.ItemCode as SPCode, m1.Price as SPrice
FROM invdetails m1 LEFT JOIN invdetails m2
ON (m1.ItemCode= m2.ItemCode AND m1.LineID < m2.LineID)
WHERE m2.LineID IS NULL)
f on a.ItemCode = f.SPCode
left outer join tblbudgets_sps g on a.ItemCode=g.ProdID
left outer join
(select
c.ItemCode as CNCode,
last_day(a.InvoiceDate) as CNDate,
ifnull(sum(c.LineTot),0)*-1 as CNNet
from cnheader a
join cndetails c on a.CreditNumber = c.CreditNumber
where year(a.InvoiceDate) = inYear and c.ItemCode not in ("TRANS","MISC")
group by c.ItemCode,last_day(a.InvoiceDate)) h on a.ItemCode = h.CNCode and last_day(b.InvoiceDate) = h.CNDate
where b.InvoiceDate >= "2017-01-01" and concat(a.ItemCode,last_day(b.InvoiceDate)) not in (select concat(budProdID, last_day(budDate)) from tblbudgets) and a.ItemCode not in ("MISC","TRANS")
group by a.ItemCode,last_day(b.InvoiceDate)
order by 1,3;
