Crystal Report 2008 taking 2 hours to run

The problem is probably due to the
{@DOS} = {?Date of Service}
in the record selection condition.

Find a way to convert {@DOS} from a regular formula to a SQL Expression or use another approach to make sure that condition finds its way into the SQL WHERE clause.

Agree.  If you look at the SQL, the date filter isn't included therefore all the data is being returned to the report (client) for filtering based on the date.

What is the @DOS formula?
I suspect it is converting the date from the database format to a date.  Rather than converting the database date how about converting the date the user enters to the correct format for comparison with the database date.

One thing you could do would be to add a filter on the date of service field in the database to limit the records to this year.  That would probably pass fewer records to the report for filtering.

Sorry, I have responded I was off work yesterday.  

The @DOS formula is CDATE({Assc99.D_Appt}-18261).  The software dictionary says:
"The CDate (number) is a value representing the number of days starting from December 30,
1899. The date can be positive or negative, and is truncated if fractional. There are 18261 days
between 01/01/1850 and 12/30/1899 and then another 18263 days to 12/31/1950. The long
integer dates would be CDate (value-18261) and the short integer dates would be CDate
(value+18263)."

This is the formula that is used in the report:
ToText ({@DOS}, "MMddyyyy")

KR

That confirms the suggestions above.

The report also has a date range which is usually 1 weeks worth of data.
When the report is run the date range to pull is entered.

How would I make this SQL formula appear in the WHERE CLAUSE  to show the DOS formula?  Do I use the @DOS formula or the @New DOS?

KR

Please read this: http://kenhamady.com/cru/archives/2665

You need to convert the date entered into a number.

If the date field is really the number of days since 30 Dec 1899 try this as the filter

{Assc99.D_Appt} = DateDiff('d',Date(1899,12,30),{?Date of Service})

mlmcc

I tried entering the formula above in the Formula Workshop and got this error attached.

KR
H--CR-error.docx

What type of value is {?Date of Service} ?  The assumption was that it's a date or datetime, but apparently it's something else.

 James

{?Date of Service} is a parameter.  I have added it to the original file I sent.
H--H--CR-report-Question.docx

I have eliminated all patient data and here is the report.
H--FPO-Press-Ganey-test-date.rpt

OMG.  It only took 1 minute to run this report.  I ran it three times and changed the dates because I couldn't believe it.  
One question though.  In the script you say  + 18261 and my script said -18261.  I just wondered why that still worked?

KR

It is on the other side of the compare.

mlmcc

thanks again for all your help.

KR

Just in case you don't really understand what's happening ...

 Your original formulas subtracted 18261 from Assc99.D_Appt, converted the result to a date, and then compared that date to the parameter.  That meant that CR had to read each record that met your other conditions, so that it could get D_Appt, subtract 18261, etc.

 The new formula takes the opposite approach, converting the dates in your parameter to numbers and adjusting them to match the values in D_Appt by adding 18261, so CR can just pass those values to the server, so that they can be compared to D_Appt on the server, instead of CR reading each record and checking it.  So CR is only sent the records for the desired dates.  If you're dealing with a lot of data, that kind of thing can make a huge difference.

 And if you already knew all of that, nevermind.  :-)

 James

Thanks to everyone.  I am keeping all this valuable information in a folder for future reference.

KR