SQL Expression - Crystal Report 7 1

[question911]

Below, I added the selection criteria to the "selection formula" in a report. This selection formula is using crystal functions, which slows performance. I know I can use an SQL expression field to replace crystal's currentdate and time function, but I do not know how to use the equivalent Sybase functions (ODBC functions that are exposed to create the SQL expression. I am connecting to Sybase via ODBC and using Crystal Report 7. Any pointers would help.

{Timestamp} in (currentdate) + time(00,00,00) to (currentdate) + time(14,00,00)

 

[synapsevampire]

You can get Crystal to pass the proper SQL to the database using the record selection formula. Using Crystal functions does NOT always slow performance.

It appears that you're trying to pull the current day rows up to 14:00 hours.

I think that CR 7 had the currentdatetime function, so create 2 formulas and reference those in the record selection formula, just remember NOT to use variables in doing so.

//@startdatetime
whileprintingrecords;
currentdatetime(year(currendate),month(currendate),day(currendate),0,0,0)

//@enddatetime
whileprintingrecords;
currentdatetime(year(currendate),month(currendate),day(currendate),14,0,0)

record selection formula:

(
{table.timestamp} >= @startdatetime
and
{table.timestamp} <= @enddatetime
)

Were you to have a complex requirement, I might suggest a SQL Expression, but you should be able to get this to pass the SQL easily.

Use Database->Show SQL Query to determine if it's being passed.

I also have a FAQ in this forum which addresses the record selection formula and optimizing pass through.

faq767-3825

-k

 

[question911]

Syn,

Your Crystal syntax is not correct. Instead, I supplement it with mine to create two formulas.

For starttime, I used datetime(currentdate, time(00,00,00))

For endtime, I used datetime(currentdate, time(14,00,00))

As before, the SQL is not being passed to constrain the number of records to be fetched from the database.

 

[BobSuruncle]

What about just doing everything in the Selection Formula:
{Orders.Order Date} in
DateTime(Currentdate,Time(0,0,0))
to
DateTime(Currentdate,Time(14,0,0))

I don't have v7 anymore (Thank God!!) but this gets passed in SQL in v9 and v8.

HTH
Bob Suruncle


Bob Suruncle

 

[synapsevampire]

Sorry, I should have tested, it's cdatetime, as in:

//@startdatetime
cdatetime(year(currendate),month(currendate),day(currendate),0,0,0)

//@enddatetime
cdatetime(year(currendate),month(currendate),day(currendate),14,0,0)

Loong night and only 3 hours sleep.

Don't use the whileprintingrecords.

-k

 

[question911]

Hi Bob,

Got your message after I changed my selection criteria to hard code it without referencing formulas. Now, it works like a champ. Very fast!

Thanks guys.