Crystal Reports 和sql-server共同進行報表的開發

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