U8 自定义报表存储过程的调用方法
| U8 自定义报表存储过程的调用方法 使用储过程作为报表数据源 调用存储过程语法 |
新浪博客
存储过程设计规范
被调用的存储过程有且必须只有一个字符串参数，该参数接收条件字符串（如果报表没有定义条件项则该参数为空字符串），该参数应留出足够的长度以接收条件字符串；存储过程最终必须生成临时表，临时表名是在调用时指定的哪个。
使用存储过程举例
1. 在数据库创建存储过程
例28:
CREATE PROCEDURE Sp_Test
(
@chrWhere varchar(1000)=''
AS
Declare @chrsql varchar(2000)
--删除已经存在的临时表
If exists(select name from tempdb..sysobjects where name ='sa_tmpupdate')
--生成临时表
set @chrsql ='select * into tempdb..sa_tmpupdate from inventory '
exec(@chrsql )
select * from tempdb..sa_tmpupdate
GO
3．保存报表，设置格式和条件。
如下图，在报表查询时
传递字符串实际执行命令：
exec sp_test 'cinvcode>=''010101'' and
目前存储过程只接受一个参数，可通过此参数传递过滤条件。
---
用友编写自定义报表的三种方法
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 iQuant