I M Having Problems Passing Integers to Crystal Reports XI (11

I’m having problems passing integers to Crystal reports XI (11.5) , I have no issue with passing lists of string from the DB, The system is UCCX 7.01 SR5 , with MSDE (not SQL). The integers are to define the number of seconds for abandon reporting.

The stored procedure works fine and delivers the data I require. The example that is shown here is one combination that does not work and uses strings for the parameter into the stored procedure (which converts to integer interally). Ive also tried the Type=”STRING” two.....

<SQLParameter ParamName="$Bucketint1" ParamDefaultValue="45">

<ParamNameText>No of Seconds 1st Bucket Interval</ParamNameText>

<ParamHelpText>No of Seconds 1st Bucket Interval</ParamHelpText>

<TextBox GUIComponentName="Interval 1" Type="STRING"/>

</SQLParameter>

Each time I change the combination , I regenerate a Crystal report for it to ensure there is no misalignment.

Which ever combination I pick the historical client allows me to pick the parameters, trys to execute and then errors with the following errors in the log file REGARDLESS OF THE COMBINATION , I would expect it to at least give and error. I have also removed the ' from around the $Bucketint parameter and the result is the same ..... what am I doing wrong ?

Stored procedure header .....

ALTER PROCEDURE OBS_Procs_1 (@StartDate DateTime , @EndDate as DateTime , @ServiceName varchar(40) ,@Bucketint1 varchar(10) , @Bucketint2 varchar(10), @BucketInt3 Varchar(10), @SortBy int) – again I have tried all combinations of INT and varchar.

Error - 94: 12/2/2011 08:14:13 %CHC-LOG_SUBFAC-3-UNK:SQLInterpreter: variable($Bucketint1) can only have alpha characters

Full log file enclosed.

I have tried to compare it against integer parameters in Cisco reports and I cant see what I’m doing differently.

<?xml version="1.0" encoding="UTF-8"?>

<ReportDescription>Abandoned Call Detail Activity Report shows the details of the calls abandoned By Service.</ReportDescription>

<ReportSQLCommand>Exec db_cra.dbo.OBS_Procs_1 '$StartDate', '$EndDate',N'$ServiceName','$Bucketint1','$Bucketint2','$Bucketint3', $SortBy</ReportSQLCommand>

<ParamNameText>Service Name</ParamNameText>

<ParamHelpText>Names the service that you wish to report on</ParamHelpText>

<SQL>SELECT distinct customVariable5 FROM db_cra.dbo.OBS_Custom_View_1</SQL>

</selectedListOperations>

</DBListToList>

</SQLParameter>

<ParamNameText>No of Seconds 1st Bucket Interval</ParamNameText>

<ParamHelpText>No of Seconds 1st Bucket Interval</ParamHelpText>

</SQLParameter>

<ParamNameText>No of Seconds 2nd Bucket Interval</ParamNameText>

<ParamHelpText>No of Seconds 2nd Bucket Interval</ParamHelpText>

</SQLParameter>

<ParamNameText>No of Seconds 3rd Bucket Interval</ParamNameText>

<ParamHelpText>No of Seconds 3rd Bucket Interval</ParamHelpText>

</SQLParameter>

</ReportParameterList>

<ReportFormats</ReportFormats>

<ReportFormats>Table</ReportFormats>

<ListOption OptionSelected="true" OptionValue="0">No Option to Sort</ListOption>

</ReportSortBy>

</Report>

</ReportTemplateDefinition>

ALTER PROCEDURE OBS_Procs_1 (@StartDate DateTime , @EndDate as DateTime , @ServiceName varchar(40) ,@Bucketint1 varchar(10) , @Bucketint2 varchar(10), @BucketInt3 Varchar(10), @SortBy int)

set DATEFIRST 1

Declare @tmp_Start DateTime,

@tmp_DayNo int,

@tmp_Var5 varchar(40),

@tmp_Var6 varchar(40),

@tmp_Contacts int,

@tmp_Bucket_1 int,

@tmp_Bucket_2 int,

@tmp_Bucket_3 int,

@tmp_Bucket_4 int,

@tmp_bucket_thresh1 int,

@tmp_bucket_thresh2 int,

@tmp_bucket_thresh3 int,

@tmp_startsearch DateTime,

@tmp_endsearch DateTime,

@tmp_displaydate varchar(10),

@tmp_dayname varchar(10),

@tmp_handled int,

@tmp_lost int

set @tmp_bucket_thresh1 = convert(int,@Bucketint1)

set @tmp_bucket_thresh2 = convert(int,@Bucketint2)

set @tmp_bucket_thresh3 = convert(int,@Bucketint3)

set @tmp_startsearch = @StartDate

set @tmp_endsearch = @EndDate

set @ServiceName = replace(@ServiceName,'OR|','')

create Table #Temp ( Start DateTime,

DayNo int,

Var5 varchar(40),

Var6 varchar(40),

Contacts int,

Bucket_1 int,

Bucket_2 int,

Bucket_3 int,

Bucket_4 int,

Displaydate varchar(11),

Dayname varchar(10),

Handled int,

Lost int

)

insert into #Temp (Start,DayNo,Var5,Var6,Contacts,Bucket_1,Bucket_2,Bucket_3,Bucket_4,Displaydate,Dayname,Handled,Lost)

SELECT

Min(startDateTime)

,max(datepart(dw,startDateTime))as DayNo

,customVariable5

,CustomVariable6

,Count(*) as NumberOfContacts

,Count(queue_time) as Bucket_1

,Count(queue_time) as Bucket_2

,Count(queue_time) as Bucket_3

,Count(queue_time) as Bucket_4

,dateadd(dd,0,DATEDIFF(dd,0,startDateTime)) as Displaydate

,' ' as Dayname

,count(queue_time) as Handled

,count(queue_time) as Lost

from OBS_Custom_View_1 where

(startDateTime > @tmp_startsearch and startDateTime < @tmp_endsearch) and

customVariable5 = @ServiceName

group by dateadd(dd,0,DATEDIFF(dd,0,startDateTime)),customVariable5,CustomVariable6

Declare Trawl CURSOR for

Select * from #Temp

Open Trawl

fetch next from Trawl into

@tmp_Start,

@tmp_DayNo,

@tmp_Var5,

@tmp_Var6,

@tmp_Contacts,

@tmp_Bucket_1,

@tmp_Bucket_2,

@tmp_Bucket_3,

@tmp_Bucket_4,

@tmp_displaydate,

@tmp_dayname,

@tmp_handled,

@tmp_lost

while @@FETCH_STATUS =0

begin

update #Temp

set Bucket_1 = (select count(*) from OBS_Custom_View_1 where