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 prints 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 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, select the menu options Crystal Reports > 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, select the menu options Crystal Reports > 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.
When a formula uses multiple conditions, they can be joined with either the AND operator or the OR operator. Each affects performance differently. To understand how this works, you have to understand how each operator is used.
When two or more conditions are combined using the AND operator, Crystal looks at each condition independently. If a condition can be converted to SQL, it is appended to the WHERE clause and passed down to the server. Any conditions that can’t be converted are left for the report engine to process. Crystal will pass as many conditions down to the server as it can and leave the rest for the client. The result is improved performance because, even though the client has to process some of the records, there will be fewer to process. Many records have already been filtered out by the server.
The OR operator works differently from the AND operator. When using the OR operator, Crystal Reports looks at all the conditions as a whole. Like the AND operator, it tries to convert each condition into SQL. But this time, if it finds that any of the conditions can’t be converted, none of them will be converted. For example, assume a record selection formula has three conditions and they are joined using the OR operator. If the first two conditions can be converted to SQL, but the last one can’t, then none of them will be passed down to the server. The entire record selection formula will be processed by the client.
The reason for this is that when using the OR operator, all records are tested for each of the stated conditions. Only passing some of the conditions doesn’t reduce the number or records that need to be passed to the client. For example, assume that there are two conditions and one of the two conditions was passed to the server. After the server processes the SELECT statement, it is left with 100 records. Even if the server performs the first test and 70 records fail, there is still a chance that these 70 records will pass the second test. However, the second test is on the client, which means which the 70 records have to get passed to the client for testing. In effect, the server ends up passing all the records to the client. Using the OR operator didn’t speed up the processing at all.
If you are using a PC database, then those rules don’t apply. Using a PC database means you can’t use the OR operator at all. Whether the individual conditions can be converted to SQL or not won’t have any effect.
If you want to find out whether a selection formula was converted into SQL, select the menu options Crystal Reprots > Database > Show SQL Query. If you see that the query includes the WHERE clause, then it was successfully converted. If the WHERE clause isn’t included, then one of the formulas or functions couldn’t get converted.
