Filtering Records
When printing records from one or more tables, you probably don’t need to print every single record. It is common to print only a subset of the original records. For example, rather than print every customer in the database, the report print customers that have been added within the past thirty days. SQL statements use the WHERE statement to filter out records that aren’t relevant to your query.
Crystal Reports uses selection formulas for data filtering. Selection formulas can be created at two dialog boxes designed for just such a purpose: the Select Expert and the Record Selection Formula Editor. As explained before, the Select Expert sets the fields and criteria used in the selection formula. It automatically builds the selection formula in Crystal Syntax. This makes it easy for you to quickly create filters without having to know the programming syntax.
When a report is run, all the records that are returned from the database are processed by the report engine and tested against the selection formula. Records meeting the conditions in the selection formula are printed on the report. This method of processing records is reliable, but slow.
It is slow because the way the selection formulas are processed makes for three bottlenecks. Firstly, the server passes all the records to the client computer. The speed at which these records are transferred is constrained by the physical limitations of the network design as well as the traffic already on the network. Secondly, the report engine has to process all the records through the selection formula. Depending on the complexity of the formula and the number of records being processed, this can be a very time intensive process. Lastly, the passing of all these records across the network can hinder the performance of other applications on the network. As the report designer, your goal is to design a report that is displayed as efficiently as possible. These three bottlenecks can be major stumbling blocks to that reality.
The solution is to transfer the workload from the client machine to the server. This is called “pushing down data” to the server. Pushing down data derives a big improvement in performance by having the server perform the filtering. Database servers are designed to provide the optimum performance when processing massive quantities of data. It can filter out records that don’t meet a certain criteria in a fraction of the time that the report engine would take. Pushing down data has the secondary benefit of passing less data along the network back to the client. The report gets the data quicker and there is less traffic on the network that would affect other users.
Pushing down data to the server requires the server to take the record selection formula you created and test each record against it. The formula you created is saved internally using Crystal syntax. Since databases only understand SQL and not Crystal syntax, the Crystal formula gets converted to SQL. Crystal Reports tries to do this conversion with every selection formula because pushing down data gives the best performance.
Converting a formula involves the creation of the equivalent of a WHERE clause in SQL. The WHERE statement gets appended to the SELECT statement; which Crystal Reports always passes to the database server. Any one familiar with SQL would know that not only is it totally different from Crystal syntax, it also has a lot less functionality than Crystal syntax. The upshot: these differences can create problems when the formula is being converted to SQL. In fact, since Crystal Reports has a function library that is much more robust than the SQL language, it is very possible that the selection formula can’t be converted into a valid WHERE clause.
When a formula can’t be converted to a valid WHERE clause, it won’t be included in the SQL string 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. The report has to manually process all the records with the selection formula to find those that ought to be printed. The result is a much slower performance than what would have been had all the filtering had been done on the server.
Now that you know that not every selection formula can be converted to SQL, let’s look at how to make this happen as frequently as possible.
Before any formula can be converted to SQL, the report needs the feature to be 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 the report to use indexes when selecting records from a database and it 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, right-click on a report and select Designer | Default Settings. Go to the database tab and look at whether it is checked or unchecked. If it is unchecked, click on it to select it. It will now be selected for every report. If you want to set it for just the current report, right-click on a report and select Report | Report Options. The checkbox is listed near the bottom.
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 converted to SQL completely. However, you may not have a choice in whether you can write a formula that can be converted to SQL or not. 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. You have to write it so that it performs the required functionality. Converting the selection formula to SQL is a great benefit, but it is secondary to generating the necessary data.
One way to ensure the selection formula gets converted into SQL is to write it using SQL Expressions. An SQL Expression is a formula that is built using only valid SQL functions. Since it only has valid SQL functions, Crystal Reports will always be able to convert it to a valid WHERE clause. SQL Expressions are explained in greater detail in a later section.
A general rule of thumb to follow is that if you use the Select Expert, it usually results in a valid WHERE clause that will be sent to the database server. This is because the Select Expert doesn’t do anything complex. It uses basic comparison operators that can be easily converted into SQL. A good rule to follow when using the Select Expert is to only specify database fields in the criteria. Since formulas generally use functions that can’t get translated into SQL, including them in the selection could result in it not being converted. Restricting the selection formula to only database fields ensures its convertibility.
