Controlling Record Selection

One of the main benefits of Visual Basic s integration with Crystal Reports is the ability to control report record selection on the fly, based on your user s interactions with the VB application. You can design the exact user interface you need for your application and build the report record selection formula to match how your user interacts with it. Because VB has such powerful user-interface features (such as the VB date picker and other custom controls), you have much more flexibility using VB controls than you do using Crystal Reports parameter fields.

Because the record selection formula you pass to the RDC must still conform to Crystal Reports syntax, you need to be familiar with how to create a Crystal Reports formula. If you are used to using the Crystal Reports Select Expert for record selection, you need to familiarize yourself with the actual Crystal Reports formula that it creates, before you create a selection formula in your VB application. A Crystal Reports record selection formula is a Boolean formula that narrows down the database records that will be included in the report. For example, the following record selection formula will limit a report to orders placed in the first quarter of 2001 from customers in Texas:

 {Orders.Order Date} In #1/1/2001# To #3/31/2001# 
 And {Customer.Region} = "TX" 

Tip  

For complete discussions of Crystal Reports record selection and how to create Boolean formulas, consult Chapters 5 and 8.

Use the RecordSelectionFormula property of the Report object to set the record selection formula for the report. You can set the property to either a string expression or a variable. Here s the code from the sample application to set record selection, based on the contents of the user-supplied From Date and To Date text boxes:

 'Supply record selection formula based on dates 
 strSelectionFormula = "{Orders.Order Date} in #" & _ 
 txtFromDate & "# to #" & txtToDate & "#" 
 Report.RecordSelectionFormula = strSelectionFormula 

Record Selection Formula Tips

You need to consider several important points when building a Crystal record selection formula within your application. Specifically, some tricks are available that you can use to make sure that your string values are formatted properly, and that as much of the SQL database query as possible is performed by the database server rather than by the PC.

The string value you pass must adhere exactly to the Crystal Reports formula syntax. This includes using correct Crystal reserved words and punctuation. The preceding example shows the necessity of including the pound sign (#) around date values in the selection formula.

Also, it s easy to forget the required quotation marks or apostrophes around literals that are used in comparisons. For example, you may want to pass the following selection formula:

 {Customer.Region} = 'TX' And {Orders.Ship Via} = 'UPS' 

If the characters TX and UPS are coming from controls, such as a text box or combo box, you might consider using the following VB code to place the selection formula in a string variable:

 strSelectionFormula = "{Customer.Region} = " & txtRegion & _ 
 " And {Orders.Ship Via} = " & cboShipper 

At first glance, this appears to create a correctly formatted Crystal Reports record-selection formula. However, if you supply this string to the RecordSelectionFormula property, the report will fail when it runs. Why? The best way to troubleshoot this issue is to look at the contents of strSelectionFormula in the VB Immediate window, by setting a breakpoint or by using other VB debugging features. This will show that the contents of the string variable after the preceding code executes as follows :

 {Customer.Region} = TX And {Orders.Ship Via} = UPS 

Notice that no quotation marks or apostrophes appear around the literals that are being compared in the record selection formula, a basic requirement of the Crystal Reports formula language. The following VB code will create a syntactically correct selection formula:

 strSelectionFormula = "{Customer.Region} = '" & txtRegion & _ 
 "' And {Orders.Ship Via} = '" & cboShipper & "'" 

If your report will be using a SQL database, remember also that RDC will attempt to convert as much of your record selection formula as possible to SQL when it runs the report. The same caveats apply to the record selection formula you pass from your VB application as apply to a record selection formula you create directly in Crystal Reports. In particular, using built-in Crystal Reports formula functions, such as UpperCase or ToText, typically causes record selection to be moved to the client (the PC) instead of to the database server. The result can be very slow report performance. To avoid this situation, take the same care in creating record selection formulas that you pass from your application as you would in Crystal Reports. Look for detailed discussions on record selection performance in both Chapters 8 and 16.

You may also choose to create the SQL statement that the report will use right in your VB application, and then submit it to the report by setting the Report object s SQLQueryString property.