语句如下，怎样才能让存储过程的数据源在报表里显示出来USE GO/****** Object: StoredProcedure . Script Date: 2021-11-29 16:34:54 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE .(@cInvCode nvarchar(100)=null,@cfree1 nvarchar(50)=null,@cInvCCode nvarchar(50)=null,@cidefine10 nvarchar(50)=null)asbegin --加索引--select sum(dls.fOutQuantity) as 总累计出库数量,dls.cinvcode as 存货编码a,isnull(dls.cfree1,'常规色') as 色号a,dls.cInvName as 存货名称1 into ##tmp1--from DispatchLists dls--left join SO_SODetails sds --on dls .cSoCode=sds.cSOCode--left join SO_SOMain sm--on sm.ID=sds.ID--where dls.cordercode is not null and sm.dclosedate is null --订单未关闭以及销售订单不为空--group by dls.cinvcode , dls.cfree1,dls.cInvName --发货单表----dls.fOutQuantity ,AutoID----dls.fOutQuantity as 累计出库数量,AutoID --select i.cinvname as 存货名称6,i.cInvCode as 存货编码b,case when (cs.cFree1='') then '常规色' else cs.cFree1 end as 色号b,i.cInvStd as 规格型号,sum(cs.iQuantity) as 总现存量 into ##tmp2--from CurrentStock cs--left join Inventory i --/存货档案现存量表--数据正确--on i.cInvCode=cs.cInvCode --where left(i.cInvCCode,2)=01 and cs.iQuantity>=0 --group by i.cInvCode ,cs.cFree1,i.cInvStd ,i.cinvname--select sds.cInvCode as 存货编码,i.cInvName as 存货名称5 ,isnull(sds.cfree1,'常规色') as 色号c, sum(sds.iQuantity) as 销售订单数量, sum(sds.iFHQuantity )as 累计发货数量,sum(sds.iQuantity) - sum(sds.iFHQuantity ) as 销售订单未开单数量 into ##tmp3--from SO_SOMain sm--left join SO_SODetails sds--on sm.id=sds.ID --销售订单主表/子表 数据正确--left join Inventory i--on sds.cInvCode =i.cInvCode--where sm.dclosedate is null --group by sds.cInvCode,sds.cfree1 ,i.cInvName--select ie.cInvCode,ie.cidefine10 into ##tmp4--from Inventory_extradefine ie --存货档案扩展自定义--select * ,##tmp3.销售订单数量-##tmp1.总累计出库数量 as 销售订单未发货单数量,##tmp2.总现存量-销售订单未开单数量 as 盈亏数,##tmp4.cidefine10 as 是否定制--from ##tmp3--left join ##tmp1 --on ##tmp3.存货编码=##tmp1.存货编码a and ##tmp3.色号c=##tmp1.色号a--left join ##tmp2--on ##tmp2.存货编码b=##tmp1.存货编码a and ##tmp2.色号b=##tmp1.色号a--left join ##tmp4--on ##tmp3.存货编码= ##tmp4.cInvCode--where --(##tmp3.存货编码=@cInvCode or @cInvCode is null)--and (##tmp3.色号c=@cfree1 or @cfree1 is null)--drop table ##tmp1,##tmp2,##tmp3,##tmp4 select inc.cInvCCode,cInvCName,ta.cInvCode 存货编码,inv.cInvName 存货名称5,inv.cInvStd 规格型号,ta.cFree1 色号c,ta.iQuantity 销售订单数量,ta.iFHQuantity, ISNULL(ta.iFHQuantity,0) 累计发货数量,ta.fQuantity 销售订单未开单数量,ISNULL(ct.iQuantity,0) 总现存量,(ISNULL(ct.iQuantity,0)-ta.fQuantity) 盈亏数,cidefine10 from ( select sd.cInvCode,sd.cFree1,sum(sd.iQuantity) iQuantity,sum(iFHQuantity) iFHQuantity, sum(rd.iQuantity) fOutQuantity,sum(sd.iQuantity-ISNULL(rd.iQuantity,0)) fQuantity2, sum(CASE WHEN ISNULL(sd.iFHQuantity,0)>sd.iQuantity then 0 else sd.iQuantity-ISNULL(sd.iFHQuantity,0) end) fQuantity from so_somain sm with(nolock) inner join so_sodetails sd with(nolock) on sm.ID=sd.ID left join (select iSOsID,sum(iQuantity) iQuantity from DispatchLists group by iSOsID) rd on sd.iSOsID=rd.iSOsID --left join (SELECT td.iSOsID,sum(rds.iQuantity) iQuantity -- FROM rdrecords32 rds -- inner join rdrecord32 rd on rds.ID=rd.ID -- left join DispatchLists td on td.iDLsID=rds.iDLsID -- where ISNULL(rd.cHandler,'')!='' -- group by td.iSOsID)rd on sd.iSOsID=rd.iSOsID where ISNULL(sm.cVerifier,'')!='' and ISNULL(sm.cCloser,'')='' and ISNULL(sd.cSCloser,'')='' group by sd.cInvCode,sd.cFree1--销售订单主表子表 )ta left join ( select cInvCode,cFree1, sum(iQuantity+fInQuantity-fOutQuantity+fTransInQuantity-fTransOutQuantity) iQuantity from currentstock with(nolock) group by cInvCode,cFree1--现存量表 ) ct on ta.cInvCode=ct.cInvCode and ISNULL(ta.cFree1,'')=ISNULL(ct.cFree1,'') left join inventory inv with(nolock) on ta.cInvCode=inv.cInvCode left join Inventory_extradefine inf with(nolock) on ta.cInvCode=inf.cInvCode--是否定制 left join InventoryClass inc on left(inv.cInvCCode,2)=inc.cInvCCode--存货大类编码 where 1=1 -- and inc.cInvCCode='01' and (ta.cInvCode=@cInvCode or @cInvCode is null) and (ta.cFree1=@cfree1 or @cfree1 is null) and (inc.cInvCCode=@cInvCCode or @cInvCCode is null) and (inf.cidefine10=@cidefine10 or @cidefine10 is null) order by ta.cInvCodeend;
