Sometimes a custom Crystal Report is needed which does not use the iMIS search dialog boxes. This is usually the case when there are nested queries and multiple joins. This solution was created to permit the generation of a Member Card export that inserts an Activity recording the generation. Because we did not use a search dialog, we could not use the option PRINT_CREATE_ACTIVITY. So... I based the report on a stored procedure. The key is that you must do the insert part of the stored proc before you do the select for the data. I tried it the other way around, and the inserts of the tracking activities did not complete. I do not have a certain explanation, but apparently Crystal closes the stored proc connection as soon as all the data is returned. So insert would start, but stop only about 10% through the update. Simply moving it to the top seems to have solved the problem. Here is a sample of the stored proc: USE [iMIS_DEV] GO /****** Object: StoredProcedure [dbo].[asiconsulting_MemberLabels_RenewPackets] Script Date: 02/01/2008 10:05:10 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[asiconsulting_MemberLabels_RenewPackets] @BeginDate datetime, @EndDate datetime AS /****** create cursor to select the records to update and insert into Activity table */ DECLARE MEMPACKET cursor for select n.ID from Name n Join Name_Address na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM Join Subscriptions s ON n.ID=s.ID and s.PRODUCT_CODE='Basic_DUES' Left Outer Join Name n2 ON n.ID = n2.CO_ID and n2.MEMBER_TYPE = 'JNTS' where n.Member_record = 1 and n.Member_type <> 'JNTS' and n.Status = 'A' and N.PAID_THRU >= GetDate() and na.Status = '' and s.Date_Added not between @BeginDate and @EndDate and s.PAYMENT_DATE between @BeginDate and @EndDate DECLARE @ID varchar(10) Open MEMPACKET fetch next from MEMPACKET into @ID While @@FETCH_STATUS<>-1 BEGIN Declare @Result int, @NewActSeqn int exec @Result=sp_iboGetCounter 'Activity',1,@NewActSeqn output insert into Activity (SEQN,ID,NOTE,ACTIVITY_TYPE,Other_code,SOURCE_SYSTEM,PRODUCT_CODE,TRANSACTION_DATE) values (@NewActSeqn,@ID,'','MEMPACKET','Renewal','MEMBER','',GetDate()) fetch next from MEMPACKET into @ID END close MEMPACKET deallocate MEMPACKET /****** Use exactly the same selection criteria as the cursor for the selected output in the Crystal report */ select n.ID,n.MEMBER_TYPE, n.PAID_THRU, n.Full_Name, n2.FULL_NAME as JNTS_NAME,n.COMPANY,na.Address_1,na.Address_2, na.Address_3,na.City, na.State_Province, n.Country, na.ZIP, n.Last_First, n.Join_Date, s.Bill_Thru, s.Date_Added, s2.PRODUCT_CODE as MAILMETHOD from Name n Join Name_Address na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM Join Subscriptions s ON n.ID=s.BT_ID and PRODUCT_CODE='iMIS_DUES' Left Outer Join Name n2 ON n.ID = n2.CO_ID and n2.MEMBER_TYPE = 'JNTS' Left Outer Join Subscriptions s2 ON n.ID = s2.ID and s2.PRODUCT_CODE in ('AIRMAIL','FIRSTCLASS') where n.Member_record = 1 and n.Member_type <> 'JNTS' and n.Status = 'A' and N.PAID_THRU >= GetDate() and na.Status = '' and s.Date_Added not between @BeginDate and @EndDate and s.PAYMENT_DATE between @BeginDate and @EndDate Use this stored proc as the datasource for your Crystal Report
Creating Crystal Reports that insert data - Example Member Card tracking in Activity
Still faster...
I can think of a couple more improvements:
* In SQL 2000 and above, a table @var is faster than a #temptable. That is, unless you are working with enough data to fill the SQL server's memory. (Unlikely)
* The insert does not need to come first. Either order should work just as well. Probably something else going on in your code.
* For consistency, it's best to build the temp table and perform both the insert and select from that table. Getting the criteria exactly the same can be tricky (semantics of an insert can cause different results than a select), but even if you do, a new record could come along mid-procedure and change your results. The table doesn't need to hold all the output data, just the keys needed to pull the correct records.
* Remember to give exec permissions on the procedure to the IMIS group, or to the user saved in the report. You sure wouldn't be running this report as "sa", right?
* The table approach is also better because you can hit the Counter table just once instead of once per row. (As shown here.)
* Keep in mind that calls to sp_asi_GetCounter, sp_asi_GetCounter2 and sp_iboGetCounter are not safe unless called from inside a transaction. I have an active SMR on this issue (rated "High"): if two connections use these procedures simultaneously, and one or both does not wrap the call in a transaction, it is possible for them to receive the same/overlapping Counter values.
-- Bruce
Adding activities without using a cursor
This will run faster if using a temporary table, eliminating the need for a cursor.
I have made the assumtion that there is only 1 name Id returned by your results. If this is not true, the temporary table would need one or more fields to ensure uniqueness.
alter PROCEDURE [dbo].[asiconsulting_MemberLabels_RenewPackets]
@BeginDate datetime,
@EndDate datetime
AS
/****** create cursor to select the records to update and insert into Activity table */
create table #tmpListID(ID varchar(10),
Seqn int identity,
act_seqn int not null default 0)
insert into #tmpListID (ID)
select n.ID from Name n
Join Name_Address na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM
Join Subscriptions s ON n.ID=s.ID and s.PRODUCT_CODE='Basic_DUES'
Left Outer Join Name n2 ON n.ID = n2.CO_ID and n2.MEMBER_TYPE = 'JNTS'
where n.Member_record = 1 and n.Member_type <> 'JNTS' and n.Status = 'A'
and N.PAID_THRU >= GetDate() and na.Status = '' and s.Date_Added not between @BeginDate and @EndDate
and s.PAYMENT_DATE between @BeginDate and @EndDate
Declare
@cntActivities int,
@NewActSeqn int
select @cntActivities = count(*) from #tmpListID
exec sp_iboGetCounter 'Activity',@cntActivities,@NewActSeqn output
update #tmpListID
set act_seqn = seqn + @NewActSeqn - 1
insert into Activity (SEQN,ID,NOTE,ACTIVITY_TYPE,Other_code,SOURCE_SYSTEM,PRODUCT_CODE,TRANSACTION_DATE)
select l.act_seqn, l.id, '','MEMPACKET','Renewal','MEMBER','',
convert(datetime,convert(varchar(12),GetDate(),112),112) -- remove time
from #tmpListID l
/****** Use exactly the same selection criteria as the cursor for the selected output in the Crystal report */
select n.ID,n.MEMBER_TYPE, n.PAID_THRU, n.Full_Name, n2.FULL_NAME as JNTS_NAME,n.COMPANY,na.Address_1,na.Address_2,
na.Address_3,na.City, na.State_Province, n.Country, na.ZIP, n.Last_First, n.Join_Date, s.Bill_Thru, s.Date_Added,
s2.PRODUCT_CODE as MAILMETHOD
from #tmpListID l join Name n on l.ID = n.ID
Join Name_Address na ON n.MAIL_ADDRESS_NUM = na.ADDRESS_NUM
Join Subscriptions s ON n.ID=s.BT_ID and PRODUCT_CODE='iMIS_DUES'
Left Outer Join Name n2 ON n.ID = n2.CO_ID and n2.MEMBER_TYPE = 'JNTS'
Left Outer Join Subscriptions s2 ON n.ID = s2.ID and s2.PRODUCT_CODE in ('AIRMAIL','FIRSTCLASS')
where n.Member_record = 1 and n.Member_type <> 'JNTS' and n.Status = 'A'
and N.PAID_THRU >= GetDate() and na.Status = '' and s.Date_Added not between @BeginDate and @EndDate
and s.PAYMENT_DATE between @BeginDate and @EndDate