Report fails with certain parameter

[Hypermommy]

Hi all,

I am having a problem I hope you can help me with. I'm using CE9.0.

I have this one report that takes a performance type as a parameter (among others). The report runs fine in ePortfolio for all performance types except IC. But the odd thing is that the IC performance type runs fine both on my development machine and when I preview the report in the Crystal Management Console.

I'm getting an error that says "Print engine failure" but again, only when using IC and only in ePortfolio, not anywhere else.

Any idea why this happens?

-= Hypermommy =-

 

[synapsevampire]

Please post all of the code referencing the IC parameter.

-k

 

[Hypermommy]

I'm not sure I understand. No code in ePortfolio and no code in my report other than the asking the user for the parameter. Maybe the stored procedure needs to be posted? But I don't think that would be the problem since it works everywhere else?? I'm so confused. So should I post the stored procedure?

-= Hypermommy =-

 

[ShortyA]

Hi,
if you have Crystal Reports installed on the same server as CE then try running the report under that. If there are any UFL (non standard functions or ones that have been added on) that are on your machine and not on the CE server then the report will not run on the CE server.

I think what Synapsevampire was trying to find out was what happens if the IC parameter is used. For example, if any other value is selected then xyz happens but if IC is selected then a stored procedure or maybe more complex processing is done. It may help if you posted the record selection formula.
ShortyA

 

[Hypermommy]

Hi there,

Thanks all for the help. I will find out if Crystal (developer) is installed on that box and try to run it with that and see what I get.

There is no record selection formula. The SP handles all record selection and Crystal just spits out what's produced. When I try the report on my local machine or run the SP through Query Analyzer I get expected results. But something on that box is preventing the return of records when the performance type is "IC". Hopefully I'll find it when I run the report on that box in developer, if I can.

However, I'm including the code from the SP here, just in case you guys see anything wierd about it. I don't think it's that (otherwise, I'd get some error or no records or something when I run the SP in Query Analyzer) but since I'm not sure, I'm posting it.

Thanks again for all your help!!!



create PROCEDURE usp_hst014
@BeginPermitholder INT,
@EndPermitholder INT,
@BeginDate smallDATETIME,
@EndDate smallDATETIME,
@PType varchar(512)
AS
set nocount on

declare @BeginDateString varchar(16)
declare @EndDateString varchar(16)

set @BeginDateString = convert(varchar(16),@BeginDate,1)
set @EndDateString = convert(varchar(16),@EndDate,1)

declare @strTmp as varchar(218)
declare @strWhere as varchar(256)
declare @strSub as varchar(16)
declare @intChar as int
declare @intStart as int
--declare @intflag as int

--set @intflag = 0

if (substring(@PType,1,3) = 'All')
begin
set @strWhere = '1=1'
end
else
begin
set @intStart = 1
set @strWhere = ''
set @strTmp = @PType
set @intChar = charindex(',',@strTmp,1)
while @intChar > 0
begin
set @strSub = substring(@strTmp,@intStart,@intChar - @intStart)
--if upper(RTrim(LTrim(@strSub))) = 'C'
--begin
--set @intflag = 1
--end
--print @strSub
set @strWhere = @strWhere + 'dtetype = ''' + RTrim(LTrim(@strSub)) + ''' or '
set @intStart = @intChar + 1
set @intChar = charindex(',',@strTmp,@intChar + 1)
end
set @strSub = substring(@strTmp,@intStart,100)
--if upper(RTrim(LTrim(@strSub))) = 'C'
--begin
--set @intflag = 1
--end
--print @strSub
set @strWhere = @strWhere + 'dtetype = ''' + RTRim(LTrim(@strSub)) + ''''
--print @strWhere
end

create table #working_table (
dtascode int,
dtastype char(1),
--dtetype char(2),
pmw_handle bigint,
numraces smallint,
number_of_performances smallint,
number_of_racing_days smallint,
total_paid_attendance int,
admission_tax decimal(10,2),
admission_tax_credit decimal(10, 2),
public_winnings decimal (12,2),
minus_breakage decimal (14, 2),
guest_takeout decimal (10,2),
daily_license_fee decimal (10, 2),
tax_on_handle decimal (12, 2),
tax_credits decimal (12,2),
state_breakage decimal (14,2),
--retained_by_track decimal (14,2),
purses decimal (12,2),
p_and_e_purses decimal (13,2),
owners_awards decimal (13, 2),
track_breakage decimal (14,2),
players_awards_breaks decimal (14,2),
breeders_assn_awards decimal (13,2)
)

DECLARE primary_pull CURSOR FOR
select distinct dtascode
from R301_handle_dist
where dtascode >= @BeginPermitholder
and dtascode <= @EndPermitholder
and dtpdate between @BeginDateString and @EndDateString


--step through each of the permitholders listed in the above cursor. For each of these
-- run the SQL Query below

declare @CurrentPH INT
--declare @CurrentType varchar(3)
declare @execStr varchar(8000)
declare @strTmpWhere varchar(256)

open primary_pull

fetch next from primary_pull into @currentPH

while @@FETCH_STATUS = 0
BEGIN
set @execStr = ''
set @execStr = '
INSERT INTO #working_table (dtascode,dtastype, pmw_handle,numraces,
number_of_racing_days, total_paid_attendance, admission_tax,
public_winnings, minus_breakage, daily_license_fee, tax_on_handle,
tax_credits, purses)
select
dtascode,dtastype,
sum(dttaxhandl) as pmw_handle,
sum(dtnumraces) as numraces,
count(distinct dtpdate) as number_of_racing_days,
sum(dttotpdat) as total_paid_attendance,
sum(dtadtax) as admission_tax,
sum(dtpublic) as public_winnings,
sum(dtmnsbrk) as minus_breakage,
sum(dtdaylic) as daily_license_fee,
sum(dtstcomm) as tax_on_handle,
sum(dttaxcr) as tax_credits,
sum(dtpurses) as purses
from R301_handle_dist where dtascode = ' + cast(@CurrentPH as char(3)) + ' and
(dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + ''') and (' + @strWhere + ')
group by dtascode,dtastype
order by dtascode'

--print @execStr
exec (@execStr)
-- update number_of_performances
set @execStr = 'update #working_table set number_of_performances = (select count(dtpnumber) from R301_handle_dist where dtascode = '
+ cast(@CurrentPH as char(3)) + ' and dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString
+ ''' and dtattendancereq = ''Y''
and (' + @strWhere + '))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)

set @execStr = 'update #working_table set admission_tax_credit =
(CASE
when dtastype = 3 then 0.00
when dtastype = 4 then 0.00
when dtastype <> 3 then (select sum(dtadtax) from r301_handle_dist
where dtascode >= ' + cast(@BeginPermitholder as char(3)) + ' and dtascode <= ' + cast(@EndPermitholder as char(3)) + '
and dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + '''
and dtadtax < dttaxcr and dtadtax > 0.0
and (' + @strWhere + '))
END)
where dtascode = ' + cast(@CurrentPH as char(3))

exec (@execStr)

set @execStr = 'update #working_table set admission_tax_credit = admission_tax_credit +
(CASE
when dtastype = 3 then 0.00
when dtastype = 4 then 0.00
when dtastype <> 3 then (select sum(dttaxcr) from r301_handle_dist
where dtascode >= ' + cast(@BeginPermitholder as char(3)) + ' and dtascode <= ' + cast(@EndPermitholder as char(3)) + '
and dtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + '''
and dtadtax >= dttaxcr and dtadtax > 0.0
and (' + @strWhere + '))
END)
where dtascode = ' + cast(@CurrentPH as char(3))

exec (@execStr)

-- update state breakage
set @execStr = 'update #working_table set state_breakage =
(isnull((select sum(dtbrk) from r301_handle_dist
where dtascode = ' + cast(@currentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dtbrksflag = ''P''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)

-- update p&e breakage
set @execStr = 'update #working_table set p_and_e_purses =
(isnull((select sum(dttakeoutamt) from r301_handle_dist_takeout
where dtascode = ' + cast(@currentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dttakeoutcode = ''7''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)

-- update owners awards
set @execStr = 'update #working_table set owners_awards =
(isnull((select sum(dttakeoutamt) from r301_handle_dist_takeout
where dtascode = ' + cast(@currentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dttakeoutcode = ''6''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)

-- update track breakage
set @execStr = 'update #working_table set track_breakage =
(isnull((select sum(dtbrk) from r301_handle_dist
where dtascode = ' + cast(@CurrentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dtbrksflag = ''I''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)

-- update player breakage
set @execStr = 'update #working_table set players_awards_breaks =
(isnull((select sum(dtbrk) from r301_handle_dist
where dtascode = ' + cast(@CurrentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dtbrksflag = ''S''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)

-- update breeders association awards
set @execStr = 'update #working_table set breeders_assn_awards =
(isnull((select sum(dttakeoutamt) from r301_handle_dist_takeout
where dtascode = ' + cast(@CurrentPH as char(3)) + '
and (' + @strWhere + ')
and dtpdate between + ''' + @BeginDateString + ''' and ''' + @EndDateString + ''' and dttakeoutcode = ''P''), 0.00))
where dtascode = ' + cast(@CurrentPH as char(3))
exec (@execStr)


set @strTmpWhere = replace(@strWhere,'dtetype','gtdtetype')
-- update guest takeout
set @execStr = 'update #working_table set guest_takeout =
(isnull((select sum(gtdtgtcomm) from r309_guest_handle where gtdthost = ' + cast(@CurrentPH as char(3)) + '
and gtdtpdate between ''' + @BeginDateString + ''' and ''' + @EndDateString + '''
and (' + @strTmpWhere + ')),0.00))
where dtascode = ' + cast(@CurrentPH as char(3))

exec (@execStr)

fetch next from primary_pull into @CurrentPH

END

--close the cursor
close primary_pull
deallocate primary_pull


select
sum(pmw_handle) as pmwHandle,
sum(numraces) as num_races,
sum(number_of_performances) as numPerformances,
sum(number_of_racing_days) as numRacingDays,
sum(total_paid_attendance) as totalPaidAtt,
sum(admission_tax) as admTax,
sum(admission_tax_credit) as admTaxCredit,
sum(public_winnings) as pubWin,
sum(minus_breakage) as minusBreakage,
sum(guest_takeout) as guestTakeout,
sum(daily_license_fee) as dailyLicFee,
sum(tax_on_handle) as taxHandle,
sum(tax_credits) as taxCredit,
sum(state_breakage) as stateBreakage,
sum(purses) as Purse,
sum(p_and_e_purses) as pePurse,
sum(owners_awards) as ownerAwards,
sum(track_breakage) as trackBreakage,
sum(players_awards_breaks) as playerAwards,
sum(breeders_assn_awards) as breederAwards
from #working_table

drop table #working_table

-- once finally done, output the results (in the working table) to the report

GO


-= Hypermommy =-

 

[ShortyA]

Good grief.
I think the first step will be to test this on the CE server (or test one). I am not too familiar with SPs but assume that the versions that are being called locally and on the CE server are the same.

Hopefully there are some CR developers who have more experience on SPs than me that could shed some light ?

 

[robnmo]

I don't have much experience with SP's on CE. But you want to look at this white paper by Crystal,

http://support.businessobjects.com/communityCS/TechnicalPapers/ce9_no_printer.pdf.asp.

Basically some printers/settings aren't available to the local system. So you have to run a few Crystal services under a local admin account or a domain account with local admin rights to the server, those services are running on.
The Page server and the Report Job Server should be the only ones you need to run under a different account.

I hope this helps

 

[synapsevampire]

I would be check the user/pw that is running from the CE server for permissions to the SP and it's objects, and I would verify that they are pointing at the same database, the same SP, and that they are using the exact same type of connectivity.

You might also verify that CE is up to date with service packs.

Another thing to check would be running the SP directly from the CE server using Query Analyzer or some such (if Crystal isn't installed).

-k