Group selection

Select Expert

Formula language

your record selection formula specifies only California customers ({customer.REGION} = “CA”), your report will have only a single group: California. If you have a group selection formula that specifies only groups with the group name “CA” (GroupName({customer.REGION}) = ”CA”) and no record selection formula, you will get an identical report, assuming that the California condition was the only selection test in both situations. When using the group selection method, however, it could conceivably take longer to get the report back.

You can select groups of records using the Select Expert just like you can select individual records.

Instead of basing your selection criteria on standard fields like you do for record selection, however, you base it on group name fields or summary fields when you are setting group selection criteria.

●If you have simply grouped your data but you have not summarized it, you can only set up group selection based on the group name field. For example you may want to select only those groups whose Region is Massachusetts:

GroupName ({Customer.REGION}) = “MA”

●If you have summarized your data, you can set up group selection based either on the group name field or on the summary field. For example:

Sum({Customer.LAST YEAR’S SALES}, {Customer.REGION}) > 10000

NOTE: You can use the Select Expert to set up record selection and group selection requests. When you select either a group name or a summary field, the program knows that the selection criteria you set up is intended for group selection. In all other cases, the program knows that you are setting up record selection.

You can select groups using the formula language. To do this you activate the Group Selection Formula Editor by choosing the GROUP command from the Report|Edit Selection Formula menu.

In the Formula Editor you can build your group selection request using group fields, group name fields, and other formulas. As was

Formula templates

The following example formulas can be used as templates to help you create your own selection formulas using the Record Selection Formula Editor. The examples illustrate different kinds of selections that you can do, not necessarily the selection that is best to do from a performance standpoint. To help identify the

Record

selection templates

best way to set up your record selection, see Selection performance tips, Page 260.

NOTE: All of these formulas are available in Seagate Crystal Reports online Help so you can copy them directly into the Selection Formula Editor. Search for Record selection formula

templates in Seagate Crystal Reports online Help.

FOR SELECTING RECORDS USING CHARACTER STRINGS

“C” in {file.FIELD}[1]

«Selects those records in which the value in the {file.FIELD} field begins with the character “C” (includes values like CyclePath, Corp. and Cyclist’s Trail Co.; excludes values like Bob’s Bikes Ltd. and Feel Great Bikes, Inc.).»

not (“C” in {file.FIELD}[1])

«Selects those records in which the value in the {file.FIELD} field does not begin with the character “C” (includes values like Bob’s Bikes Ltd. and Feel Great Bikes, Inc.; excludes values like CyclePath, Corp. and Cyclist’s Trail Co.).»

“999” in {file.FIELD}[3 to 5]

«Selects those records in which the 3rd through 5th digits of the {file.FIELD} field is equal to “999” (includes values like 10999, 70999, and 00999; excludes values like 99901 and 19990).»

“Cycle” in {file.FIELD}

«Selects those records in which the value in the {file.FIELD} field contains the string “Cycle” (includes values such as CyclePath Corp. and CycleSporin, Inc.; excludes values like Cyclist’s Trail Co. and Feel Great Bikes, Inc.).»

FOR SELECTING RECORDS USING NUMBERS

Single values

{file.FIELD} > 99999

«Selects those records that have a value in the {file.FIELD} field greater than 99999.»

Selection

performance tips

“AOK” in {file.HISTORY}[3 to 5] and {file.OPENCRED} >= 5000

«Selects those records in which the {file.HISTORY} field shows the characters “AOK” as the 3, 4, and 5 characters and the {file.OPENCRED} field (the amount of available credit) is at least 5000.»

You can use these templates as is (with your own data), or combine them to create complex formulas, or you can use the principles illustrated here plus Seagate Crystal Reports online Help topics for functions and operators to create powerful selection formulas for yourself. Search for functions or operators by name in Seagate Crystal Reports online Help.

There are a number of performance-related items that you should consider when you are setting up your selection requests:

●Record selection will be faster if it is based on indexed fields instead of non-indexed fields. See Indexed tables, Page 518.

●If you have based record selection on indexed fields, make sure the Use Indexes or Server for Speed option is toggled on in the Report Options dialog box. Search for Report Options dialog box in Seagate Crystal Reports online Help.

●Avoid performing record selection based on formula fields if at all possible because it will result in less efficient reporting. For example, assume you have a formula field (@ExtendedPrice) in your report that returns the extended price of a line item (Quantity * Price). If you base your selection criteria on that formula (@ExtendedPrice > 1000, for example), the SQL server will not understand the formula so the program will not pass the selection criteria down to the server. Instead it will retrieve all of the records from the server, and then it will apply record selection on the client machine. This can tie up network resources and slow processing time considerably.

●Try to avoid subscript ranges such as:

{file.FIELD}[1 to 5]

Record

selection with a group selection formula

The program parses selection formulas and converts anything it can to SQL so that the bulk of the work can be off-loaded to the SQL server. Because there is no SQL equivalent to subscript ranges, SQL pass through will not occur. Subscripting of only the first character works, but subscripting multiple characters does not. Search for Subscript in Seagate Crystal Reports online Help.

●When using SQL/ODBC data sources, if you are unsure if the record selection is passing through to SQL or not, check it by choosing the SHOW SQL QUERY command from the Database menu. If the SQL query does not have a WHERE statement or if the WHERE statement does not mention all of your fields that you are dealing with in your record selection, then you will need to work through the formula again since the translation did not occur properly.

¾Make sure you have logged on to your data source before choosing the SHOW SQL QUERY command from the Database menu.

¾SQL syntax will change with different drivers (ODBC or SQL) but the majority follow the Oracle SQL model as a guide. Consider the fact that your driver may use slightly different syntax. This is also the case for nonSQL databases.

●Do not perform any data type conversions in the record selection formula (for example, converting a number to a string using the ToText function). Such conversions can not be translated to SQL so SQL pass through will not occur. Search for ToText in Seagate Crystal Reports online Help.

If you need to perform record selection on indexed and nonindexed fields, you can set up your record selection in two steps to maximize performance. You do this by creating a record selection formula and a group selection formula, and using them both to select records. A little explanation is called for here.

The Group Selection Formula Editor has the same functionality as the Record Selection Formula Editor. While its primary use is for setting up group selection, it can be used to set up record selection as well.