Release 10.1A: OpenEdge Reporting:
Query/Results for Windows

> ## Creating calculated fields

> You can create calculated fields from within any query view except the Labels view. See Table 6–2 for a list of the views in which individual calculated fields are available.

> | To create a calculated field:

> 1. Choose Field Add Calculated Field, then select the field you want to create.

> Results displays the Add Field dialog box for the type of calculated field you selected. The following sections describe these dialog boxes.

> 2. Use the Add Field dialog box to specify the parameters for the field, then choose OK. The Properties dialog box appears.

> For example, you can choose Assistant to modify how the data in the field is displayed. You can also change the name of a calculated field by selecting Rename. See the "Renaming calculated fields" section for more information.

> 3. Set the properties for the field you created. See the "Changing display field properties" section for more information.

> ##### Adding a Percent of Total field

> The Add Field - Percent of Total dialog box lets you specify the field for which you want to calculate the percent of total.

> | To specify a field, select the field and choose OK:

> For example, Figure 6–4 uses Percent of Total to calculate each sales representative’s percent of total for the month of January.

> Figure 6–4: Percent of Total calculation example

> ##### Adding a Running Total field

> The Add Field - Running Total dialog box lets you select the field for which you want to calculate a running total.

> | To specify a field, select the field and choose OK:

> Figure 6–5 lists the running total for the Total-Paid field.

> Figure 6–5: Total-Paid field example

> ##### Adding a Counter

> The Add Field - Counter dialog box lets you specify the parameters for the Counter field:

> | To create the Counter field, set the following parameters:

> - Starting number — Specifies the number from which to begin counting.

> - Number to add — Specifies the increment between the numbers.

> - Sections used in — Select the sections you want to count: Master, Detail, or both. See "Using Master and Detail sections to display records" section for more information about Master and Detail sections.

> The example presented in Figure 6–6 uses the Counter field to count the total number of orders in the Master section.

> Figure 6–6: Counter field example

> ##### Adding a Stacked Array field

> When a field consists of several elements, each of which contains its own value, the field is called an array. By default, the values in the array are listed horizontally. To list them in a vertical column, use the Stacked Array Calculated field.

> The Add Field - Stacked Array dialog box lets you specify the array you want to stack vertically.

> | To specify the array, select the array and choose OK:

> In Figure 6–7, the Stacked Array field displays the contents for Month-Quota in a vertical column.

> Figure 6–7: Stacked Array field example

> ##### Adding a Lookup field

> The Add Field - Lookup Source dialog box allows you to display the value of a field in a table not currently in the query.

> To use this option, both the table that contains the field you want to display and one of the tables in your query must contain a field that stores the same kind of information. This similarity between the two fields lets you establish a relationship between the two tables.

> As shown in Figure 6–8, you might use a field in the Customer table called Cust-Num and a field in the Invoice table called ID-Num to identify your customers. In this case, they both store a set of numbers that identify your customers.

> Figure 6–8: Adding a Lookup field example

> See the "Record displays in multi-table queries" section and the "Changing table relationships in multi-table queries" section for more information about table relationships.

> | To look up a field in a table not included in the query:

> 1. Select a field from one of the tables in your query that contains the same type of information as a field in the table with which you are establishing a relationship.

> 2. Choose OK. The Add Field - Lookup Matching Field dialog box appears:

> 3. Select the table that contains the field you want to look up.

> 4. Select a field that contains the same type of information as the field you selected in Step 1. This establishes a relationship between the two tables.

> 5. Choose OK. The Add Field - Lookup Display Field dialog box appears:

> 6. Select the field that contains the information you want to display.

> 7. Type a value to display in place of the lookup field when Results cannot find a matching field value in one or more records. By default, Results displays a question mark (?) when it cannot find a value.

> 8. Choose OK.

> The following example displays a lookup field called Region from a table called State that is not included in the query:

> ##### Adding a Math expression

> The Add Field - Math dialog box allows you to create a math expression based on the value of two fields. When you select the Math option, Results displays the Add Field - Math dialog box:

> | To create a Math expression:

> 1. Select the mathematical type of function to perform. Results lists the available display fields in the Function/Field selection list and displays an empty expression based on the function you choose:

> 2. Double click one of the following to create the first part of the expression:

> - A field — Uses the value of the field in the first part of the expression.
> - Constant value — Lets you enter a value to use in the first part of the expression. For example, you can use constant value field to insert a number to calculate within the expression.
> - Subexpression — Lets you enter a subexpression as the first value in the expression. A subexpression is an expression within another expression.

> Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.

> 3. If the expression contains a second value, double click a field, constant value, or subexpression to create the last part of the expression.

> Results creates the remaining part of the expression and displays the entire expression in the Expression selection list:

> 4. Choose OK to save the expression.

> The following example contains a calculated field named Total Price that multiplies the value of the Price and Qty fields for each record in the query:

> ##### Adding a String Function

> The Add Field - String Function dialog box allows you to create a string expression:

> | To create a string expression:

> 1. In the Function/Field selection list, double click the function you want to perform. Table 6–3 describes these functions.

> Table 6–3: String functions
> Function | Description
> --- | ---
> Combine strings | Combines two or more strings into one string.
> Current time | Returns the current time.
> Display as time | Displays a string in time format.
> Greater of two strings | Returns the greater (larger) of two strings.
> If first expression is true return second else third string | Defines an expression that makes a decision based on string values. If the first string expression is true, the second expression is returned. If the first string expression is false, the third expression is returned.
> Length of string | Returns the number of characters in a string. For example, the length of the string “Progress” is 8.
> Lesser of two strings | Returns the lesser (smaller) of two strings.
> Name of month | Returns the current month.
> Name of weekday | Returns the current day.
> String constant or field | Lets you to build an expression with either a string constant or the value of a character field.
> Substring | Returns a substring. A substring is part of a string. For example, “hold” is a substring of “credit hold.”
> User ID | Returns the user ID.

> Results does the following:

> - Lists the available display fields in the Function/Field selection list.
> - Displays an empty expression based on the function you chose in Step 1.
> 2. Double click one of the following to create the first part of the expression:

> - A field — Uses the value of the field in the first part of the expression.
> - Constant value — Lets you enter a value to use in the first part of the expression. For example, you could use constant value to insert a comma or a space within a string.
> - Subexpression — Lets you enter a subexpression as the first value in the expression. A subexpression is simply an expression within another expression.

> Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.

> 3. If your expression contains a second value, double click a field, constant value, or subexpression to create the last part of the expression.

> Results creates the remaining part of the expression and displays the entire expression in the Expression field:

> 4. Choose OK to save the expression.

> The following example shows the results. It contains a calculated field named City/State that uses the Combine String function to combine the values of the City and State fields into one field:

> Note that the City/State field uses a constant value that contains a comma and space to separate the city and state. This constant value is added to the expression through a subexpression. Figure 6–9 illustrates the expression used in this example.

> Figure 6–9: Expression example

> ##### Adding a Numeric Function

> Use the Numeric Function to create a numeric expression. When you select the Numeric Function option, Results displays the Add Field - Numeric Function dialog box:

> | To create a numeric expression:

> 1. In the Function/Field selection list, double click the function you want to perform. Table 6–4 describes these functions.

> Table 6–4: Numeric functions
> Function | Description
> --- | ---
> Absolute value | Returns the absolute value of a number. The absolute value is the positive value of a given number. For example, the absolute value of -3 is 3; the absolute value of 5 is 5.
> Cube root | Returns the cube root of a number. The cube root of a number is a number that when multiplied by itself three times, equals the number. For example, the cube root of 8 is 2 (2*2*2).
> Greater of two numbers | Returns the largest number from a list of numbers. For example, if a list consists of two numbers, 24 and 55, the function returns 55.
> If first expression is true return second else third number | Defines an expression that makes a decision. If the first expression is true, the second expression is returned. If the first expression is false, the third expression is returned. For example, you can define an expression that calculates a discount based on the credit limit of a customer.
> Log base e | Returns the logarithm based on the value e. The value of e is 2.7182818.
> Log base n | Returns the natural logarithm for a number for a given base. For example, 25 is 32. The natural logarithm for 32 with base 2 is 5.
> Numeric constant or field | Defines a field’s value
> Remainder | Returns the remainder of a division operation.
> Round | Rounds a value to the nearest whole number. For example, rounding 5.2 returns 5 and rounding 5.7 returns 6.
> Smaller of two numbers | Returns the smaller of two numbers. For example, if the function compares the numbers 18 and 29, it returns the number 18.
> Square root | Returns the square root of a number. For example, the square root of 9 is 3.
> Truncate | Truncates a number.

> Results does the following:

> - Lists the available display fields in the Function/Field selection list.
> - Displays an empty expression based on the function you chose in Step 1 within the Expression scroll box.
> 2. Double click one of the following to create the first part of the expression:

> - A field — Uses the value of the field in the first part of the expression.
> - Constant value — Lets you enter a value to use in the first part of the expression. For example, you can enter a number to calculate within the expression.
> - Subexpression — Lets you enter a subexpression as the first value in the expression. A subexpression is simply an expression within an expression.

> Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.

> 3. If the expression contains a second value, double click a field, constant value, or subexpression to create the last part of the expression.

> Results creates the remaining part of the expression and displays the entire expression in the Expression field:

> 4. Choose OK to save your expression.

> Figure 6–10 contains a calculated field named Round Amount that rounds the amount of each customer’s invoice to the nearest whole dollar.

> Figure 6–10: Round Amount field example

> ##### Adding a Date Function

> The Add Field - Date Function dialog box, shown in Figure 6–11, allows you to define a date calculation.

> Figure 6–11: Add Field - Date Function dialog box

> | To add a Date Function:

> 1. In the Function/Field selection list, double click the function you want to perform. Table 6–5 describes these functions.

> Table 6–5: Date functions
> Function | Description
> --- | ---
> Add days to date value | Adds the number of days you specify to a date and returns the value.
> Current date | Returns the current date.
> Day of month | Returns the day of the month for the date you specify.
> Day of week | Returns the day of the week for the date you specify in numeric format, where the value “1" begins with Sunday.
> Difference between two dates | Returns the difference between two dates.
> Earlier of two dates | Returns the earlier of two dates. For example, if the function compares the dates 01/05/95 and 01/09/99, it returns 01/05/95.
> If first expression is true return second else third date | Defines an expression that makes a decision. If the first part of the expression is true, the function returns the second date; if the first part of the expression is not true, the function returns the third date.
> Later of two dates | Returns the later of two dates. For example, if the function compares the dates 01/05/95 and 01/09/99, it returns 01/09/99.
> Month of year | Returns the number of the month for the date you specify.
> Subtract days from date value | Subtracts the number of days you specify from a date and returns the value.
> Year value | Returns the year for the date you specify. For example, the date 04/05/99 is returned as 1999.

> Results does the following:

> - Lists the available display fields in the Function/Field selection list.
> - Displays an empty expression based on the function you chose in Step 1 within the Expression editor box:

> 2. Double click one of the following to create the first part of the expression:

> - A field — Uses the value of the field in the first part of the expression.
> - Constant value — Lets you enter a date to use in the first part of the expression.
> - Current date — Uses the current date each time you run the query.
> - Subexpression — Lets you enter subexpression as the first value in the expression.

> Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.

> 3. Double click a field, constant value, current date, or subexpression to create the last part of the expression.

> Results creates the remaining part of the expression and displays the entire expression in the Expression field:

> 4. Choose OK to save your expression.

> The following example contains a calculated field named Delivery Time that uses the Differences Between Two Dates function to calculate the difference between the Ordered and Shipped dates. Note that a question mark displays in the Delivery Time field for those records that Results cannot calculate because the order has not shipped:

> ##### Adding a Logical Function

> The Add Field - Logical Function dialog box allows you to create a logical expression:

> | To create a logical expression:

> 1. In the Function/Field selection list, double click the function you want to perform. Table 6–6 describes these functions.

> Note: Many of the functions in Table 6–6 contain comparison operators. See Appendix B "Comparison Operators."

> Table 6–6: Logical functions
> Function | Description
> --- | ---
> Begins | Returns the logical value “yes” for strings that begin with the letter you specify or the logical value “no” for strings that do not begin with the letter you specify.
> Dates equal | Returns the logical value “yes” for dates equal to the value you specify or the logical value “no” for dates not equal to the value you specify.
> Dates greater or equal | Returns the logical value “yes” for dates larger or equal to the value you specify or the logical value “no” for dates smaller than the value you specify.
> Dates greater than | Returns the logical value “yes” for dates larger than the value you specify or the logical value “no” for dates smaller or equal to the value you specify.
> Dates less or equal | Returns the logical value “yes” for dates smaller or equal to the value you specify or the logical value “no” for dates larger than the value you specify.
> Dates less than | Returns the logical value “yes” for dates smaller than the value you specify or the logical value “no” for dates larger or equal to the value you specify.
> Dates not equal | Returns the logical value “yes” for dates not equal to the value you specify or the logical value “no” for dates equal to the value you specify.
> Logical AND | Lets you join two logical expressions together. The function returns the value “yes” when both expressions are true and the value “no” when either one or both expressions are false.
> Logical OR | Lets you join two logical expressions together. The function returns the value “yes” when either expression is true and the value “no” when neither expression is true.
> Matches | Returns the logical value “yes” for strings that match the characters you specify or the value “no” for strings that do not match the characters you specify.
> Negate logical expression (NOT) | Returns the logical value “no” for logical expressions that are true and the logical value “yes” for logical expressions that are false.
> Numbers equal | Returns the logical value “yes” for numbers equal to the value you specify or the logical value “no” for numbers not equal to the value you specify.
> Numbers greater or equal | Returns the logical value “yes” for numbers larger or equal to the value you specify or the logical value “no” for numbers smaller than the value you specify.
> Numbers greater than | Returns the logical value “yes” for numbers larger than the value you specify or the logical value “no” for numbers smaller or equal to the value you specify.
> Numbers less or equal | Returns the logical value “yes” for numbers smaller or equal to the value you specify or the logical value “no” for numbers larger than the value you specify.
> Numbers less than | Returns the logical value “yes” for numbers smaller than the value you specify or the logical value “no” for numbers larger or equal to the value you specify.
> Numbers not equal | Returns the logical value “yes” for numbers not equal to the value you specify or the logical value “no” for numbers equal to the value you specify.
> Strings equal | Returns the logical value “yes” for strings equal to the value you specify or the logical value “no” for strings not equal to the value you specify.
> Strings greater or equal | Returns the logical value “yes” for strings larger or equal to the value you specify or the logical value “no” for strings smaller than the value you specify.
> Strings greater than | Returns the logical value “yes” for strings larger than the value you specify or the logical value “no” for strings smaller or equal to the value you specify.
> Strings less or equal | Returns the logical value “yes” for strings smaller or equal to the value you specify or the logical value “no” for strings larger than the value you specify.
> Strings less than | Returns the logical value “yes” for strings smaller than the value you specify or the logical value “no” for strings larger or equal to the value you specify.
> Strings not equal | Returns the logical value “yes” for strings not equal to the value you specify or the logical value “no” for strings equal to the value you specify.

> 2. Double click one of the following to create the first part of the expression:

> - A field — Uses the value of the field in the first part of the expression.
> - Constant value — Lets you enter a date to use in the first part of the expression.
> - Current date — Uses the current date each time you run the query.
> - Subexpression — Lets you enter subexpression as the first value in the expression. A subexpression is an expression within an expression.

> Results creates the first part of the expression and displays it in the Expression field at the bottom of the dialog box.

> 3. If your expression contains a second value, double click a field, constant value, current date, or subexpression to create the last part of the expression.

> Results creates the remaining part of the expression and displays the entire expression in the Expression field:

> 4. Choose OK to save your expression.

> 5. By default, Results uses the display values yes and no to represent true and false. To substitute a different set of display values for true and false, choose Assistant in the Properties dialog box and modify the display values. See "Changing display field properties" section for more information.

> The following example contains a calculated field named On Time that uses the Dates Less or Equal function to determine whether the date an order is shipped is equal to or less than the promise date for the order:

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095