ReportingServies报表开发实例教程

文章目录

ReportingServies报表开发实例教程

ReportingServies——SQLServer报表开发综合实例

效果图如下：

1、我们先完成sql语句的编写，以下是各表和关键字段：

--t:hx_t_waterusedamount--用水水量 hx_t_waterusedamountid

--f:hx_frecorder(抄表员),hx_fzone(区段),hx_fmeterid(水表),hx_fpayamount(结算水量),hx_fdepartmentid(收费部门)

 

--t:hx_t_receivable--应收水费

--f:hx_fcollchargesreceivable4(污水处理费),hx_freceivablefee(应收水费),hx_fusedamountid(水量信息)

--hx_fstate(水量状态计划：100000000),

 

--hx_frecordtype(抄表类型):正常,000,000  估水,000,001 未抄见,000,002

--hx_festimateamountreason(估水原因):不用水,000,000 无表,000,001 防冻,000,002 表停,000,003

--hx_fnotrecordreason（未抄见原因）:未见表,000,000 有水,000,001 表埋,000,002 锁门,000,003 表不清,000,004

--hx_freading(本次抄表止度)

以下是SQL正文：

declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate()

--count(w.hx_t_waterusedamountid) as '应抄表数',
declare @hx_ClosingAccountInfoId uniqueidentifier
set @hx_ClosingAccountInfoId='08AFEF9F-E174-46F2-855A-32F26BE38F3B'
declare @BusinessunitId uniqueidentifier
set @BusinessunitId='6FE6FE55-5945-E411-9402-6CAE8B22702D'

-----------------SQL正文----------------------------------------------------------执行时间：
declare @FMonth int
declare @FYear int
select @FYear=t.hx_FYear,@FMonth=t.hx_FMonth from hx_ClosingAccountInfo t where hx_ClosingAccountInfoId=@hx_ClosingAccountInfoId
--select @FYear,@FMonth

if @BusinessunitId is null
begin
select s.fullname fullname,
w.hx_fzone hx_fzone,
1 as AllCounts,
case when w.hx_frecordtype='100000000' then 1 else 0 end Normal,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000000' then 1 else 0 end NotUseWater,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000001' then 1 else 0 end NoMeterWater,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000002' then 1 else 0 end antifreeze,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000003' then 1 else 0 end MeterStop,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason not in('100000000','100000001','100000002','100000003') then 1 else 0 end OtherWater,

case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000000' then 1 else 0 end NotSeeMeterWater,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000001' then 1 else 0 end HaveWater,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000002' then 1 else 0 end MeterWaterBuried,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000003' then 1 else 0 end LockDoor,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000004' then 1 else 0 end MeterWaterFuzzy,

case when w.hx_freading is null then 1 else 0 end hx_freading,

w.hx_fpayamount hx_fpayamount,
r.hx_freceivablefee hx_freceivablefee,
r.hx_fcollchargesreceivable4 hx_fcollchargesreceivable4
from hx_t_waterusedamount w inner join hx_t_receivable r
on w.hx_t_waterusedamountid=r.hx_fusedamountid and w.hx_frecorder is not null 
and w.hx_fstate='100000006' and w.hx_FYear=@FYear and w.hx_FMonth=@FMonth --and
inner join systemuser s on w.hx_frecorder=s.systemuserid
end
else
begin

select s.fullname fullname,
w.hx_fzone hx_fzone,
1 as AllCounts,
case when w.hx_frecordtype='100000000' then 1 else 0 end Normal,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000000' then 1 else 0 end NotUseWater,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000001' then 1 else 0 end NoMeterWater,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000002' then 1 else 0 end antifreeze,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason='100000003' then 1 else 0 end MeterStop,
case when w.hx_frecordtype='100000001' and w.hx_festimateamountreason not in('100000000','100000001','100000002','100000003') then 1 else 0 end OtherWater,

case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000000' then 1 else 0 end NotSeeMeterWater,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000001' then 1 else 0 end HaveWater,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000002' then 1 else 0 end MeterWaterBuried,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000003' then 1 else 0 end LockDoor,
case when w.hx_frecordtype='100000002' and w.hx_fnotrecordreason='100000004' then 1 else 0 end MeterWaterFuzzy,

case when w.hx_freading is null then 1 else 0 end hx_freading,

w.hx_fpayamount hx_fpayamount,
r.hx_freceivablefee hx_freceivablefee,
r.hx_fcollchargesreceivable4 hx_fcollchargesreceivable4
from hx_t_waterusedamount w inner join hx_t_receivable r
on w.hx_t_waterusedamountid=r.hx_fusedamountid and w.hx_fdepartmentid=@BusinessunitId and w.hx_frecorder is not null 
and w.hx_fstate='100000006' and w.hx_FYear=@FYear and w.hx_FMonth=@FMonth --and
inner join systemuser s on w.hx_frecorder=s.systemuserid

end

---------------------------------------------------------------------------
--group by s.fullname, w.hx_fzone,w.hx_fpayamount,r.hx_freceivablefee,r.hx_fcollchargesreceivable4

select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '用时/毫秒'
--and w.hx_fdepartmentid=@BusinessunitId

2、使用报表开发工具

右键单击解决方案管理器中的报表，选择“新建报表”

由于我们要按照用户名fullname、区段（hxfzone)进行分组统计，我们可以这样设置

这样就可以实现如下效果：

我们可以看到查询条件

这个是从数据库中查询出来的，我们可以通过如下配置来实现

同样，关于查询条件 部门

接下来，要配置查询参数了

最后再配置查询的主数据集