dbr.hidecolumn.data

(5 posts) (2 voices)
  • Started 1 year ago by ajdjackson
  • Latest reply from myDBR Team
  1. ajdjackson, Member

    Hi

    I've created a crosstab and wish to give the user the option of getting a summary or detail view.

    I use a parameter called inType where 0 = detail and 1 = summary.

    I've the following lines in stored procedure:

    select 'dbr.crosstab','Mon'; select 'dbr.crosstab.col','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec';

    if inType = 1 then select 'dbr.hidecolumn.data','AQty'; select 'dbr.hidecolumn.data','BQty'; select 'dbr.hidecolumn.data','AQtyVar'; select 'dbr.hidecolumn.data','ASales'; select 'dbr.hidecolumn.data','BSales'; select 'dbr.hidecolumn.data','SAVar'; select 'dbr.hidecolumn.data','PSAVar'; select 'dbr.hidecolumn.data','AMats'; select 'dbr.hidecolumn.data','BMats'; select 'dbr.hidecolumn.data','MAVar'; select 'dbr.hidecolumn.data','PMAVar'; select 'dbr.hidecolumn.data','AVadd'; select 'dbr.hidecolumn.data','BVadd'; select 'dbr.hidecolumn.data','AValVar'; end if;

    This hides the monthly detail columns and only shows the hsum columns which is what I want.
    However the column titles now have random month names above each one where I was expecting only 'Total' spanned across them.

    I reckon it must have something to do with me setting the crosstab columns and have tried:

    if inType = 0 then select 'dbr.crosstab.col','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'; end if;

    but this didn't make any difference.

    Is it possible to do what I wish to?

    Cheers

    Jake

    Posted 1 year ago #
  2. myDBR Team, Key Master

    Are you trying to hide all data-columns, your code does not show what the query actually is. Also the relation between dbr.hidecolumn.data-columns and dbr.crosstab.col-columns is not clear.

    If you just want to show the hsum column without any data columns, why bother using the crosstab at all? Why not just query with group by?

    --
    myDBR Team

    Posted 1 year ago #
  3. ajdjackson, Member

    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;

    Posted 1 year ago #
  4. myDBR Team, Key Master

    if you have a very complex query (as you do), the easiest solution is to push the result into a temporary table and then query the two separate queries from the calculated temporary table. Then you need to do the complex query just once.

    Also, this would give you the opportunity to split the query to more easily maintainable pieces.

    --
    myDBR Team

    Posted 1 year ago #
  5. myDBR Team, Key Master

    Jake,
    latest myDBR version (4.7.3) includes support for hiding all crosstab data columns. You can either hide them individually or all at once with single command (dbr.hidecolumn.data.all, {1/0}').

    The version also user separator lined between crosstab data sets, so no longer need for hard to maintain CSS rules for this.

    --
    myDBR Team

    Posted 1 year ago #

Reply

You must log in to post.