金蝶kis云+sqlserver报表分析

封装存储过程

sql代码走一波

go
drop procedure test1
go
create procedure test1
 @fromEmpNum varchar(64),
 @toEmpNum varchar(64)
  as
 begin
    SELECT I.FNumber,I.FName,D.*,isnull(D.入库,0)-isnull(D.出库,0) as [余额] FROM 
	(select E.FItemID,
	--(case when B.FTranType = 1 then 1 end) as [入库],
	--(case when B.FTranType =21 then B.FTranType end) as [出库],
	--ITE.FName as[物料Name],
	--E.FAmount as [金额],
	sum((case when B.FTranType = 1 then E.FQty end)) as[入库],
	sum((case when B.FTranType =21 then E.FQty end)) as[出库]
	--isnull(sum((case when B.FTranType = 1 then E.FQty end)),0)-isnull(sum((case when B.FTranType =21 then E.FQty end)),0) as 余额
	--E.FQty as [入库或者出库数量]
	from ICStockBill as B 
	inner join ICStockBillEntry as E
	on B.FInterID = E.FInterID
	--inner join t_Item as ITE
	--on ITE.FItemID = E.FItemID
	inner join ICTransType as ICT
	on ICT.FID = B.FTranType
	Group by E.FItemID ) AS D
	INNER JOIN t_Item AS I
		ON D.FItemID=I.FItemID
		where (FNumber>=@fromEmpNum or @fromEmpNum='') and(FNumber<=@toEmpNum or @toEmpNum='') 
 end 
 --执行名为 GetStuCou 的有返回值的存储过程
 execute test1 @fromEmpNum = '10001' , @toEmpNum = '10004'

金蝶kis云执行示例