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