Need help using Command object and Excel spreadsheet in report

[rorymo]

CR2008
SQL Server 2000

Hello,
I have been trying to incorporate this into a report, and can only get so far with it before I hit a wall.
I found a couple of threads where someone had a similar problem but there wasn’t anything there that I could use.

Problem:
I am trying to modify a report that is using a command object. The command object contains a declaration that is hard coded. What I want to do is use an excel spreadsheet as a datasource to replace the hardcoded part. The report is using that column as a projected total for use in the command below. I have a SQL Server db and the spreadsheet as datasources in the report.

Here is a copy of the command:
declare @datproj smalldatetime
declare @projnumber numeric(10,0)
declare @currentnumber decimal(10,0)
declare @currentdate smalldatetime

set @datproj = '2010.12.21'

set @projnumber = 289000

set @currentnumber = (Select count(*) from call_req INNER JOIN prob_ctg on
call_req.category = prob_ctg.persid where prob_ctg.sym like 'lms%')
***NOTE this variable will be replaced with a parameter.

set @currentdate = GETDATE()

select DATEDIFF(day, @datproj, @currentdate)
AS Dayssinceonboarding,

CAST(@currentnumber / DATEDIFF(day, @datproj, @currentdate) AS int)
AS AvgTicketsPerDay,

CAST(@projnumber / (@currentnumber / DATEDIFF(day, @datproj, @currentdate)) AS int)
AS DaysToClose,

dateadd(day, @projnumber / (@currentnumber / DATEDIFF(day, @datproj, @currentdate)), @datproj)
AS ReachProjectionDate


To bring in the spreadsheet columns, I use another command object:
SELECT `'SD_Tickets_'`.`Forecast FY2011`, `'SD_Tickets_'`.`F2`, `'SD_Tickets_'`.`F3`, `'SD_Tickets_'`.`F4` FROM `'SD Tickets$'` `'SD_Tickets_'`

I am trying to use the F4 column from the spreadsheet to replace “set @projnumber = 289000” but nothing I have tried works.
I tried using a select statement in place of the “289000” (“set @projnumber = Select `'SD_Tickets_'`.`F4` FROM `'SD Tickets$'` `'SD_Tickets_'`….
But that didn’t work.

When I try to combine the command for the spreadsheet it and the command for the SQL Server db, I keep getting syntax errors.
I tried removing the backwards quotes but that didn’t help. Then I removed all quotes and that didn’t help. Then I tried to select * but that didn’t work.

The datasource name in the report is shown as ‘SD_Tickets_’. I tried removing the underscores but that didn’t help either. I can’t get past the syntax errors to see whether I can bring in the column to use in the report.

I was able to pass data using a subreport, but it passed the Grand Total column from the spreadsheet, not the data from the parameter selection that I chose. (“lms”)

I hope that I am making sense with this. I might be talking apples and oranges and not be able to do it at all.

If any one has any ideas, I would greatly appreciate it!
Thanks in advance,
Rory

 

[Madawc]

You could try moving the SQL stuff to a subreport and reading the Excel in the main report, passing down the parameter using Edit > SUbreport Link. Never tried it so it might not work.

An alternative is to use the Excel values to select the data in the 'Command' table that the SQL creates. This is of course very inefficient, but I know of no better answer.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[rorymo]

Thanks. I'll try those options. I appreciate your help.
Rory