Restriction functions

This section describes the functions available when defining a restriction for a report. It includes a definition of each function, an example of how the function is used in a restriction expression, and an explanation of how you can use such an expression in a report. Also included is information about the storage types that can be used with each function, since some functions can be used only in expressions with fields of a certain storage type.

Most of these functions can be used only with fields from a report table that has a one-to-many relationship with another report table, such as a secondary table with a one-to-many relationship with the report’s main table. Records in secondary tables with a one-to-many relationship with the main table are sometimes referred to as detail records. Using one of these functions with any other fields will cause an error at runtime. Only two functions can be use with fields from any of the report tables: SUBSTRING and WILDCARD.

AVERAGE

The AVERAGE function calculates the average of an expression for all detail records. It can be used with expressions containing fields of all storage types except strings.

Example

You can use the AVERAGE function to create a restriction on an invoice list so that it will print only invoices for which the invoice’s items average a markup of at least 20% over cost.

AVERAGE ((Item Price) – (Item Cost) / (Item Cost)) > .2

EXISTS

The EXISTS function ascertains whether at least one record in a group of detail records makes the expression true. The expression the EXISTS function evaluates should be a comparison as shown in the following example. This function can be used with all boolean expressions.

You can use the NOT operator with this function to increase its scope and create expressions restricting the information on your reports according to expressions that aren’t true.

Example

You can use the EXISTS function to create a restriction on an order list to list customers whose orders include at least one item over $100.

EXISTS (Item Price > 100)

FIRST

The FIRST function finds the first occurrence of an expression in a group of detail records. It can be used with expressions containing fields of any storage type.

Restrictions are evaluated before the report is sorted, so the item designated by the FIRST function may not be the same as the first item in a group on the report once it’s been sorted and printed. The first item will be the first value in the table, sorted by the key used to link the secondary table to the report’s main table.

For example, you could print a report that lists invoices and corresponding line items, then create a restriction to print only invoices for which the first line item’s price was more than $1,000. If you then sort the report in ascending order by line item price, the line item that the FIRST function finds won’t necessarily be the line item listed first on the report. It will be the first line item in the table, which depends on the key used to create the table relationship. If the key is composed of the invoice number and the line item number, the first line item will be the item with the smallest (or largest, depending upon the key’s sorting method) line item number.

The following example shows an invoice and its line items as they’re sorted within the tables, by invoice number and by item number.

Since the line items are sorted by item and not by price, the record identified by the FIRST function is item 1201. If you sort the report by the price of each line item, that’s how the information will appear on the report, but the FIRST function will still identify item 1201 as the first item.

Example

The following example shows how to use the function to create the expression explained in the previous paragraphs and illustrations.

FIRST (Item) > 1000

FORALL

The FORALL function prints only those records for which all of the detail records meet the expression’s requirements. This function can be used with all boolean expressions.

You can use the NOT operator with this function to increase its scope and create expressions restricting the information on your reports according to expressions that aren’t true.

Example

You can use the FORALL function to create a restriction on an invoice list to display only invoices containing items that all cost more than $500.

FORALL ((Item Price) > 500)

FREQUENCY

The FREQUENCY function counts the number of detail records. The FREQUENCY function can be used with expressions containing fields of any storage type.

Example

You can create a restriction on a customer order list to print only orders containing more than five items.

FREQUENCY (Item Number) > 5

LAST

The LAST function finds the last occurrence of an expression in a group of detail records. The LAST function can be used with expressions containing fields of any storage type except string.

Restrictions are evaluated before the report is sorted, so the item designated by the LAST function may not be the same as the last item in a group on the report once it’s been sorted and printed. The last item will be the last value in the table, sorted by the key used to link the secondary table to the report’s main table.

For example, you could print a report that lists Salesperson IDs and corresponding line items containing dates upon which they’ve made sales and the total amount of each sale. You could then create a restriction to print only the records for salespeople whose most recent sale totaled more than $1,000. If you then sort the report in ascending order by the amount of the sale, the record that the LAST function finds won’t necessarily be the one listed last on the report. The last record on the report will be the item with the largest total sale amount.

The following example shows the records for Salesperson ID 001 as they’re sorted within the tables, by salesperson ID and sale date.

Since the salesperson records are sorted by sale date and not by sale total, the record identified by the LAST function is the 4/12/97 sale. If you sort the report by the sale total, that is how the information will appear on the report, but the LAST function will still identify the 4/12/97 sale as the last item.

Example

The following example shows how to use the function to create the expression explained in the previous paragraphs and illustrations.

LAST (Sale Total) > 1000

MAXIMUM

The MAXIMUM function finds the detail record with the highest value that satisfies the given expression. The MAXIMUM function can be used with fields of any storage type except strings.

Example

You could create a restriction on an invoice list that prints only invoices that include items priced over $100.

MAXIMUM (Item Price) > 100

MINIMUM

The MINIMUM function finds the detail record with the lowest value that satisfies the given expression. The MINIMUM function can be used with fields of any storage type except strings.

Example

You could create a restriction on an invoice list that prints only invoices that include items priced under $100.

MINIMUM (Item Price) < 100

SUBSTRING

The SUBSTRING function is used to search fields with a string storage type for a match to a specified pattern. The SUBSTRING function will find a match if the specified pattern appears anywhere within the specified field. It is case-sensitive.

Only the equality (=) and inequality (<>) operators can be used with the SUBSTRING function. The data in the specified field either matches or doesn’t match the pattern. No other operators can be used.

This function is used to restrict the data on the report to information that matches a set of characters specified as the search pattern. Three special characters can be used in the search pattern to indicate the type of match you’re searching for: the asterisk (*), the question mark (?) and the backslash (\), as indicated in the following table.

The SUBSTRING function implicitly adds an asterisk to the beginning and end of the pattern you specify, so that a match will be found even if the pattern occurs within the information in the field. For example, the SUBSTRING function would find a match for the pattern P*L in the word APPLE, while the WILDCARD function wouldn’t.

The backslash is used in front of special characters, such as quotation marks, that have specific meaning in Report Writer. A quotation mark indicates the beginning or end of a field. If you wish to search for fields containing, among other characters, a quotation mark, you’ll need to place a backslash in front of it to indicate that the quotation mark is simply a character, and not the sign for the end of the field. Most symbols and non-alphanumeric characters are special characters and should be used with backslashes.

Example

You could use this function to print a list of companies in a particular sales region whose names contain the string John’s. The following example shows an expression for which “John’s Fish Market” and “Mabel and John’s Deli” would both be valid matches.

SUBSTRING ("John\'s") = Company_Name

SUMMATION

The SUMMATION function calculates the sum of a specified expression for each record in a set of detail records. The SUMMATION function can be used with fields of any storage type except strings.

Example

You can use the SUMMATION function on a report listing invoices and the items on each invoice to print only the invoices where the total of all item extended prices is greater than $1,000. The extended price is the price of each item multiplied by the number of items that were purchased. The SUMMATION function would first calculate the extended price for each item on the invoice, then sum those extended prices and compare the sum to the specified $1,000 value. The following expression demonstrates how this restriction would be written.

SUMMATION (Quantity Ordered * Item Price) > 1,000

WILDCARD

The WILDCARD function is used to search fields with a string storage type for a match to a specified pattern. The WILDCARD function will find a match only if the specified pattern appears in the specified field exactly as stated. The asterisk, question mark and backslash special characters, described in the explanation of the SUBSTRING function, can also be used with this function.

By using these special characters, you can expand the scope of the search conducted by the WILDCARD function. Without the special characters, the specified field must contain exactly the specified pattern, and only the specified pattern, for a match to occur. For example, the expression WILDCARD (Apple) = Snack Type would not find a match if the value in the Snack Type field was Apples.

Only the equality (=) and inequality (<>) operators can be used with the WILDCARD function. The data in the specified field either matches or doesn’t match the pattern. No other operators can be used.

The WILDCARD function is case-sensitive.

Example

You could use this function to print a list of users whose IDs begin with the letter A.

WILDCARD ("A*") = User ID

Documentation Feedback

---