，歡迎訪問網頁設計愛好者web開發。
crystal reports 和sql-server共同進行報表的開發
1：crystal reports功能自述
crystal reports 用于處理數據庫，幫助用戶分析和解釋重要信息。使用 crystal reports 可以方便地創建簡單報表，同時它也提供了創建復雜或專用的報表所需的整套工具。
創建所能想象的任何報表
crystal reports 幾乎可以從任何數據源生成您需要的報表。內置報表專家在您生成報表和完成一般的報表任務過程中，會一步一步地指導您進行操作。報表專家通過公式、交叉表、子報表和設置條件格式幫助表現數據的實際意義，揭示可能被隱藏掉的重要關系。如果文字和數字確實不夠充分，則用地理地圖和圖形進行形象的信息交流。
將報表擴展到 web
crystal reports 的靈活性并未停留在創建報表這一功能上 ?您可以用各種各樣的格式發布報表，包括用 microsoft 的 word 和excel 發布、通過電子郵件甚至 web 發布。高級的 web 報表功能允許工作組中的其他成員在他們自己的 web 瀏覽器中查看或更新共享報表。
將報表并入應用程序
通過將 crystal reports 的報表處理功能整合到自己的數據庫應用程序中，應用程序和 web 開發人員可以節省開發時間并滿足用戶的需求。crystal reports 支持大多數流行的開發語言，可以方便地在任何應用程序中添加報表。
不論您是 it 行業的站點管理員，還是營銷推廣經理，也無論您是金融業的數據庫管理員還是 ceo，crystal reports 都堪稱是一個功能強大的工具，它可以幫助每一個人分析、解釋重要信息。
2：crystal reports和sql-server結合
crystal雖然提供了強大的報表功能，但是對于復雜的邏輯運算，卻是很難實現。但是，crystal中可以像添加表一樣添加存儲過程，這就給我們的復雜運算提供了簡便的處理方法。
3：例子
這是我們給國航公司it服務項目做的報表的sql-server存儲過程部分。（歡迎大家共同討論）
a：每個員工的處理故障完成數、總數
fgw_proc1.txt
--fgw_proc1 處理故障完成數、總數
create procedure [ahd].[fgw_proc1](@開始時間 datetime , @結束時間 datetime)
as
declare @begin int , @end int /*轉時間*/
exec fgw_util1 @開始時間, @begin output
exec fgw_util1 @結束時間, @end output
declare @userid int, @handled float, @total float
create table #temp_proc1
(
userid int,
handled float,
total float
)
declare cur_ctct cursor for select id from ahd.ahd.ctct --取所有的用戶id
open cur_ctct
fetch cur_ctct into @userid
while @@fetch_status = 0
begin
--get @handle through exec fgw_proc2
exec fgw_proc1_1 @userid , @begin , @end , @handled output , @total output /*call下個存儲過程，得到某個用戶的解決數、接觸故障數*/
insert into #temp_proc1 values (@userid , @handled , @total) /*將用戶信息插入臨時表*/
fetch next from cur_ctct into @userid /*記錄下移*/
end
close cur_ctct
deallocate cur_ctct
select * from #temp_proc1 /*生成結束集*/
drop table #temp_proc1 /*釋放*/
go
fgw_proc1_1.txt
--fgw_proc1_1
create procedure [ahd].[fgw_proc1_1](@userid int , @begin int , @end int , @handled float output , @total float output)
as
set @handled = 0
set @total = 0
declare @cr_id int, @zh_id int, @status char(12), @to_status char(12), @cnt int, @open_date int
--handled /*計算此人的處理完成故障數*/
declare cur11_1 cursor for select ahd.call_req.id as cr_id, ahd.ztr_his.id as zh_id, ahd.call_req.status, ahd.ztr_his.to_status, ahd.ztr_his.to_cnt as cnt, ahd.call_req.open_date from ahd.call_req left outer join ahd.ztr_his on ahd.call_req.persid = ahd.ztr_his.call_req_id where ahd.call_req.type='i' and (ahd.call_req.status in ('cl', 'ttpc')) and (ahd.ztr_his.to_status in ('l1wip', 'l2wip', 'icp', 'srbyl1', 'srbyl2', 'nccbyl1', 'nccbyl2', 'crbyl1', 'crbyl2')) and ahd.call_req.open_date>@begin and ahd.call_req.open_date<@end and ahd.ztr_his.to_cnt = @userid
open cur11_1
fetch cur11_1 into @cr_id, @zh_id, @status, @to_status, @cnt, @open_date /*事件id,歷史id,狀態，處理人，打開時間取所需要的值*/
while @@fetch_status = 0 /*循環每一個記錄*/
begin
declare @count2 int /*每個事件單在歷史記錄中有多少條*/
declare cur11_2 cursor for select count(*) from ahd.call_req left outer join ahd.ztr_his on ahd.call_req.persid = ahd.ztr_his.call_req_id where ahd.call_req.type='i' and (ahd.call_req.status in ('cl', 'ttpc')) and (ahd.ztr_his.to_status in ('l1wip', 'l2wip', 'icp', 'srbyl1', 'srbyl2', 'nccbyl1', 'nccbyl2', 'crbyl1', 'crbyl2')) and (ahd.call_req.open_date>@begin) and (ahd.call_req.open_date<@end) and (ahd.call_req.id = @cr_id)
open cur11_2
fetch cur11_2 into @count2
close cur11_2
deallocate cur11_2
if @count2 <> 0
set @handled = @handled + 1.0 / @count2 /*此人的處理完成數*/
fetch next from cur11_1 into @cr_id, @zh_id, @status, @to_status, @cnt, @open_date /*循環記錄*/
end
close cur11_1
deallocate cur11_1
--total /*計算此人的處理故障數*/
declare cur11_3 cursor for select count(distinct(ahd.call_req.id)) from ahd.call_req left outer join ahd.ztr_his on ahd.call_req.persid = ahd.ztr_his.call_req_id where ahd.call_req.type='i' and (ahd.call_req.open_date>@begin and ahd.call_req.open_date<@end) and (ahd.ztr_his.to_cnt = @userid) /*取此人所有單*/
open cur11_3
fetch cur11_3 into @total /*總故障數*/
close cur11_3
deallocate cur11_3
--select @handled
--declare @handled float,@total float
--exec fgw_proc1_1 400115,1,1111111111,@handled output ,@total output
--print @handled
--print @total
go
b：每個員工的響應達標數、響應總數
fgw_proc2.txt
--fgw_proc2 響應達標數、響應總數
create procedure [ahd].[fgw_proc2](@開始時間 datetime , @結束時間 datetime)
as
declare @begin int , @end int
exec fgw_util1 @開始時間, @begin output
exec fgw_util1 @結束時間, @end output
declare @cr_id int, @zh_id int, @cnt int, @sym char(30), @time_stamp int, @isok int , @userid int , @handled int , @total int
declare @call_req_id char(30)
create table #temp_proc2 /* 響應達標數、響應總數*/
(
userid int,
handled2 int,
total2 int
)
create table #temp_proc2_1 /* 事件單為op的記錄*/
(
cr_id int,
zh_id int,
cnt int,
isok int
)
--initialize #temp_proc2_1 /*已經op的單，是否響應達標，返回處理人*/
declare cur2_1 cursor for select zh.call_req_id,zh.id,zh.to_cnt,sd.sym,zh.time_stamp from ahd.ahd.call_req as cr left outer join ahd.ahd.ztr_his as zh on cr.persid=zh.call_req_id left outer join ahd.ahd.srv_desc as sd on cr.support_lev=sd.code where cr.type='i' and cr.open_date>@begin and cr.open_date<@end and (zh.to_status='astol1' or zh.to_status='astol2')
open cur2_1
fetch cur2_1 into @call_req_id, @zh_id, @cnt, @sym, @time_stamp /*事件單id,歷史單id,人員,服務級別,op狀態的時間*/
while @@fetch_status = 0
begin
exec fgw_proc2_1 @call_req_id , @sym , @time_stamp , @isok output
insert into #temp_proc2_1 values (@cr_id , @zh_id , @cnt , @isok)
fetch next from cur2_1 into @call_req_id, @zh_id, @cnt, @sym, @time_stamp
end
close cur2_1
deallocate cur2_1
--initialize #temp_proc2
declare cur2_2 cursor for select id from ahd.ahd.ctct
open cur2_2
fetch cur2_2 into @userid
while @@fetch_status = 0
begin
--get @total /*所有的已響應的單*/
declare cur2_3 cursor for select count(*) from #temp_proc2_1 where cnt = @userid
open cur2_3
fetch cur2_3 into @total
close cur2_3
deallocate cur2_3
--get @handled /*所有的已響應的單,并達標的單*/
declare cur2_4 cursor for select count(*) from #temp_proc2_1 where cnt = @userid and isok=1
open cur2_4
fetch cur2_4 into @handled
close cur2_4
deallocate cur2_4
insert into #temp_proc2 values (@userid , @handled , @total)
fetch next from cur2_2 into @userid
end
close cur2_2
deallocate cur2_2
drop table #temp_proc2_1
select * from #temp_proc2
drop table #temp_proc2
go
fgw_proc2_1.txt
--fgw_proc2_1
create procedure [ahd].[fgw_proc2_1](@call_req_id char(30) , @level char(30) , @time_stamp int , @isok int output)
as
set nocount on
set @isok = 0
declare cur_zh cursor for select time_stamp from ahd.ahd.ztr_his where call_req_id = @call_req_id and to_status in ('l1wip','l2wip') and time_stamp>@time_stamp
open cur_zh
declare @time_stamp1 int
set @time_stamp1=0
fetch cur_zh into @time_stamp1
if (@time_stamp1 is not null) and (@time_stamp1<>0)
begin
if charindex('一級', @level) is not null and charindex('一級', @level)<>0
begin
if @time_stamp1 - @time_stamp <600
set @isok=1
end
else if charindex('二級', @level) is not null and charindex('二級', @level)<>0
begin
if @time_stamp1 - @time_stamp <1800
set @isok=1
end
else if charindex('三級', @level) is not null and charindex('三級', @level)<>0
begin
if @time_stamp1 - @time_stamp <1800
set @isok=1
end
else if charindex('四級', @level) is not null and charindex('四級', @level)<>0
begin
if @time_stamp1 - @time_stamp <1800
set @isok=1
end
end
close cur_zh
deallocate cur_zh
--select @isok, @time_stamp1
go
c：每個員工的處理時限達標數，總數
fgw_proc3.txt
--fgw_proc3
create procedure fgw_proc3(@開始時間 datetime , @結束時間 datetime)
as
/*時間轉換*/
declare @begin int , @end int
exec fgw_util1 @開始時間, @begin output
exec fgw_util1 @結束時間, @end output
declare @cr_id int, @zh_id int, @cnt int, @sym char(30), @time_stamp int, @isok int , @userid int , @handled int , @total int
create table #temp_proc3
(
userid int,
handled2 int,
total2 int
)
declare cur3_2 cursor for select id from ahd.ahd.ctct
open cur3_2
fetch cur3_2 into @userid
while @@fetch_status = 0
begin
--get @handled
declare cur3_4 cursor for select distinct(cr.id) from ahd.ahd.call_req as cr left outer join ahd.ahd.ztr_his as zh on cr.persid=zh.call_req_id where cr.type='i' and cr.open_date>@begin and cr.open_date<@end and zh.to_cnt = @userid and cr.sla_violation=0
open cur3_4
set @handled = @@cursor_rows
close cur3_4
deallocate cur3_4
--get @total
declare cur3_5 cursor for select distinct(cr.id) from ahd.ahd.call_req as cr left outer join ahd.ahd.ztr_his as zh on cr.persid=zh.call_req_id where cr.type='i' and cr.open_date>@begin and cr.open_date<@end and zh.to_cnt = @userid
open cur3_5
set @total = @@cursor_rows
close cur3_5
deallocate cur3_5
insert into #temp_proc3 values (@userid , @handled , @total)
fetch next from cur3_2 into @userid
end
close cur3_2
deallocate cur3_2
select * from #temp_proc3
drop table #temp_proc3
d：將日期格式轉換成整型
fgw_util1.txt
--fgw_util1
create procedure [ahd].[fgw_util1] (@time datetime, @seconds int output)
as
set @seconds=datediff(ss,'1970-01-01 00:00:00', @time)
go
例子只是整盤拷貝了代碼，歡迎大家共同討論
