Perplexing Problem with Crystal Reports and MSSQL UDF

[johnawoods]

I constructed a UDF to handle most of the processing and intended to use Crystal Reports to handle the presentation as I have done with a large number of flash reports in the past. However this time the UDF returned no output. The SQL and the Crystal Reports command SQL are listed below.

I have traced the Crystal Report in Query Profiler and Crystal Reports logs on to the server (access is via ODBC) reads the expected number of records then stops. The UDF produces the expected output when called using Query Analyser.

I have created the report in both version 9 and XI of Crystal Reports. XI gives me a message saying “Error Data Source: Verify for table Command failed. Possible report corruption.” when I check dependencies. But this is of little help, I have also created a number of versions of the report but to no avail.

Any help would be gratefully received.

UDF SQL

CREATE FUNCTION [dbo].[udf_SLOTS_03b] (@Cost_Centre_cd as char(4), @startdate as datetime, @enddate as datetime)

returns @SLOTS_03 TABLE
(
LBO char(4),
BusinessDate datetime,
Terminal char(10),
FOBTCashInBox money,
FOBTLoadedCredit money,
FOBTReceipts money,
FOBTClaimsPaid money,
FOBTCashWin money
)

begin

declare @TransferTable TABLE --DECLARE TRANSFER TABLE THIS WILL HOLD THE ROWS FOR THE SPECIFIED LBO AND DATE RANGE
(
cost_centre_cd char(4),
business_date datetime,
terminal_no varchar(30),
transfer_type_id int,
total_amount money
)

insert into @TransferTable --LOADS TRANSFER TABLE
select cost_centre_cd as lbo,
convert(varchar(11), when_transferred, 106),
additional_data,
transfer_type_id,
sum(amount)
from act_dt_shoptransfers
where when_transferred >= @StartDate and
when_transferred < @EndDate and
transfer_type_id in (55, 56, 57, 58) and
cost_centre_cd = @Cost_Centre_Cd
group by cost_centre_cd, convert(varchar(11), when_transferred, 106), additional_data, transfer_type_id

insert into @slots_03 -- INSERT INTO OUTPUT TABLE

-- WHEN THE TRANSFER TABLE HAS BEEN CREATED EXTRACT THE REQUIRED COLUMNS BY TRANSFER TYPE ID
select a.cost_centre_cd as LBO,
a.business_date as BusinessDate,
--terminal = substring(a.terminal_no,1,1),
rtrim(a.terminal_no) as Terminal,
(select sum(total_amount)
from @TransferTable b -- READ TRANSFER TABLE FOR TT_ID = 55 CASH IN BOX
where a.cost_centre_cd = b.cost_centre_cd and
a.business_date = b.business_date and
a.terminal_no = b.terminal_no and
b.transfer_type_id = 55) as FOBTCashInBox,
(select sum(total_amount)
from @TransferTable b -- READ TRANSFER TABLE FOR TT_ID = 58 LOADED CREDIT
where a.cost_centre_cd = b.cost_centre_cd and
a.business_date = b.business_date and
a.terminal_no = b.terminal_no and
b.transfer_type_id = 58) as FOBTLoadedCredit,
(select sum(total_amount)
from @TransferTable b -- READ TRANSFER TABLE FOR TT_ID = 56 RECEIPTS
where a.cost_centre_cd = b.cost_centre_cd and
a.business_date = b.business_date and
a.terminal_no = b.terminal_no and
b.transfer_type_id = 56) as FOBTReceipts,
(select sum(total_amount)
from @TransferTable b -- READ TRANSFER TABLE FOR TT_ID = 57 CLAIM PAID
where a.cost_centre_cd = b.cost_centre_cd and
a.business_date = b.business_date and
a.terminal_no = b.terminal_no and
b.transfer_type_id = 57) as FOBTClaimPaid,
(select sum(total_amount)
from @TransferTable b
where a.cost_centre_cd = b.cost_centre_cd and
a.business_date = b.business_date and
a.terminal_no = b.terminal_no and
b.transfer_type_id in (56,57)) as FOBTCashWin
from @TransferTable a


group by a.cost_centre_cd, a.business_date, a.terminal_no
order by a.cost_centre_cd, a.business_date, a.terminal_no


return

END


CRYSTAL COMMAND SQL

declare @CostCentreCD char, @StartDate datetime, @EndDate datetime
set @CostCentreCD = {?01CostCentreCD}
set @StartDate = {?02StartDate}
set @EndDate = {?03EndDate}
select * from udf_SLOTS_03a (@CostCentreCD, @StartDate,@EndDate)