这个问题说难也难,说容易也容易,关键是选好报表工具。给你个你的数据的演示例子。
http://218.28.143.234/report/default.aspx?reportName=%u6536%u53d1%u660e%u7ec6.cr
更多演示在:
http://218.28.143.234/demo欢迎加入中国式报表群:60531400
http://218.28.143.234/report/default.aspx?reportName=%u6536%u53d1%u660e%u7ec6.cr
更多演示在:
http://218.28.143.234/demo欢迎加入中国式报表群:60531400
日期 摘要 仓库 货品名称 单位 收入数量 发出数量 结存数量
2009-03-02 期初 原材料仓 A货品 个 18
2009-03-03 原材料仓 A货品 个 30 3 45
2009-03-07 原材料仓 A货品 个 50 5 90
declare @I_INSTORE table(BD smalldatetime,ST nvarchar(50),GN nvarchar(50),UN nvarchar(10),IQT int)
insert into @I_INSTORE values('2009-03-01','原材料仓','A货品','个',20)
insert into @I_INSTORE values('2009-03-03','原材料仓','A货品','个',30)
insert into @I_INSTORE values('2009-03-07','原材料仓','A货品','个',50)
insert into @I_INSTORE values('2009-03-06','废品仓','A货品','个',8)
insert into @I_INSTORE values('2009-03-01','半成品仓','B货品','个',60)
insert into @I_INSTORE values('2009-03-03','半成品仓','B货品','个',70)
insert into @I_INSTORE values('2009-03-07','半成品仓','B货品','个',90)
declare @I_OUTSTORE table(BD smalldatetime,ST nvarchar(50),GN nvarchar(50),UN nvarchar(10),OQT int)
insert into @I_OUTSTORE values('2009-03-01','原材料仓','A货品','个',2)
insert into @I_OUTSTORE values('2009-03-03','原材料仓','A货品','个',3)
insert into @I_OUTSTORE values('2009-03-07','原材料仓','A货品','个',5)
insert into @I_OUTSTORE values('2009-03-06','废品仓','A货品','个',2)
insert into @I_OUTSTORE values('2009-03-01','半成品仓','B货品','个',6)
insert into @I_OUTSTORE values('2009-03-03','半成品仓','B货品','个',7)
insert into @I_OUTSTORE values('2009-03-07','半成品仓','B货品','个',9)--预置起止日期
declare @sdate smalldatetime
declare @tdate smalldatetime
set @sdate = '2009-03-02'
set @tdate = '2009-03-07'--实现第一个报表
declare @t table(日期 smalldatetime,摘要 nvarchar(10),仓库 nvarchar(50),货品名称 nvarchar(50),单位 nvarchar(10),收入数量 int,发出数据 int,结存数量 int)
declare @st nvarchar(50),@gn nvarchar(50),@iNum int,@oNum int
DECLARE tmp_Cursor CURSOR FOR select distinct st,gn from @I_INSTORE union select distinct st,gn from @I_OUTSTORE
OPEN tmp_Cursor
FETCH NEXT FROM tmp_Cursor into @st,@gn
WHILE @@FETCH_STATUS = 0
BEGIN
--计算中间日期
insert into @t
select rs.BD,(Case When rs.BD=@sdate Then '期初' Else '' End),@st,@gn,'个',
IsNull((select sum(IQT) from @I_INSTORE where st=@st and gn=@gn and BD=rs.BD),0),
IsNull((select sum(OQT) from @I_OUTSTORE where st=@st and gn=@gn and BD=rs.BD),0),
IsNull((select sum(IQT) from @I_INSTORE where st=@st and gn=@gn and BD<=rs.BD),0)
-IsNull((select sum(OQT) from @I_OUTSTORE where st=@st and gn=@gn and BD<=rs.BD),0)
from
(select @sdate as BD
union
select BD from @I_INSTORE
where st=@st and gn=@gn and BD>=@sdate and BD<=@tdate
group by BD
union
select BD from @I_OUTSTORE
where st=@st and gn=@gn and BD>=@sdate and BD<=@tdate
group by BD
union
select @tdate as BD
) rs FETCH NEXT FROM tmp_Cursor into @st,@gn
END
CLOSE tmp_Cursor
DEALLOCATE tmp_Cursorselect CONVERT(varchar(100), 日期, 23),摘要,仓库,货品名称,单位,cast(收入数量 as nvarchar),发出数据,结存数量 as 日期 from @t
declare @I_INSTORE table(BD smalldatetime,ST nvarchar(50),GN nvarchar(50),UN nvarchar(10),IQT int)
insert into @I_INSTORE values('2009-03-01','原材料仓','A货品','个',20)
insert into @I_INSTORE values('2009-03-03','原材料仓','A货品','个',30)
insert into @I_INSTORE values('2009-03-07','原材料仓','A货品','个',50)
insert into @I_INSTORE values('2009-03-06','废品仓','A货品','个',8)
insert into @I_INSTORE values('2009-03-01','半成品仓','B货品','个',60)
insert into @I_INSTORE values('2009-03-03','半成品仓','B货品','个',70)
insert into @I_INSTORE values('2009-03-07','半成品仓','B货品','个',90)
declare @I_OUTSTORE table(BD smalldatetime,ST nvarchar(50),GN nvarchar(50),UN nvarchar(10),OQT int)
insert into @I_OUTSTORE values('2009-03-01','原材料仓','A货品','个',2)
insert into @I_OUTSTORE values('2009-03-03','原材料仓','A货品','个',3)
insert into @I_OUTSTORE values('2009-03-07','原材料仓','A货品','个',5)
insert into @I_OUTSTORE values('2009-03-06','废品仓','A货品','个',2)
insert into @I_OUTSTORE values('2009-03-01','半成品仓','B货品','个',6)
insert into @I_OUTSTORE values('2009-03-03','半成品仓','B货品','个',7)
insert into @I_OUTSTORE values('2009-03-07','半成品仓','B货品','个',9)--预置起止日期
declare @sdate smalldatetime
declare @tdate smalldatetime
set @sdate = '2009-03-02'
set @tdate = '2009-03-07'--实现第一个报表
declare @t table(日期 smalldatetime,摘要 nvarchar(10),仓库 nvarchar(50),货品名称 nvarchar(50),单位 nvarchar(10),收入数量 int,发出数据 int,结存数量 int)
declare @st nvarchar(50),@gn nvarchar(50),@iNum int,@oNum int
DECLARE tmp_Cursor CURSOR FOR select distinct st,gn from @I_INSTORE union select distinct st,gn from @I_OUTSTORE
OPEN tmp_Cursor
FETCH NEXT FROM tmp_Cursor into @st,@gn
WHILE @@FETCH_STATUS = 0
BEGIN
--计算中间日期
insert into @t
select rs.BD,(Case When rs.BD=@sdate Then '期初' Else '' End),@st,@gn,'个',
IsNull((select sum(IQT) from @I_INSTORE where st=@st and gn=@gn and BD=rs.BD),0),
IsNull((select sum(OQT) from @I_OUTSTORE where st=@st and gn=@gn and BD=rs.BD),0),
IsNull((select sum(IQT) from @I_INSTORE where st=@st and gn=@gn and BD<=rs.BD),0)
-IsNull((select sum(OQT) from @I_OUTSTORE where st=@st and gn=@gn and BD<=rs.BD),0)
from
(select @sdate as BD
union
select BD from @I_INSTORE
where st=@st and gn=@gn and BD>=@sdate and BD<=@tdate
group by BD
union
select BD from @I_OUTSTORE
where st=@st and gn=@gn and BD>=@sdate and BD<=@tdate
group by BD
union
select @tdate as BD
) rs FETCH NEXT FROM tmp_Cursor into @st,@gn
END
CLOSE tmp_Cursor
DEALLOCATE tmp_Cursorselect CONVERT(varchar(100), 日期, 23),摘要,仓库,货品名称,单位,cast(收入数量 as nvarchar),发出数据,结存数量 as 日期 from @t
FROM (select 0 OrderID,
'' BD,
'期初' DIGEST,
ST,
GN,
UN,
0 IQT,
0 OQT,
sum(IsNull(IQT, 0) - IsNull(OQT, 0)) QTY
from (select distinct ST, GN, UN
FROM (SELECT ST, GN, UN
FROM I_INSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07'
UNION ALL
SELECT ST, GN, UN
FROM I_OUTSTORE
WHERE BD >= '2009-03-02')) M
LEFT JOIN (select BD, ST, GN, UN, IQT, 0 OQT
from I_INSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07'
UNION ALL
select BD, ST, GN, UN, 0 IQT, OQT
from I_OUTSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07') A ON M.ST = A.ST
AND M.GN = A.GN
AND M.UN = A.UN
UNION ALL
select 1 ORDERID,
BD,
' ' DIGEST,
ST,
GN,
UN,
IQT,
OQT,
IsNull (select sum(IsNull(IQT, 0) - IsNull(OQT, 0))
from ((select BD, ST, GN, UN, IQT, 0 OQT
from I_INSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07'
UNION ALL
select BD, ST, GN, UN, 0 IQT, OQT
from I_OUTSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07')) B
where B.ST = C.ST
AND B.GN = C.GN
AND B.UN = C.UN
AND B.BD <= C.BD)
FROM (SELECT M.BD,
M.ST,
M.GN,
M.UN,
ISNULL(I.IQT, 0) IQT,
ISNULL(O.OQT, 0) OQT
FROM (SELECT DISTINCT BD, ST, GN, UN
FROM (SELECT BD, ST, GN, UN
FROM I_INSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07'
UNION ALL
SELECT BD, ST, GN, UN
FROM I_OUTSTORE
WHERE BD >= '2009-03-02'
AND BD <= '2009-03-07') A) M
LEFT JOIN I_INSTORE I ON M.ST = I.ST
AND M.GN = I.GN
AND M.UN = I.UN
AND M.BD = I.BD
LEFT JOIN I_OUTSTORE O ON M.ST = O.ST
AND M.GN = O.GN
AND M.UN = O.UN
AND M.BD = O.BD) C) F
ORDER BY ORDERID, BD