U8 自定义报表存储过程的调用方法

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