Crystal Reports and Sql-server Report development--Stored procedure-Practice

server| Stored Procedures
Crystal Reports and Sql-server together for the development of reports

1:crystal Reports feature Readme
Crystal Reports is used to process databases to help users analyze and interpret important information. With Crystal Reports You can easily create simple reports, and it also provides the complete set of tools you need to create complex or dedicated reports.

Create any report you can imagine
Crystal Reports can generate almost any report you need from any data source. A built-in report specialist guides you step-by-step through the process of generating a report and completing a general report task. Report experts help to show the real meaning of data through formulas, crosstab, subreport, and conditional formatting, revealing important relationships that can be hidden away. If the words and figures do not fully enough, then use geographical maps and graphics for the image of the exchange of information.

Extend a report to the Web
Crystal Reports's flexibility is not a function of creating reports? You can publish reports in a variety of formats, including publishing with Microsoft Word and Excel, e-mail, and even Web publishing. Advanced Web Reporting functionality allows other members of the workgroup to view or update shared reports in their own Web browsers.

Consolidating reports into applications
By consolidating the report processing capabilities of Crystal Reports into your own database applications, application and WEB developers can save development time and meet the needs of users. Crystal Reports supports most popular development languages and makes it easy to add reports to any application.

Whether you're an IT industry site administrator or Marketing Manager, whether you're a financial database administrator or a ceo,crystal Reports, it's a powerful tool that can help everyone analyze and interpret important information.

2:crystal reports and Sql-server combined
Crystal provides a powerful reporting function, but it is difficult to implement for complex logical operations. However, crystal can add stored procedures just like adding tables, which provides a simple way to handle complex operations.

3: Example
This is part of the sql-server stored procedure of the report we made to the IT Service project of Air China Company. (Welcome to discuss it together)
A: Number of processing failures per employee, total
Fgw_proc1.txt

Total number of--FGW_PROC1 processing failures
CREATE PROCEDURE [AHD]. [Fgw_proc1] (@ Start date datetime, @ end time datetime)
As
DECLARE @begin int, @end INT/* Turn time * *
EXEC fgw_util1 @ start time, @begin output
EXEC fgw_util1 @ end time, @end output


DECLARE @userid int, @handled float, @total float

CREATE TABLE #temp_proc1
(
UserID int,
Handled float,
Total float
)

DECLARE CUR_CTCT CURSOR for the SELECT ID from AHD. AHD.CTCT--Take all the user IDs
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 the next stored procedure, get a user's number of solutions, contact failure Number * *
INSERT INTO #temp_proc1 VALUES (@userid, @handled, @total)/* Inserts user information into the temporary table * *
FETCH next from CUR_CTCT into @userid/* Record down/*
End
Close CUR_CTCT
Deallocate CUR_CTCT
SELECT * FROM #temp_proc1/* Generate End Set * *
DROP TABLE #temp_proc1/* Release * *
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 (a), @to_status char (a), @cnt int, @open_date int
--handled/* Calculates the number of processing complete failures for this person * *
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_s Tatus, 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.per Sid = 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/* Event ID, History ID, State, handler, open time to take the required value * *
While @ @FETCH_STATUS = 0/* Cycle per record * *
BEGIN
DECLARE @count2 INT/* * How many records in the history of each event list * *
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 (' L 1WIP ', ' 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/* The number of processing completed by this person * *
FETCH NEXT from Cur11_1 into @cr_id, @zh_id, @status, @to_status, @cnt, @open_date/* Circular record * *
End
Close Cur11_1
Deallocate cur11_1

--total//* Calculate the number of handling failures for this person * *
DECLARE cur11_3 CURSOR for SELECT count (Distinct (AHD.call_req.id)) from Ahd.call_req left OUTER JOIN ahd.ztr_his on ahd.ca Ll_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)/* Take all the single person/

OPEN cur11_3
FETCH cur11_3 into @total/* Total number of failures * *
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: Number of responses per employee, total number of responses
Fgw_proc2.txt
--FGW_PROC2 Response Compliance Number, total number of responses
CREATE PROCEDURE [AHD]. [FGW_PROC2] (@ Start date datetime, @ end time datetime)
As
DECLARE @begin int, @end int
EXEC fgw_util1 @ start time, @begin output
EXEC fgw_util1 @ end time, @end output

DECLARE @cr_id int, @zh_id int, @cnt int, @sym char (m), @time_stamp int, @isOK int, @userid int, @handled int, @total Int
DECLARE @call_req_id Char (30)

CREATE TABLE #temp_proc2/* Response number per target, total number of responses * *
(
UserID int,
Handled2 int,
Total2 int
)

CREATE TABLE #temp_proc2_1/* Event list for OP record * *
(
cr_id int,
zh_id int,
CNT int,
IsOK int
)

--initialize #temp_proc2_1/* Already OP of the single, whether to respond to compliance, return processing person * *
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 en on the 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 (z h.to_status= ' ASTOL1 ' OR zh.to_status= ' ASTOL2 ')
OPEN Cur2_1
FETCH cur2_1 into @call_req_id, @zh_id, @cnt, @sym, @time_stamp/* Event single ID, history single ID, personnel, service level, OP state time * *
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 the SELECT ID from AHD. Ahd.ctct
OPEN cur2_2
FETCH cur2_2 into @userid
While @ @FETCH_STATUS = 0
BEGIN
--get @total/* All responses to the single */
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/* All responses to the single, and the standard of single * * *
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 (), @level char (), @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 ', @level) is not NULL and CHARINDEX (' level ', @level) <>0
BEGIN
If @time_stamp1-@time_stamp <600
SET @isOK =1
End
ELSE IF CHARINDEX (' Level two ', @level) is not NULL and CHARINDEX (' Level two ', @level) <>0
BEGIN
If @time_stamp1-@time_stamp <1800
SET @isOK =1
End
ELSE IF CHARINDEX (' Level three ', @level) is not NULL and CHARINDEX (' Level three ', @level) <>0
BEGIN
If @time_stamp1-@time_stamp <1800
SET @isOK =1
End
ELSE IF CHARINDEX (' Level four ', @level) is not NULL and CHARINDEX (' Level four ', @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: The processing time of each employee compliance number, total
Fgw_proc3.txt
--fgw_proc3
CREATE PROCEDURE fgw_proc3 (@ start time datetime, @ end time datetime)
As
/* Time Conversion */
DECLARE @begin int, @end int
EXEC fgw_util1 @ start time, @begin output
EXEC fgw_util1 @ end time, @end output

DECLARE @cr_id int, @zh_id int, @cnt int, @sym char (m), @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 the 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 en on cr.persid=zh.call_req_id WHERE cr.type= ' I ' and cr.open_date> @begin and cr.open_date< @end and EN . 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 en on cr.persid=zh.call_req_id WHERE cr.type= ' I ' and cr.open_date> @begin and cr.open_date< @end and EN . 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: Convert date format to integral type
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

The example is only the whole disk copy code, welcome to discuss together