Just in case this would be handy for others who haven't
tried this yet, remember how powerful it is to call a
stored procedure from within a report.
A client needed a message printed on the bottom of a report. I was
to count up how many invoices a customer had with a bunch of
different criteria, such as date of invoice, whether paid off, if
paid off within certain time frame, amount of invoice, etc. If no
invoices met the criteria, then no message on the report. I could
have (1) calculated this outside the report in a temp table, but this
report is called from many different programs so it would be nice to
have it within the report itself, or (2) I could define a whole bunch
of complicated report variables. Instead I used a stored procedure.
My report variables:
INTEGER: RF : rvNoInvoices = (CALL WDeliver(CustID))
*I am passing to the stored procedure the CustID, it will
be returning a variable with the number of invoices
TEXT : RF : rvFooter =
(IFGT(.rvNoInvoices,0,'CONGRATULATIONS! <etc....>', ' '))
Here's the text of the stored procedure:
*( WDeliver.sp, stored procedure run from report WinDelivery
Will be passed a CustID
Returns a count of invoices that meet criteria)
-- Initialize the passed in variable
SET VAR vCustID INT
SET VAR vCount INT = 0
DROP CURS wdeliver
DECLARE wdeliver CURSOR FOR SELECT servid, saledate FROM service +
WHERE custid = .vcustid AND BalDueS <= 0.50
OPEN wdeliver
WHILE 1 = 1 THEN
FETCH wdeliver INTO vServid, vDate
IF SQLCODE = 100 THEN
BREAK
ENDIF
< a bunch of tests in here to count invoices. If it meets a
criteria then:
SET VAR vcount = (.vcount+1)
>
ENDWHILE
DROP CURS wdeliver
CLEAR VAR vDate, vServid
RETURN .vCount
RETURN
Karen
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
