Selection Formulas

book list add book to my bookshelf create a bookmark purchase this book online

mastering crystal reports 9
Chapter 7 - Selectively Refining a Report Using Data Values
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

In a report, the Database Expert lets you choose the data tables and the Field Explorer helps you pick the fields to place in your report. If you were using a structured query language (SQL) command to retrieve the data, these two pieces would comprise the SELECT fields FROM tables part of the SQL statement. For instance, a VistaNations report that selects the resort code, its state or province, its purchase price, its country, and its purchase date would generate a SQL statement like this behind the scenes to send to the database:

SELECT Resorts.ResortCode,Resorts.StateProvince,  Owners.PurchasePrice,Resorts.Country, Owners.PurchaseDat FROM Resorts INNER JOIN Owners ON Resorts.ResortCode=Owners.ResortCode

This type of statement retrieves all the rows in the two tables that match on the ResortCode field. How would you go about creating a statement that restricted the selection of data to only those records where the resort was located in Canada or the United States? We have no way to do this with the Database Expert and the Field Explorer; we need something more. We need the Select Expert.

The Select Expert

The job of the Select Expert is to generate the WHERE clause in a complex SELECT statement. The WHERE clause is ideally passed to the database for processing as a part of the SELECT statement. There are cases when it is not possible to pass the WHERE clause to the database, specifically, when Crystal formulas are used as part of the WHERE clause.

To retrieve only records for resorts located in Canada or the United States, here’s the type of SQL that needs to be generated:

 SELECT Resorts.ResortCode,Resorts.StateProvince,  Owners.PurchasePrice,Resorts.Country, Owners.PurchaseDat FROM Resorts INNER JOIN Owners ON Resorts.ResortCode=Owners.ResortCode WHERE     (Resorts.Country="CN" or Resorts.Country="US")

To open the Select Expert, choose Report > Select Expert or click the Select Expert icon on the toolbar. Either method opens the dialog shown in Figure 7.1.

click to expand
Figure 7.1. The Select Expert

To begin, you choose the field that will be used to compare against a value. To generate the SQL we have above, the field to choose is the Country field in the Resorts table. With the field chosen, the Select Expert displays a dialog to help you choose the comparison operation, which is presented in the drop-down list shown in Figure 7.2. Table 7.1 describes the comparison operations and describes how the WHERE clause would be constructed for each operator.

click to expand
Figure 7.2. Comparison operators

Table 7.1: Select Expert Comparison Operators

Comparison Operator

WHERE Clause Construction

is any value

Does not generate a WHERE clause.

is equal to

WHERE field = value

is not equal to

WHERE field <> value

is one of

WHERE (field in [ value1, value2, …])

is not one of

WHERE not (field in [ value1, value2, …])

is less than

WHERE field < value

 

WHERE field <= value

is greater than

WHERE field > value

 

WHERE field >= value

is between

WHERE (field >= value1 AND field <= value2)

is not between

WHERE NOT (field >= value1 AND field <= value2)

starts with

WHERE field LIKE 'C*'  Here, C is any character or sequence of characters, and the asterisk is a wild card character matching against any characters that follow.

does not start with

WHERE field NOT LIKE 'C*' Here, C is any character or sequence of characters, and the asterisk is a wild card character matching against any characters that follow.

is like

WHERE field LIKE 'C?CC*' Here, C is any character, the ? is a wild card to match on any single character, and the asterisk is a wild card character matching against any characters that follow.

is not like

WHERE field NOT LIKE 'C?CC*' Here, C is any character, the ? is a wild card to match on any single character, and the asterisk is a wild card character matching against any characters that follow.

formula

Any valid Crystal formula that compares a field to a value and returns a discrete result.

Warning 

If the Formula option is chosen, a Crystal formula can be coded in place of the WHERE clause. However, this may negatively affect Crystal’s performance for any formula that can’t be passed to the database for equivalent processing. A Crystal formula can be evaluated only within the Crystal environment, meaning that all data will be retrieved from the database and processed locally in Crystal by the formula. It is generally a better idea to process complex WHERE clauses in the database environment and return a subset of data to Crystal rather than return all the records and have Crystal do the processing.

To limit the records in a VistaNations report to the countries of Canada and the United States, the is one of operation is ideal. The is one of operation expects you to choose the values that it should allow for the report. To do this, use the drop-down arrow to the right of the is one of drop-down box or click the Browse button. Each time you choose a value in the list box, it is added to the area below the list box. Figure 7.3 shows the inclusion of CN and US for Canada and the United States, respectively.

click to expand
Figure 7.3. An is one of selection

You can also type values directly in the value area of the drop-down box and then click the Add button; the Add button is active only when you’ve typed a new value in the drop-down box. However, using the drop-down list to choose the values is preferred since it helps to eliminate typing errors and the occurrence of those not-quite-right values. For instance, if you typed Canada as the value, it would not match against anything in the database since the value is actually stored only as CN.

Note 

You can remove a value from the list after you add it by selecting it and clicking the Delete key on the keyboard or using the Remove button to the left of the value list. The Remove button becomes available only when a value is selected.

Data Type Considerations

The type of data available in the value drop-down box is determined by the data type of the field represented by the tab name. The example above is a record selection based on a text field. If the data is numeric or date/time, both the operations and the value choices are different. The starts with, does not start with, is like, and is not like options are specific to text data and therefore do not appear as options when numeric fields or date/time fields are being used in the condition.

Tip 

Crystal uses an internal format for its date and time values. When you type a date as a value rather than picking it from the drop-down list, surround it with # signs to force Crystal to convert it properly to its internal format. For instance, type #9/3/1985# to allow the value you supply to be converted to a Crystal date. You could also use the built-in Date function for the same purpose using the format Date(1985,9,3).

is in the period is not in the period

WeekToDateFromSun

MonthToDate

YearToDate

Last7Days

Last4WeeksToSun

LastFullWeek

LastFullMonth

AllDatesToToday

AllDatesToYesterday

AllDatesFromToday

AllDatesFromTomorrow

Aged0To30Days

Aged31to60Days

Aged61to90Days

Over90Days

Next30Days

Next31To60Days

Next91to365Days

Calendar1stQtr

Calendar2ndQtr

Calendar3rdQtr

Calendar4thQtr

Calendar1stHalf

Calendar2ndHalf

LastYearMTD

LastYearYTD

 

Note 

Crystal Reports is bundled with many of the top-selling accounting systems, and the standard date range selection criteria are used to create financial reports and aging reports.

Formula Editor

Using the drop-down boxes in the Select Expert generates a formula representing the Crystal equivalent of the WHERE clause condition(s). The Show Formula button visible at the bottom of Figure 7.3 opens an editing window where you can directly type a formula or edit the formula created by the Select Expert. From within the editing window, a Formula Editor button provides direct access to the Formula Workshop and all of Crystal’s built-in functions and operators. When the formula is showing, the Show Formula button disappears and is replaced by a Hide Formula button so that you can toggle between the two.

Tip 

Changes made to the selection criteria directly in the Formula Editor go into effect after the settings have been saved by closing the Select Expert.

Multiple Conditions

A SQL WHERE clause can contain multiple conditions. For instance, if we wanted to use only records for resorts in Canada and the United States where the resort code starts with the letter C, we would need two conditions in the WHERE clause. The Select Expert has a separate tab for each condition in the WHERE clause. In Figure 7.3, the Country field has its own tab and a second tab is labeled as <New>. To add additional conditions, click the <New> tab or the New button; the current <New> tab moves to the right and a new condition tab slips in to its left. In Figure 7.4, we see that the ResortCode field has been set to allow only resort codes that start with the letter C.

click to expand
Figure 7.4. Additional conditions

For each condition added (meaning each tab added), Crystal performs a logical AND operation to connect one condition to the next. For Figure 7.4, the default behavior of Crystal is to look for a Country code equal to either CN or US AND Resort codes that start with the letter C. There will be cases when you don’t want to do an AND but instead want to code an OR. While Crystal will allow this, the Select Expert is not capable of generating this code, so you’ll need to use the Show Formula button to directly edit the generated code to replace the AND with an OR.

start sidebar
Controlling Precedence and Determining a Result

AND and OR clauses are of equal precedence and therefore calculate in left-to-right order as they appear in a formula. To modify this default behavior, use parentheses around the condition that you want to execute first. Conditions within parentheses can be nested, with innermost sets of parentheses evaluating first and then moving out to the next innermost set of parentheses. Figuring out how a sequence of multiple logical ANDs and ORs will combine and what result will be returned can be tricky. Here are the two rules a computer uses to evaluate pairs of ANDs and ORs:

 (condition AND condition)     returns TRUE if both conditions are TRUE, otherwise returns FALSE (condition OR condition)     returns TRUE if either condition is TRUE, otherwise returns FALSE

end sidebar

Conditions, and therefore the tabs associated with the conditions, can be removed from the WHERE clause using the Delete button.

Viewing the SQL

When a SQL-capable database is used as the data source, you can look at the SELECT clause and the WHERE clause generated by choosing Database > Show SQL Query. Figure 7.5 shows the SQL generated by the Select Expert for records with resorts located in Canada or the United States.

click to expand
Figure 7.5. Generated SQL query

Record Selection

The Select Expert generates a record selection that can be edited directly without using the Select Expert dialog. The Record Selection option is available on the menu by choosing Report > Selection Formulas > Record. This opens the Formula Workshop directly, as shown in Figure 7.6. The formula shown here is the one that was generated from the Select Expert example visible in Figure 7.4. Notice that the drop-down choice of “starts with” has been converted to a Crystal Reports formula keyword “startswith.” This translation was done automatically by Crystal Reports.

click to expand
Figure 7.6. Record selection formula

The complete power of the Formula Workshop, including access to fields, groups, functions, and operators, is available to create a record selection formula. Crystal will generate generic SQL statements based on the Crystal formula coded if possible or pass as much of the SQL to the database as possible, leaving Crystal to process locally only what cannot be processed by the database.

Group Selection

In Chapter 6, “Summarizing Information,” we took a good look at how to create and manage groups. In addition, we introduced group selection formulas as a way to filter groups of data out of a report. To create a group selection formula, a report must first be grouped on a field in the report, a formula, or a field in a database table. With a group created, you can choose Report > Selection Formulas > Group to code a statement that will evaluate to TRUE or FALSE to filter out group data. This opens the Formula Editor, where you can then type any formula that evaluates to a TRUE value. When the value of the formula is TRUE, then the group is included in the report; a value of FALSE eliminates the group from the report. In Figure 7.7, the formula returns a TRUE value if the first letter of the Resorts.Country field is not equal to the letter A. This will eliminate all countries from the report that start with A.

click to expand
Figure 7.7. Coding a group selection formula

The condition statement is all that is needed in the formula edit box; it does not need to be a complete IF-THEN-ELSE statement. When the clause is TRUE, the group is included in the report, and when it’s FALSE, the group is excluded.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement
 
Conello © 2000-2003     Feedback


Mastering Crystal Reports 9
Mastering Crystal Reports 9
ISBN: 0782141730
EAN: 2147483647
Year: 2005
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net