U8编写自定义报表的3种方法.doc

编写自定义报表的三种方案 U8本身提供的报表有些客户用不到，有些要通过好几张报表合成才能完成客户需要的数据，因此实施过程中难免要根据客户的需要编写自定义报表。下面介绍三种编制自定义报表的方法。 自定义报表栏写SQL语句。此方法相对简单。如下举例 宏越家具要求所有的存货按销售订单来采购，入库，出库，并可查询订单的执行统计表。目前软件中有插件可以查询，但不直观，看数据比较的类，因此直接作自定义表进行查询，具体语句如下： select m.ccuscode as 客户编码,m.ccusname as 客户名称,n.dDate as 单据时间,n.cSOCode as 生产令,f.dPreMoDate as 完工日期, f.irowno as 行号,s.cinvcode as 成品编码,s.cinvname as 成品,f.iQuantity as 成品数量, r.BaseQtyN as 分子用量,r.BaseQtyD as 分母用量,r.BaseQtyN/r.BaseQtyD as 单位用量, c.cinvcode as 存货编码,c.cinvname as 存货名称,c.cInvAddCode as 存货代码,c.cInvStd as 规格型号,b.cFree3 as 颜色, c.cinvccode as 存货大类,g.cComUnitName as 辅计量,h.cComUnitName as 主计量,g.iChangRate as 换算率, f.iQuantity*(r.BaseQtyN/r.BaseQtyD) as 计划数量, round((f.iQuantity*(r.BaseQtyN/r.BaseQtyD))/isnull(g.iChangRate,1),0) as 计划件数, b.iquantity as 订单数量,b.iquantity/isnull(g.iChangRate,1) as 订单件数, sum(d.fValidQuantity) as 到货数量,sum(d.fValidQuantity)/isnull(g.iChangRate,1) as 到货件数, sum(d.fValidinQuan) as 入库数量,sum(d.fValidinQuan)/isnull(g.iChangRate,1) as 入库件数--,k.iquantity from so_somain n left join so_sodetails f on n.id=f.id left join inventory s on f.cinvcode=s.cinvcode and s.cinvccode like 0106% left join bas_part p on f.cinvcode=p.invcode left join bom_parent q on p.partid=q.ParentId left join bom_opcomponent r on q.bomid=r.BomId left join bas_part p1 on r.ComponentId=p1.partid left join inventory c on p1.invcode=c.cinvcode left join PO_Podetails b on f.iSOsID=b.sodid and b.sotype=1 and c.cinvcode=b.cinvcode left join PU_ArrivalVouchs d on b.ID=d.iPOsID left join inventoryclass e on c.cinvccode=e.cinvccode left join ComputationUnit g on c.cSTComUnitCode=g.cComUnitCode left join ComputationUnit h on c.cComUnitCode=h.cComUnitCode left join customer m on n.ccuscode=m.ccuscode --left join rdrecords k on f.iSOsID=k.isodid and k.isotype=1 where f.cinvcode=01060303005 group by m.ccuscode,m.ccusname,n.dDate,n.cSOCode,f.dPreMoDate, f.irowno,s.cinvcode,s.cinvname,f.iQuantity, r.BaseQtyN,r.BaseQtyD, c.cinvcode,c.cinvname,c.cInvAdd