|
U8本身提供的报表有些客户用不到，有些要通过好几张报表合成才能完成客户需要的数据，因此实施过程中难免要根据客户的需要编写自定义报表。下面介绍三种编制自定义报表的方法。 1、自定义报表栏写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.cInvAddCode,c.cInvStd,b.cFree3, c.cinvccode,g.cComUnitName,h.cComUnitName,g.iChangRate, b.iquantity--,k.iquantity 2、利用作业完成自定义报表 基本的思路是这样的：先用过程生成要编制的报表，并且将它插入到临时表中。把过程做成作业，让SQL SERVER在后台定时刷新这个报表。在自定义报表中，然后简单的select语句列出报表即可。 此方法相对灵活，可以按自己的需求来处理，而且占用资源也不大 如我在出版印刷项目中，计算运费的报表。 if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[yf]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[yf] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create procedure yf as --超过2吨在实际计算，不足2吨按2吨计算 if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[dispatch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[dispatch] CREATE TABLE [dbo].[dispatch] ( [dlid] int ,[cdlcode] [varchar](20), [cdefine7] [decimal] (18,3) ) ON [PRIMARY] INSERT INTO [dbo].[dispatch] ( [dlid],[cdlcode] , [cdefine7] ) select a.dlid,a.cdlcode, case when sum(isnull(b.cdefine26,0))>'2'then sum(b.cdefine26)*(isnull(c.ccusdefine13,0)) when sum(isnull(b.cdefine26,0))<'2' then 2*(isnull(c.ccusdefine13,0)) end from dispatchlist a left join dispatchlists b on a.dlid=b.dlid left join customer c on a.ccuscode=c.ccuscode where a.cSCCode='03' group by a.dlid,a.cdlcode,c.ccusdefine13 ---------------------------------------------------- update a set a.cdefine7=b.cdefine7 from dispatchlist a,dispatch b where a.dlid=b.dlid and a.cdlcode=b.cdlcode return GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO 3、利用存储过程编写报表 此方法最灵活，目前u8中很多报表都适合通过调用存储过程来完成报表的。 如编制一张vmi的报表 CREATE PROCEDURE hzuf_VMI_RdRecord (@chrWhere varchar(8000)) as declare @chrsql varchar(8000) declare @chrrq varchar(2000) declare @chrrqs varchar(2000) declare @chrrqe varchar(2000) declare @i int declare @j int Select @chrrq=cValue from AccInformation Where cSysId='IA' and cName='dIAStartDate' set @chrrqs=''''+@chrrq+'''' set @i=len(@chrWhere) set @j=0 while @j<@i begin if substring(@chrWhere,@j,4)='日期>=' begin set @chrrqs=substring(@chrWhere,@j+4,12) set @chrWhere=left(@chrWhere,@j-1)+' 1=1 '+substring(@chrWhere,@j+16,@i-@j-15) set @j=@i end set @j=@j+1 end set @i=len(@chrWhere) set @j=0 while @j<@i begin if substring(@chrWhere,@j,4)='日期<=' begin set @chrrqe=substring(@chrWhere,@j+4,12) set @chrWhere=left(@chrWhere,@j-1)+' 1=1 '+substring(@chrWhere,@j+16,@i-@j-15) set @j=@i end set @j=@j+1 end if exists(select name from sysobjects where name='hzuf_tbl_VMI_RdRecord') drop table hzuf_tbl_VMI_RdRecord --建临时空表 set @chrsql='Create TAble hzuf_tbl_VMI_RdRecord ([自动编号] int,[仓库编码] varchar(10),[仓库名称] varchar(20),[存货编码] varchar(20),' set @chrsql=@chrsql+'[存货名称] varchar(60),[存货代码] varchar(30),[规格型号] varchar(60),[产地] varchar(100),[存货大类编码] varchar(12),' set @chrsql=@chrsql+'[存货大类名称] varchar(20),[主计量单位编码] varchar(10),[存货自由项1] varchar(20),[存货自由项2] varchar(20),' set @chrsql=@chrsql+'[存货自由项3] varchar(20),[存货自由项4] varchar(20),[存货自由项5] varchar(20),[存货自由项6] varchar(20),[存货自由项7] varchar(20),' set @chrsql=@chrsql+'[存货自由项8] varchar(20),[存货自由项9] varchar(20),[存货自由项10] varchar(20),[收发类别编码] varchar(5),' set @chrsql=@chrsql+'[辅计量数量] float,[代管采购入库数量] float,[现金采购入库数量] float,[委外加工入库数量] float,[半成品组件入库数量] float,[半成品零件入库数量] float,[成品生产入库数量] float,[成品三包解析入库数量] float,[调拨入库数量] float,[材料退库数量] float,[半成品退库数量] float,[成品入库数量] float,[成品退库数量] float,[盘盈数量] float,[总计入库数量] float,[材料生产领用数量] float,[材料其他领用数量] float,[销售成品出库数量] float,[材料销售出库数量] float,[成品返修出库数量] float,[委外加工出库数量] float,[调拨出库数量] float,[盘亏出库数量] float,[工装领用出库数量] float,[期初结存数量] float,[期初结存辅计量数量] float,[项目大类编码] varchar(20),' set @chrsql=@chrsql+'[项目大类名称] varchar(20),[项目编码] varchar(60),[项目名称] varchar(60),[收发标志] int,[材料厂商编码] varchar(120),[材料厂商名称] varchar(100))' exec(@chrsql) set @chrsql='' set @chrsql=' Insert into hzuf_tbl_VMI_RdRecord ([自动编号],[仓库编码],[仓库名称],[存货编码],[存货名称],[存货代码],[规格型号],[产地],[存货大类编码],' set @chrsql=@chrsql+' [存货大类名称],[主计量单位编码],[存货自由项1],[存货自由项2],[存货自由项3],[存货自由项4],[存货自由项5],[存货自由项6],[存货自由项7],[存货自由项8],[存货自由项9],[存货自由项10],' set @chrsql=@chrsql+' [收发类别编码],[辅计量数量],[代管采购入库数量],[现金采购入库数量],[委外加工入库数量],[半成品组件入库数量],[半成品零件入库数量],[成品生产入库数量],[成品三包解析入库数量],[调拨入库数量],[材料退库数量],[半成品退库数量],[成品入库数量],[成品退库数量],[盘盈数量],[总计入库数量],[材料生产领用数量],[材料其他领用数量],[销售成品出库数量],[材料销售出库数量],[成品返修出库数量],[委外加工出库数量],[调拨出库数量],[盘亏出库数量],[工装领用出库数量],[期初结存数量],[期初结存辅计量数量],[项目大类编码],[项目大类名称],[项目编码],[项目名称],[收发标志],[材料厂商编码],[材料厂商名称]) ' set @chrsql=@chrsql+' SELECT rdrecords.autoid,Warehouse.cWhCode, Warehouse.cWhName, RdRecords.cInvCode, Inventory.cInvName, Inventory.cInvAddCode,Inventory.cInvStd' set @chrsql=@chrsql+' ,Inventory.cAddress , Inventory.cInvCCode,InventoryClass.cInvCName ,Inventory.cComUnitCode ' set @chrsql=@chrsql+' ,[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10]' set @chrsql=@chrsql+' ,Left(RdRecord.cRdCode,4) As cRdCode' set @chrsql=@chrsql+' ,(CASE WHEN Inventory.iGroupType = 0 THEN 0 WHEN Inventory.iGroupType = 2 THEN iNum WHEN Inventory.iGroupType = 1 ' set @chrsql=@chrsql+' THEN iQuantity /(SELECT iChangRate FROM ComputationUnit CU_F WHERE Inventory.cSTComUnitCode = CU_F.cComUnitCode) END) AS iNum' set @chrsql=@chrsql+' ,(case RdRecord.cRdCode when ''101'' then iQuantity else 0 end) as iRQty101,(case RdRecord.cRdCode when ''102'' then iQuantity else 0 end) as iRQty102,(case RdRecord.cRdCode when ''103'' then iQuantity else 0 end) as iRQty103,(case RdRecord.cRdCode when ''1041'' then iQuantity else 0 end) as iRQty1041,(case RdRecord.cRdCode when ''1042'' then iQuantity else 0 end) as iRQty1042,(case RdRecord.cRdCode when ''1051'' then iQuantity else 0 end) as iRQty1051,(case RdRecord.cRdCode when ''1052'' then iQuantity else 0 end) as iRQty1052,(case RdRecord.cRdCode when ''106'' then iQuantity else 0 end) as iRQty106,(case RdRecord.cRdCode when ''107'' then iQuantity else 0 end) as iRQty107,(case RdRecord.cRdCode when ''108'' then iQuantity else 0 end) as iRQty108,(case RdRecord.cRdCode when ''109'' then iQuantity else 0 end) as iRQty109,(case RdRecord.cRdCode when ''110'' then iQuantity else 0 end) as iRQty110,(case RdRecord.cRdCode when ''111'' then iQuantity else 0 end) as iRQty111,(case left(RdRecord.cRdCode,1) when ''1'' then iQuantity else 0 end) as iRQty,(case RdRecord.cRdCode when ''2011'' then iQuantity else 0 end) as iCQty2011,(case RdRecord.cRdCode when ''2012'' then iQuantity else 0 end) as iCQty2012,(case RdRecord.cRdCode when ''2031'' then iQuantity else 0 end) as iCQty2031,(case RdRecord.cRdCode when ''2032'' then iQuantity else 0 end) as iCQty2032,(case RdRecord.cRdCode when ''204'' then iQuantity else 0 end) as iCQty204,(case RdRecord.cRdCode when ''205'' then iQuantity else 0 end) as iCQty205,(case RdRecord.cRdCode when ''206'' then iQuantity else 0 end) as iCQty206,(case RdRecord.cRdCode when ''207'' then iQuantity else 0 end) as iCQty207,(case RdRecord.cRdCode when ''208'' then iQuantity else 0 end) as iCQty208,iNum-iNum AS iQCJCSL, iNum-iNum AS iQCJCJS,RdRecords.cItem_class ,RdRecords.cItemCName ' set @chrsql=@chrsql+' ,RdRecords.cItemCode ,RdRecords.cName , RdRecord.bRdFlag ,rdrecords.cDefine33 ,vendor.cVenName' set @chrsql=@chrsql+' FROM RdRecord ' set @chrsql=@chrsql+' INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID ' set @chrsql=@chrsql+' INNER JOIN Inventory ON RdRecords.cInvCode = Inventory.cInvCode ' set @chrsql=@chrsql+' INNER JOIN Warehouse ON RdRecord.cWhCode = Warehouse.cWhCode ' set @chrsql=@chrsql+' INNER JOIN InventoryClass On Inventory.cInvCCode = InventoryClass.cInvCCode ' set @chrsql=@chrsql+' left join vendor on rdrecords.cDefine33 = vendor.cVenCode' set @chrsql=@chrsql+' Where ((RdRecord.dDate < '''+@chrrq+''' And IsNull(RdRecord.bIsSTQc,0) = 1)' set @chrsql=@chrsql+' Or (RdRecord.dDate >= '''+@chrrq+''' And IsNull(RdRecord.bPUFirst,0) = 0' set @chrsql=@chrsql+' And IsNull(RdRecord.bIAFirst,0) = 0' set @chrsql=@chrsql+' And (not (RdRecord.cBusType = ''假退料''and RdRecord.cVouchType = 11))))' if ltrim(rtrim(@chrrqs))<>'' set @chrsql=@chrsql+' and dDate>= '+@chrrqs if ltrim(rtrim(@chrrqe))<>'' set @chrsql=@chrsql+' and dDate<= '+@chrrqe if ltrim(rtrim(@chrwhere))<>'' set @chrsql=@chrsql+' and '+@chrwhere exec(@chrsql) set @chrsql='update a set a.[期初结存数量]=b.iQCJCSL,a.[期初结存辅计量数量]=b.iQCJCJS from hzuf_tbl_VMI_RdRecord a' set @chrsql=@chrsql+' left join (' set @chrsql=@chrsql+'SELECT RdRecords.autoid,RdRecord.cWhCode,RdRecords.cInvCode,RdRecords.cDefine33' set @chrsql=@chrsql+' ,(CASE WHEN bRdFlag <>0 THEN iQuantity ELSE -iQuantity END) AS iQCJCSL' set @chrsql=@chrsql+' , (CASE WHEN Inventory.iGroupType = 0 THEN 0 WHEN Inventory.iGroupType = 2 THEN (CASE WHEN bRdFlag <>0 THEN iNUM ELSE -iNUM END) ' set @chrsql=@chrsql+' WHEN Inventory.iGroupType = 1 THEN (CASE WHEN bRdFlag <> 0 THEN iQuantity ELSE -iQuantity END)/(SELECT iChangRate' set @chrsql=@chrsql+' FROM ComputationUnit CU_F WHERE Inventory.cSTComUnitCode = CU_F.cComUnitCode) END) AS iQCJCJS' set @chrsql=@chrsql+' FROM RdRecord ' set @chrsql=@chrsql+' INNER JOIN RdRecords ON RdRecord.ID = RdRecords.ID ' set @chrsql=@chrsql+' INNER JOIN Inventory ON RdRecords.cInvCode = Inventory.cInvCode ' set @chrsql=@chrsql+' WHERE ((RdRecord.dDate < '+@chrrq+' And IsNull(RdRecord.bIsSTQc,0) = 1)' set @chrsql=@chrsql+' Or (RdRecord.dDate >= '+@chrrq set @chrsql=@chrsql+' And IsNull(RdRecord.bPUFirst,0) = 0' set @chrsql=@chrsql+' And IsNull(RdRecord.bIAFirst,0) = 0' set @chrsql=@chrsql+' And (not (RdRecord.cBusType = ''假退料''and RdRecord.cVouchType = 11))' set @chrsql=@chrsql+')' set @chrsql=@chrsql+') AND dDate < ' if ltrim(rtrim(@chrrqs))<>'' set @chrsql=@chrsql+@chrrqs else set @chrsql=@chrsql+@chrrq if ltrim(rtrim(@chrwhere))<>'' set @chrsql=@chrsql+' and '+@chrwhere set @chrsql=@chrsql+' ) b on (a.[仓库编码]=b.cWhCode and a.[存货编码]=b.cInvCode and isnull(a.[材料厂商编码],'''')=isnull(b.cDefine33,''''))' exec(@chrsql) --select [autoid],[cWhCode] as 仓库编码,[cWhName] as 仓库名称,[cInvCode],[cInvName],[cInvAddCode],[cInvStd],[cAddress],[cInvCCode], [cInvCName],[cComUnitCode],[cFree1],[cFree2],[cFree3],[cFree4],[cFree5],[cFree6],[cFree7],[cFree8],[cFree9],[cFree10], [cRdCode],[iNum],[iQuantity],[iQCJCSL],[iQCJCJS],[cItem_class],[cItemCName],[cItemCode],[cName],[bRdFlag],[cDefine33],[cVenName] from hzuf_tbl_VMI_RdRecord set quoted_identifier off GO |
|
|
