Optimizing Record Selection

The WHERE clause of the SQL query specifies how records are filtered. It lists the fields to be filtered on and the criteria for specifying which records to select. This is what you do when using the Record Selection dialog box to specify which records you want displayed on the report.

When Crystal Reports builds the SQL query to pass to the database server, it looks at the record selection formula and tries to convert it to its SQL equivalent. When it is successful with the conversion, the database filters the records at the server prior to passing the result set back to Crystal Reports. This results in a significant performance improvement.

When the record selection formula can’t be converted to a valid WHERE clause, it won’t be included in the SQL query passed to the database server. Only the SELECT portion of the SQL statement is sent. This results in the database server passing all the records from the SELECT statement back to the client computer. More traffic is put on the network and Crystal Reports has to manually process all the records using the selection formula. The result is a much slower performance than what would have been, had all the filtering been done on the server.

Of course, your goal is to write record selection formulas that can be converted to SQL and processed on the database server. Let’s look at how to make this happen as frequently as possible.

Before any formula can be converted to SQL, the report needs this feature turned on. The option “Use Indexes or Server for Speed” must be enabled for the report to push data down to the server. This option tells Crystal Reports to use indexes when selecting records from a database and it also tells it to use the server to improve performance whenever possible. You can set this option to be turned on by default for all reports or for just the current report. To make it the default setting for all reports, select the menu options File > Options. Click on the Database tab to select it there. If you want to set it for just the current report, select the menu options File > Report Options. The checkbox is near the middle of the dialog box.

PC databases (e.g. MS Access) have an additional restriction for pushing down data. The fields in the record selection formula must be indexed.

After enabling this option for the report, you want to create a selection formula that would deliver the best performance for your report. The optimum selection formula is one that can be completely converted to SQL. However, you can’t always write the formula so that it can be converted to SQL. As mentioned earlier, Crystal syntax has a lot more functionality than SQL. Since the purpose of a formula is to carry out the requirements of the report’s design, you don’t have a lot of choice about what will be in the formula. Converting the selection formula to SQL is a great benefit, but it is secondary the overall goal of generating the required data.

When determining whether a selection formula can be converted to SQL or not, Crystal Reports doesn’t do any advanced analysis. If it sees a simple formula then it will usually try to convert it. But even something like including a calculation within the formula will throw it off and it won’t convert it to SQL. The easiest way to write record selection formulas that are converted to SQL is to use the Select Expert dialog box. The Select Expert only creates very simple selection formulas and these can all be converted to SQL. Writing custom formulas is when you start running the risk of them not being converted.

Here are some rules to consider when creating your selection formulas:

• Use the following operators: =, <, >, <>, <=, >=, StartsWith and Like.
• The left side of the comparison operator must be a database field. It can not be a formula field or perform any calculations.
• On the right side of the comparison operator, you can do simple calculations
• The IsNull() function can be converted.
• Use the boolean operators And, Or.
• Use the In range operator for numbers and dates only. Do not use it for strings.