交叉报表的设计，比如想按“经手人”为列显示各物品的领料情况，比如这样的报表
处理方法：
---针对消耗品领用 按员工分列
if exists (select * from dbo.sysobjects where id = object_id(N'［dbo］.［CorssTab］') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure ［dbo］.［CorssTab］
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure CorssTab
( @bdate datetime,
@edate datetime,
@type varchar(40)
)
as
declare @CreateTableSQL varchar(8000)
declare @hander varchar(60)
declare @FieldAreaSQL varchar(8000)
declare @beginDate char(10)
declare @endDate char(10)
declare @TypeCode varchar(30)
if exists (select * from Tempdb..sysobjects where id = object_id('Tempdb..##TmpRecive') )
drop table ##TmpRecive
set @beginDate = convert(char(10),@bdate,120)
set @endDate = convert(char(10),@edate,120)
select @TypeCode = rtrim(ltrim(类别编码)) from 物品类别 where 类别=@type
DECLARE CURSORMonth CURSOR FOR
select 姓名 from 员工信息 --领用人 from 物品领用与退还单据查询视图
open CURSORMonth FETCH NEXT FROM
CURSORMonth into @hander
WHILE @@FETCH_STATUS = 0
begin
set @FieldAreaSQL = isnull(@FieldAreaSQL,'') + ',convert(float,0.0) as ［'+@hander+'领数量］,convert(float,0.0) as ［'+@hander+'领金额］'
FETCH NEXT FROM CURSORMonth INTO @hander
END
CLOSE CURSORMonth
DEALLOCATE CURSORMonth
set @CreateTableSQL =isnull(@CreateTableSQL,'') +'select 物品编码,名称,计量单位,单价,sum(金额) as 合计金额,sum(数量) as 合计数量'+@FieldAreaSQL+
' into ##TmpRecive from 物品领用与退还单据查询视图 where 日期>='''+convert(char(10),@bdate,120)+''' and 日期<='''+convert(char(10),@edate,120)+
''' and left(类别编码,len('''+@TypeCode+'''))='''+@TypeCode+''' and 领用人 in (select 姓名 from 员工信息) group by 物品编码,名称,计量单位,单价'
print @CreateTableSQL
exec (@CreateTableSQL)
DECLARE CURSORMonth CURSOR FOR
select 姓名 from 员工信息 --distinct 领用人 from 物品领用与退还单据查询视图
open CURSORMonth FETCH NEXT FROM
CURSORMonth into @hander
WHILE @@FETCH_STATUS = 0
begin
exec('update ##TmpRecive set ［'+@hander+'领数量］=b.数量,［'+@hander+'领金额］=b.金额 from ##TmpRecive a'+
' ,(select sum(数量) as 数量,sum(金额) as 金额,物品编码 from 物品领用与退还单据查询视图 where 领用人='''+@hander+
''' and 日期>='''+@beginDate+''' and 日期<='''+@endDate+''' and left(类别编码,len('''+@TypeCode+'''))='''+@TypeCode+''' group by 物品编码)b where a.物品编码=b.物品编码 '
) /* and 日期>@bdate and 日期<@edate */
FETCH NEXT FROM CURSORMonth INTO @hander
END
CLOSE CURSORMonth
DEALLOCATE CURSORMonth
update ##TmpRecive set 合计数量=isnull(b.数量,0),合计金额=isnull(b.金额,0) from ##TmpRecive a left join (select sum(数量) as 数量,sum(金额) as 金额,物品编码 from
物品领用与退还单据查询视图 where 领用人 in (select 姓名 from 员工信息) and 日期>=@beginDate and 日期<=@endDate and left(类别编码,len(@TypeCode))=@TypeCode group by 物品编码 ) b on a.物品编码=b.物品编码
select * from ##TmpRecive
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
并且在自定义报表中写入：CrossTab :开始日期,:结束日期,:类别，如下图：
