Convert Crystal Reports 2008 to CR 4.1 for enterprise with Command object

[Former Member]

Is it possible to convert a report, which contains a Command object?

The command object contains creating a temp table, a cursor, select from the temp table and dropping the temp table. Is freehand SQL supported at all?

I don't have much background in Crystal Reports (but I am a Business Object Analyst) but I have a very important Crystal Reports 2008 report, which I need to convert a  to Crystal Reports for Enterprise 4.1 SP5.

It prints picking slips every ten minutes, when there is data.

Command SQL

-- store the invreserveids for picking slips to be printed in temp1 table

CREATE TABLE #Temp1 (invreserveid INT)

INSERT INTO #Temp1 (invreserveid)

SELECT invreserveid FROM YTPickingSlip3EGM

-- create temp2 table and populate with UPDATE statements from ytpickingslip3EGM (note this is EGM-IST only)

create table #Temp2 (sqlcommand varchar(500))

DECLARE @sqlCommand1 nvarchar(1000)

SET @sqlCommand1 = 'SELECT substring(SqlString,1,500) from ytpickingslip3EGM'

insert into #Temp2

EXECUTE sp_executesql @sqlCommand1

-- execute the UPDATE commands stored in the temp2 table and then delete temp2

-- *** NOTE - this is the part which writes the flags etc back to the database

DECLARE @sqlcommand varchar(500)

DECLARE db_cursor CURSOR FOR

SELECT sqlcommand FROM #Temp2 ORDER BY 1

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @sqlcommand

WHILE @@FETCH_STATUS = 0

BEGIN

--       PRINT @sqlcommand

       EXEC (@sqlcommand)

       FETCH NEXT FROM db_cursor INTO @sqlcommand

END

CLOSE db_cursor

DEALLOCATE db_cursor

drop table #Temp2

-- use the invreserveids saved in temp1 to get and print the picking slips

SELECT *

FROM YTPickingSlip2

WHERE invreserveid IN (SELECT invreserveid FROM #Temp1)

DROP TABLE #Temp1

-- end of process

[abhilash_kumar · Accepted solution]

Hi Anatoli,

CR for Enterprise 4.1 does support Command Objects ( I guess SP04 onwards).

However, a lot of issues have been reported with this type of source. It doesn't seem to work as well as the legacy CR designer does.

Having said that, why don't you consider migrating to CR 2013 instead?

-Abhilash

[Former Member · Accepted solution]

Thanks, Abhilash.

We only have CR for Enterprise 4.1.

It doesn't seem to understand the Command object from CR 2008. Besides, just running the above SQL usually doesn't produce any data. YTPickingSlip3EGM is a view and it has data only under some conditions (I still need to determine this but I am using hard-coded values for testing). Basically, I can't paste the same SQL to create a command in CR 4.1. Sorry, if it's confusing.

[abhilash_kumar · Accepted solution]

Like I said, CR For Ent 4.1 doesn't seem to work well with command yet.

Have you thought about using CR 2013 instead?

-Abhilash

[Former Member · Accepted solution]

I have made some progress with CR for Enterprise, using "SQL Command", although it didn't convert automatically and the report didn't know how to handle "Command" object.

As I said, we don't have CR 2013, have to stick to CR for Enterprise.

Now, the problem is with the empty data set or no data set. This version of CR requires some records to be returned from this query (per above code).

SELECT *

FROM YTPickingSlip2

WHERE invreserveid IN (SELECT invreserveid FROM #Temp1)

The records from YTPPickingSlip2 are all records for this report, there's nothing else.

In the old CR 2008, the report works (compiles) fine but displays nothing when there are no records in YTPPickingSlip2, which is the expected behaviour.

The requirement is to run the report - 1. print picking slips when there are records, 2. do nothing, if there are no records.

I am temporary using some hard-coded values (to force some values), as the data is not always there. Someone suggested to use a stored procedure. Will it work with no rows returned? How do I use stored procs in CR?

Also, is there a way to rename "SQL Command"?

[abhilash_kumar · Accepted solution]

A stored procedure should work fine too.

When you create a new connection to the database, you should see Stored Procedures as one of the options along with Tables and Views.

Expand this option and select the SP you'd like to connect to from the list.

If your report is nearing completion, you can simple go to the 'Set data source' option and map the existing fields on the report to fields in the SP.

P.S: if you have CR for Ent 4.1, you're eligible to use CR 2013 using the same license key.

-Abhilash