cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Report - Stored procedure

Former Member
0 Kudos

I wrote a SQl query that includes a function package. I also, filter the data for a specific date. The query runs in TOAD in about 3 minutes. But in crystal in runs for about 6 hours.

In crystal I used the Parameter Fields option to create a date parameter.

Question: Would creating a Stored Procedure for Startdate, Enddate speed up the processing time?

If the stored procedure is the answer, how do I write it for crystal to read it along with the query?

I appreciate all assistance. Thanks.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

There shouldn't be any need for all of that. Not sure why you are seeing such a difference in run times. CR simply passes the SQL code to the database. Are you filtering by date in TOAD and not in you CR command?

If thats the case, you can pass a parameter to the SQL code as opposed bringing back ALL of the records and then filtering with the Select Expert.

What you want to do is edit you SQL command:

1) click the Create button on the right hand side of the command window

2) made a new Date Parameter... Give it the same name as the date parameter you already created in the report (it will pick up the attributes of the parameter) ... FYI this will only work if you are looking for a single date...

3) add the following to you your WHERE statement: WHERE DATEADD(dd,0, DATEDIFF(dd,0,Table.DateField)) = {?DateParameter}

The formula removes the time portion of you database field allowing the match on the date, not the date time.

If you want to be able to filter by a range of dates you have to create 2 separate date parameters (begin date & end date)... The "allow range values" setting in the report parameter won't work here.

1) make your Begin and End date parameters

2) add the following to your WHERE statement: WHERE Table.DateField >= {?BeginDateParameter} AND Table.DateField <= ({?EndDateParameter} +1)

You should see a marked improvement in your run times.

Hope this helps,

Jason

Former Member
0 Kudos

Thank you for your input.