Date Range Parameter vs Hardcoded - only hardcoded works?

[ainkca]

Hi all, I'm using Crytal 6 and Oracle 8 to write a Turnaround Time report for our Lab system. (I'm new to Crystal)

I got the elapsed time to stuff to work beautifully and in my testing I hardcoded the date range so I wouldn't have to keep answering the parameters. This is what I used (and takes a few minutes to run):


{TESTREQ.STATUSDATE} in DateTime(2004,05,01,00,00,00) to DateTime(2004,05,02,23,59,00) and
{TESTREQ.STATUS} = "D" and
{TEST.TESTCODE} in {?TestCode}

Now, I want to have the date range parameter values to enter at run time and I can't make it work. The hard coded date range above works fairly quickly, but the examples below take forever (more than an hour with still no returned records). I assume the field I'm using is OK since it works fine when I've got the DateTime hardcoded.

Here are some of the things I have tried:

This one has the StartDate and EndDate parameters as strings

{TESTREQ.STATUSDATE} in DTSToDateTime (Left ({?StartDate},4 ) + "," + Mid ({?StartDate},5 ,2 ) + "," + Mid ({?StartDate},7,2 ) + ",00,00,00") to
DTSToDateTime (Left ({?EndDate},4 ) + "," + Mid ({?EndDate},5 ,2 ) + "," + Mid ({?EndDate},7,2 ) + ",00,00,00") and
{TESTREQ.STATUS} = "D" and
{TEST.TESTCODE} in {?TestCode}

This one both parameters are Dates:

{TESTREQ.STATUSDATE} in DateTime (Year ({?StartDate}),Month ({?StartDate}) ,Day ({?StartDate}) ,00 ,00 ,00 ) to DateTime (Year ({?EndDate}),Month ({?EndDate}) ,Day ({?EndDate}) ,00 ,00 ,00 ) and
{TESTREQ.STATUS} = "D" and
{TEST.TESTCODE} in {?TestCode}

This didn't work either, I used both Date and Time Parameters, where all 4 parameters were strings.

{TESTREQ.STATUSDATE} in DTSToDateTime ({?StartDate} + " " + {?StartTime} + ":00:00") to DTSToDateTime ({?EndDate} + " " + {?EndTime} + ":00:00") and
{TESTREQ.STATUS} = "D" and
{TEST.TESTCODE} in {?TestCode}

I'm betting the answer is obvious, but I'm stumped. All of these seem to be working, and I get "No errors found" when I use the Check in the formula editor. Can anyone out there help me? This should have been the easy stuff.

Thanks for any insight....

ainkca

 

[Reebo99]

Create a parameter called DateRange

Set the parameter value type to DateTime, then set it to a range value.

Then use :

{TESTREQ.STATUSDATE} in {?DateRange} and
{TESTREQ.STATUS} = "D" and
{TEST.TESTCODE} in {?TestCode}

in your record selection formula.

Hope this helps, if you have problems I'm afraid I'm leaving for the weekend, but I'm sure someone else will pick it up.

Reebo

 

[ainkca]

Hi,

I had tried to do that, but in Crystal 6 there is no option for a variable to be DateTime, only Date. That's why I was trying to convert the strings to dates and times... but no luck so far. Thanks for the thought though, I appreciate it.

kim

 

[ainkca]

Here is what worked:

With some guidance from a Crystal expert that I cannot name here...

I renamed my StartDate and EndDate parameter fields, appending ".Date" to the end of each, and leaving them as Date data types.

{TESTREQ.STATUSDATE} in {?StartDate.Date} to {?EndDate.Date} and
{TESTREQ.STATUS} = "D" and
{TEST.TESTCODE} in {?TestCode}

From what I understand this worked because the .Date in the name of the parameter let Oracle do the constraint as a date instead of Crystal having to convert the string to DateTime... much more efficient. Please correct me anyone if I've assumed incorrectly.

Since I couldn't find this answer anywhere I thought I'd post it in case anyone else needed it.

ainkca

 

[synapsevampire]

This seems wrong to me.

Oracle won't understand a parameter type based on the name.

Your post lacks technical information, and trying to get a 5+ year old version of Crystal to work with a newer version of Oracle seems very wasteful, like trying to squeeze a nickel til it drops a dime

Using just a type date (not datatime) parameter probably did the trick.

There are other factors here, such as connectivity used, but the point is that you tried hardcoding a datetime, and then later used a type DATE which worked.

Try renaming your parameters to .mugwampfritters, it should still work fine.

More importantly, upgrade your version of CR, or try using a Stored Procedure in Oracle as the data source:

http://support.businessobjects.com/library/kbase/articles/c2001269.asp

-k