2Activate the printer you want to use if it is not already the active printer. The options for paper size are directly related to the selected printer.

For example, the HP LaserJet driver (PCL) offers a choice of letter, legal, executive or A4 paper sizes, whereas the PostScript printer driver lets you choose from letter, legal, note, A4, B5, letter small, and A4 small paper sizes.

3Select either Portrait or Landscape orientation by clicking the appropriate option button in the Orientation section.

4Select the desired paper Size and Source from the drop-down lists in the Paper section.

5Click OK when finished.

NOTE: The Remove and Remove All buttons can also be used to delete formulas.

HANDS-ON (Conditional Formatting)

How to create a footer that appears on all pages but the first

You may choose to print a page footer on all pages except the first page. You can do this by formatting the Page Footer section conditionally, using an on or off property.

Formatting

221

1Place the field you want displayed as a page footer in the Page Footer section of the report.

2Click the SECTION EXPERT button on the standard toolbar. The Section Expert appears.

			4 Click the Conditional Formula button
3 Highlight the Page Footer			to the right of the Suppress property to
section from this list.			open the Formula Editor.

5Enter the following formula in the Format Formula Editor:

PageNumber = 1

This formula suppresses the page footer on the first page but not on any of the other pages.

6Click the Save formula and Close button.

●If there is an error in the formula, a message box appears, asking whether to exit without examining the error. If you click No, a second message box will be displayed, detailing the error.

●If there is no error in the formula, you are returned to the Section Expert. Note that the Formula button has changed. This indicates that a formula has been entered for that property.

Search for Formula Compiler Errors in Seagate Crystal Reports online Help.

7Click the PREVIEW button on the standard toolbar to preview the report and ensure that the page footer appears on all pages but the first.

222	Seagate Crystal Reports User’s Guide

Right-click the currency or number field you want formatted and choose HIGHLIGHTING EXPERT from the shortcut menu, or select the frame you wish to highlight and click the HIGHLIGHTING button on the Formatting toolbar.The Highlighting Expert appears.

NOTE: If you have a multi-line page footer and you have inserted the lines into separate Page Footer sections, you will need to suppress each of those sections conditionally, using the formula above.

NOTE: To create a page header that appears on all pages but the first, put the header information in the Page Header section and then suppress that section conditionally, using the same formula that was used for suppressing the Page Footer section.

How to conditionally format fields using the Highlighting Expert

NOTE: You can only highlight currency and number fields.

1

2 Select a comparison from this drop-down list.

3 Enter a numeric value in the adjacent text box.

4To specify formatting changes, set the Font color, Background, and Border using the appropriate drop-down lists in the Item editor section of the dialog box.

5Apply new formulas as needed. Use the Priority arrow buttons to specify the priority for each formula.

6Click OK to return to the report.

Formatting

223

How to Use the Highlighting Expert

Setting highlighting priorities

Seagate Crystal Reports includes a powerful Highlighting Expert that allows you to efficiently apply conditional formatting to currency and number fields. The Highlighting Expert is most commonly used for highlighting field values that are in some way distinguished from the other values in the report. For example, if you wanted to highlight the {customer.LAST YEAR’S SALES} field in red whenever the sales are greater than $25,000, you could specify in the Highlighting Expert that, if the value is greater than $25,000, a red background color is applied to the field. You can also use the dialog box to set conditional font color and border style.

NOTE: Think of the Highlighting Expert as an alternative to the formula editor. The Highlighting Expert essentially runs the following equation: If Condition is True, Then Apply These Formatting Specifications. For this purpose, the dialog box is divided into two sections, the Item list section (which displays the formula), and the Item editor (which allows you to set the formula). The Item editor includes a Sample view to demonstrate the formatting specifications applied.

Here are some additional benefits to note:

●The Highlighting Expert can also be used for numeric/currency fields in Cross-tabs, as well.

●You can undo highlighting.

The Priority buttons in the Item list section of the Highlighting Expert allow you to set the priorities for your formulas. This is useful when you have two or more formulas that could offer conflicting results in some situations.

For example, suppose you are highlighting the Unit Price field on the report. You assign to this field a highlighting formula that shows a yellow background when a unit price is greater than $100. Imagine then, that on this same report, you create another highlighting formula that shows a red background when a unit price is greater than $200. Considering that 100 is a subset of 200, you could have Unit Price fields with yellow backgrounds when, in fact, those fields should have red backgrounds. In other words, a unit price of $300 could receive either a red or a yellow background depending on which formula has been assigned priority.

To set the priority for a formula:

1 Select the formula.

224	Seagate Crystal Reports User’s Guide

2Use the Up and Down Priority buttons to move the selected formula to a position above or below the conflicting formula(s).

NOTE: A formula has priority over another formula when it is higher in the Items list section.

3Click OK.

4Refresh the report (or click the Preview tab) to see the highlighting changes.

NOTE: The Remove and Remove All buttons can also be used to delete formulas.

Formatting

225

226	Seagate Crystal Reports User’s Guide

11	Record and Group Selection

What you will find in this chapter...

Record selection, Page 228

Group selection, Page 231

Record selection formula templates, Page 233 HANDS-ON (Record and Group Selection), Page 238

Record and Group Selection

227

Record selection

Specifying records/groups to be included

Setting up record selection

When a field is selected to appear on a report, field values from every record in the active table(s) are printed by default. In many cases, you may not want to include all the values, but only a subset of those values. For example, you may want to include:

●records only for a specific group of customers,

●records for a specific range of account numbers out of the total number of records in the database, or

●values from only those records that fall within a particular date range.

You can select records in one of two ways:

1.by using the Select Expert, or

2.by using the Formula Language.

USING THE SELECT EXPERT

Seagate Crystal Reports includes a very sophisticated formula language which you can use to specify virtually any type of record selection. However, you may not always need the flexibility in record selection that the formula language provides. The Select Expert is designed for such instances.

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

The Select Expert makes it easy to specify the records you want included in your report. You simply highlight the field on which you intend to base your selection and then set the selection criteria. If you want to set additional criteria for the selected field or if you want to base record selection on additional fields, then the Select Expert provides the tools you need. See How to set up record selection using the Select Expert, Page 238, and search for Select Expert in Seagate Crystal Reports online Help.

The Expert can be used to set up simple record selection requests. For example:

●customers from Arizona,

●orders in the first quarter, or

●sales over $10,000.

228	Seagate Crystal Reports User’s Guide

The Expert can also be used to set up some very sophisticated requests:

●customers whose names start with “A”, “M”, or “S”, or

●customers from California or Florida who ordered in July.

These are all range limit requests. One or more constants define the range. The program compares the field value in each record to the constant(s) and rejects records with values outside the range. The report is thus limited to values within the range. The Select Expert handles requests like this with ease.

NOTE: When creating a range limit request based on part of a field value, you are limited as to what you can do in the Select Expert.

You can set up all of these types of record selection requests without any previous knowledge of the formula language.

NOTE: You can use the formula language from within the Select Expert if you wish, but that masks its main purpose. The Select Expert’s purpose is to create powerful record selection requests using simple point-and-click processes.

USING THE FORMULA LANGUAGE

If you are planning to create a selection formula using the formula language, you can go directly to the appropriate Selection Formula Editor by using the EDIT SELECTION FORMULA command on the Report Menu, then choose either RECORD or GROUP from the submenu.

From the Selection Formula Editor, you can build your selection request using fields, functions, operators, and other formulas. The only restriction is that the resulting formula must be Boolean; that is, it must return either a True or False value.

Related Topics

For complete instructions on creating formulas, see Introduction to Formulas, Page 291.

For sample record and group selection templates, see Record selection formula templates, Page 233, and How to use record/group selection templates, Page 241.

Search for Record/Group Selection Templates in Seagate Crystal Reports online Help.

DETERMINING WHAT FIELD(S) TO USE

When you select records, you are basing your report only on those records that meet some conditions that you have set. You base those conditions on what kind of information you want in the finished report.

Record and Group Selection

229

Interaction of

the Select Expert

and the Selection

Formula Editor

Assume, for example, that you want a report that only shows California data. The challenge is to find the best way to identify those records that come from California.

●If the table used in a report has a State field or a Region field, you can specify in your request that the program use only those records in which the value in the state field is equal to California (Region is equal to CA). This is clearly the easiest way.

●If the table does not have a State field and you still want to report only on California data, there may be another way to identify that data.

¾If the table has a Postal Code field, you could base your record selection on the range of ZIP codes that apply to California (Postal Code is between n and N).

¾If the table has an Area Code field, you could base your record selection on California Area Codes (Area Code is one of x, y,....z).

NOTE: If the Area Code is stored in the telephone number field, you will not be able to do this same record selection using the Select Expert based on the Area Code. You will have to create a record selection formula using the formula language to extract the Area Code part of the phone number and then do record selection based on that. Search for Subscript in Seagate Crystal Reports online Help.

Clearly you are not locked into any one method of record selection. However, you should still use caution when you set up your selection criteria. For a list of details to consider, see Selection performance tips, Page 236.

NOTE: As a general rule, if you can base your record selection on a number of fields (as in this example), you should select an indexed field instead of a field that is not indexed. See Indexed tables, Page 511.

The Select Expert and the Selection Formula Editor are interactive. That is, record selection criteria you enter via the Select Expert automatically generates a record selection formula which can be reviewed and modified using the Show Formula button in either the Select Expert or the Record Selection Formula Editor. Likewise, record selection formulas and modifications to existing record selection formulas automatically update the selection criteria in the Select Expert.

Because of this interactivity, you can use the two facilities together as a tutorial for learning the formula language.

1 Use the Select Expert to set up the selection criteria.

230	Seagate Crystal Reports User’s Guide

2Click the SHOW FORMULA button and the Select Expert expands so you can review the formula the program generated based on your criteria.

3Click the HIDE FORMULA button when you are done with your review.

4Change your selection formula using the Select Expert.

5Review the updated formula by clicking the SHOW FORMULA button again.

6As you gain confidence and decide to make changes using the formula language, click the FORMULA EDITOR button in the expanded Select Expert and use the tools in the Record Selection Formula Editor to make your formula changes.

7Review the results of those changes in the Select Expert. Select each field used in the record selection formula and observe how the program translates your formula into Expert selection criteria.

NOTE: Selection formula components that do not fit any of the fixed criteria in the Select Expert will not be translated. For example, if part of your record selection formula extracts the last four characters in a customer number, then the section of the formula code that performs that extraction will not be converted to Select Expert selection criteria. The Select Expert has no facility to perform such an extraction simply by pointing and clicking.

Group selection

When you group or summarize data, all the groups in the report are included by default. There may be times, however, when you do not want to include all the groups. For example:

●You might want to see only those groups that have certain group names, or whose summarized values meet a certain condition.

●You might want to see only the groups with the highest summary values, or the lowest.

You can select the groups that appear in the report in several different ways.

You can do some selections by using either a record selection formula or a group selection formula. For example:

●If you have a mailing list grouped by Region and your record selection formula specifies only California customers ({customer.REGION} = “CA”), your report will have only a single group: California.

Record and Group Selection

231

Select Expert

Formula language

Top N

●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. However, if the group selection method is used, it could take longer to get the report back.

You can select groups of records using the Select Expert in the same way that you can select individual records.

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

●If you have 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 on either the group name field or the summary field. For example:

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

NOTE: The Select Expert can be used to set up record selection and group selection requests. When a group name or summary field is selected, 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 also select groups using the formula language. To do this, 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 with record selection formulas, your only restriction is that the formula you create must be Boolean; that is, it must return either a True or False value. See

Introduction to Formulas, Page 291.

At times, you might want to show only the “top” or “bottom” groups in a report: the fastest selling product lines, the least productive sales regions, the states that generate the most orders, etc. Because this kind of group selection is so popular, the program includes the Top N Sort Group Expert for setting it up easily.

232	Seagate Crystal Reports User’s Guide

You access the Top N Sort Group Expert by clicking the TOP N button on the supplementary toolbar. Use this Expert to specify first whether you want to display the Top N or Bottom N groups and then specify which number N is to represent.

For example:

●To report on the three fastest selling product lines, select the top N option in the Top N Sort Group Expert and set N to be equal to three.

●To report on the five least productive sales regions, select the bottom N option in the Top N Sort Group Expert and set N to be equal to five.

The program will display those groups as specified.

But there is one other element to consider when setting up Top N group selection and that is what to do with all the records from other groups that do not fit the Top N or Bottom N criteria you have set. You need to decide whether to eliminate those records from your report entirely or to lump them all together into a single group. The program enables you to do choose either option. See How to select the top or bottom N groups, Page 242.

Record selection formula templates

Formula templates

Record selection templates

The following sample formulas can be used as templates to help you create your own selection formulas using the Record Selection Formula Editor. The examples illustrate different selections that you could do, not necessarily the best selections. To help identify the best way to set up your record selection, see Selection performance tips, Page 236.

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])

Record and Group Selection

233

«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 in which the value in the {file.FIELD} field is greater than 99999.»

{file.FIELD} < 99999

«Selects those records in which the value in the {file.FIELD} field is less than 99999.»

Range of values

{file.FIELD} > 11111 and {file.FIELD} < 99999

«Selects those records in which the value in the {file.FIELD} field is greater than 11111 but less than 99999 (neither 11111 or 99999 is included in the range of values).»

{file.FIELD} >= 11111 and {file.FIELD} <= 99999

«Selects those records in which the value in the {file.FIELD} field is greater than 11111 but less than 99999 (both 11111 and 99999 are included in the range of values).»

FOR SELECTING RECORDS USING DATES

The Month, Day, and Year functions can all be used in examples like the following:

234	Seagate Crystal Reports User’s Guide

Year ({file.DATE}) < 1996

«Selects those records in which the year found in the {file.DATE} field is earlier than 1996.»

Year ({file.DATE}) > 1992 and

Year ({file.DATE}) < 1996

«Selects those records in which the year found in the {file.DATE} field falls between 1992 and 1996 (1992 and 1996 not included).»

Year({file.DATE}) >= 1992 and Year({file.DATE}) <= 1996

«Selects those records in which the year found in the {file.DATE} field falls between 1992 and 1996 (1992 and 1999 are included).»

Month({file.DATE}) in 1 to 4

«Selects those records in which the month found in the {file.DATE} field is one of the first four months of the year (includes January, February, March, and April).»

Month({file.DATE}) in [1,4]

«Selects those records in which the month found in the {file.DATE} field is the first or fourth month of the year (includes January and April, excludes February and March).»

SELECTING RECORDS USING PRESET DATE RANGES

The preset date ranges can be used to create selection formulas similar to these:

{file.DATE} in LastFullMonth

«Selects those records in which the date found in the {file.DATE} field falls within the last full month. (If the month is May, this selects all records with an April date.)»

not({file.DATE} in LastFullMonth)

«Selects all records except those in which the date found in the {file.DATE} field falls within the last full month. (If the month is May, this selects all records except those with an April date.)»

{file.DATE} < Today

«Selects all records in which the date found in the {file.DATE} field falls before today's date.»

Record and Group Selection

235

Selection performance tips

SELECTING RECORDS USING DATE/NUMBER/CHARACTER COMBINATIONS

These formulas simply “mix and match” formulas from the categories above.

“C” in {file.FIELD}[1] and Month({file.DATE}) in [1,4]

«Selects those records in which the value in the {file.FIELD} field begins with “C” and the month is either January or April. For example, if this kind of formula was used with an order database, you could be asking for a report showing all customers whose names begin with “C” and who placed orders in January or in April.»

“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.»

These templates can be used as they are (with your own data), or they can be combined to create complex formulas. You can also 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 511.

●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.

●If at all possible, avoid performing record selection based on formula fields, since 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 the formula (@ExtendedPrice > 1000), the SQL server will not understand the formula and 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.

236	Seagate Crystal Reports User’s Guide

Record selection with a group selection formula

● Try to avoid subscript ranges such as:

{file.FIELD}[1 to 5]

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. However, 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.

●If you are using SQL/ODBC data sources and you are unsure whether or not the record selection is passing through to SQL, 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 the 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 possibility that your driver may use slightly different syntax. This may also be the case for non-SQL 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 and so SQL pass through will not occur. Search for ToText in Seagate Crystal Reports online Help.

To perform record selection on both indexed and non-indexed fields, you can set up your record selection in such a way as to maximize performance. You do this by creating a record selection formula and a group selection formula, and using them both to select records. This may require some explanation.

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 also be used to set up record selection.

While the two Formula Editors are fundamentally the same and the formulas they produce appear the same, they each produce formulas that are evaluated at different times.

Record and Group Selection

237