I am wondering if someone can help with a problem I am having while selecting data from SQL 2000 table using Crystal as part of the record selection in Crystal, I say to get all data from a specific data where the dates are between 2 dates, these were originally set as parameters that the users could select, I have not tried to change this in a number of ways so that the parameters are picked up from a view, I created, 2 sql expressions, one to pick a start date and then one to pick and end date, then created 2 formula fields that reference the results of the sql expression.  I have inserted the formula fields in to my record so then to limit the data returns, but rather than running the select statement for all the data limited between the 2 dates, it returns all data from the table and then only runs the filter at the end, this means that the report takes about 4 times as long to run, please find below what I have done

 

SQL Expression Field

sql_FirstDate = (select ay from perioddates)

sql_LastDate = (select ed from perioddates)

 

Formula Field

FirstDate = {%sql_FirstDate}

LastDate = {%sql_LastDate}

 

Selection Formulas - Record Selection

{TableName.Date} in mailto:%7b@FirstDate - {@FirstDate } to mailto:%7b@LastDate - {@LastDate }

 

I have checked in the SQL code that CR shows and it shows no use of the parameters, when I revert it back to using Parameters entered by the user it all works fine, can anyone see where I am going wrong, still a bit of a novice at this.

 

Thanks PD

Define the params in the SP and they will become available as params in the crystal report. Do not mannually create crystal params.

Primary issue here is that you have to define the params as datetime instead of date as you can in crystal. one work around is to define them as varchar and cast that into a datetime. You lose the formating and crystal date validation so it dies in the SP when a bad date is entered. There are several posts on this if you look around.

 

Thanks for the response, I did have an inkling that might have been the problem, had tried every other way of getting the parameters to be passed through.  Thanks for the confirmation its much appreciated and saves me having to waste any more time.

 

Thanks PD