Skip to Content
Author's profile photo Jamie Wiseman

Peformance Tip – Passing Conditional Date Ranges to the Database

If you have a conditional date range filter in Crystal Reports and are reporting directly on database tables, you may notice when you refresh the report that this filter is not being processed by the database server. If the database server is not processing this filter, then too many records are being brought back into Crystal and then filtered again. This can cause a huge performance loss.



Is your date filter being processed by the database or by Crystal?


a) You can check to see if your filter is being processed by the database by going to the Database menu and then the Show SQL Query. If all of the parameterized pieces of your filter are not represented in this query, then the entire filter is not being processed by the database server.


b) You can also verify if too many records are being brought into Crystal. If you refresh the report and watch the Status Bar, this will be shown in the “Records” area of the Status Bar. If you are getting a “Records: N of M” display where N is much smaller than M, you may wish to see if you can optimize your report further.


How to increase performance when using conditional date ranges:


1) There is a way that you can help these types of conditional filters be processed by the database server instead of Crystal. First download the sample report here to see an example record selection filter. This report runs against the Xtreme sample database which ships with Crystal Reports.


2) Go to the Report menu > Selection Filters > Record to see how to structure this type of filter wherein the processing is done by the database server.



3) The most important thing to note on this filter is that the database field, {Orders.Order Date} is not being used directly against any function. For example, Year({Orders.Order Date}) or Month({Orders.Order Date}) etc. are not being used.


4) The record selection filter always results in {field} = value or {field} >= value etc.   This is what ensures that the filter is processed by the database as opposed to being processed by Crystal Reports.


5) You can apply this technique to your report by copying the date range prompt and the filter to your report and changing the {Orders. Order Date} field to the appropriate datetime field.


Please check the BusinessObjects > Forums and the Crystal and Xcelsius Support > Notes for more information on performance and server side filter processing.

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.