Slow Response to Parameter Selection

[socalvelo]

CR XI, ODBC Oracle db

My report is grouped by a Case Number, followed by details. The case number is a 9 digit string which looks like this 120012345. The first two digits represent the year. Creating a parameter and placing it into the report selection editor where the user simply enters the nine digit works fine. Immediate results.

Employees running the report would like the parameter entry to be in this format 12-12345, as the two zeros in positions 3 and 4 are never used. I created a simple formula @CaseNumber {case_number}[1 to 2]+"-"+{case_number}[5 to 9] to go with the parameter ?CaseNumber that has an edit mask AA-AAAAA. This is placed in the selection editor as @CaseNumber = ?CaseNumber. This works fine, but it takes about 5 minutes or more for the report to open. There is excess of one million records in database. If I create a second parameter selection for a date range using date values in the table it will run faster.

What I am looking for is a better solution to use the parsed case number format in the parameter selection and speed up the report.

 

[Turkbear]

By using a formula in Crystal to do the selection you have most likely moved that step from the database ( fast) to the local machine ( slow).
Take a look at the SQL created by your report and you will probably see that that part of the WHERE clause is not being passed.
Try creating a view that has the 'masked' data in a field and then use that field for the selection criteria.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[pmax9999]

I agree with turkbear. That part of the selection formula will not have been passed to the WHERE clause, meaning all the records that meet meet the other selection criteria will need to be dragged back from the database and assessed against the case number parameter.

My first suggestion would be to reverse your approach, ie rather than manipulating the database record to compare against the parameter, manipulate the parameter. Replace the {@CaseNumber} = {?CaseNumber} in your Record Selection formula with:

{your_table.case_number} = Left({?CaseNumber},2) + '00' + Right({?CaseNumber},5)

This is much more likely to be passed to the database server via the WHERE clause.

Cheers
Pete.

 

[socalvelo]

Turkbear and pmax: This is the exact solution I was looking for. I receive an immediate result using this formula.
Thank you.