InfoMaker User's Guide
|Part 8 Appendixes|
|Appendix C: InfoMaker Functions|
This appendix provides an introduction to the functions you can use in expressions in InfoMaker and some examples of their use. For information about each function and its syntax, see online Help.
You can use InfoMaker functions in expressions for computed fields, filters, validation rules, and graphed data in the Report painter and the Form painter. Most of the functions can be used in both the Report painter and the Form painter, but some functions are painter-specific. For example, five functions are crosstab functions and can be used only in crosstab reports in the Report painter.
The functions that we call InfoMaker functions are the same functions as the DataWindow functions you use in DataWindow painter expressions in PowerBuilder. PowerBuilder also has additional PowerScript functions and functions associated with objects and controls that you use when coding in PowerBuilder.
This appendix includes a short description of each function and some examples to help you use functions more easily. For complete information about each function (syntax, description, return value, and examples), see the Reference topic in InfoMaker's online Help.
Each function returns a value of a certain data type. An expression also returns a value having the data type of double, string, DateTime, or time. Function return values are provided in online Help. When you code in PowerBuilder using the PowerScript language, you often need to know a function's or expression's return value. In InfoMaker, you usually don't need to know about return values since you don't code in InfoMaker.
In InfoMaker, you create an expression that uses one or more functions in dialog boxes in the Report painter and the Form painter. The dialog boxes include a listbox that lists the available functions with placeholders for their arguments. These dialogs make it easier for you to insert one or more functions into the expression.
For example, when you add a computed field to a report or form, the Computed Object property sheet displays.
If you are an experienced user of functions, you can type the expression that defines the computed field in the Expression box. But if you need a little more help creating the expression, you can click the More button to display the Modify Expression dialog box and then select functions and column names in the Functions and Columns listboxes. Using either method, you can click the Verify button to test your expression.
The following table shows the name of each InfoMaker function and a description of what each function does.
For a complete description of each function, see online Help.
|Abs||Calculates the absolute value of a number|
|Asc||Converts the first character of a string to its ASCII integer value|
|Avg||Calculates the average of the values of the column|
|Bitmap||Displays the specified bitmap in a computed field in a report or form|
|Case||Tests the value of a column or expression and returns values based on the results of the test|
|Ceiling||Determines the smallest whole number that is greater than or equal to a specified limit|
|Char||Converts an integer to a character|
|Cos||Calculates the cosine of an angle|
|Count||Calculates the total number of rows in the specified column|
|CrosstabAvg||Calculates the average of the values returned by an expression in the values list of a crosstab report|
|CrosstabCount||Counts the number of values returned by an expression in the values list of a crosstab report|
|CrosstabMax||Calculates the maximum value returned by an expression in the values list of a crosstab report|
|CrosstabMin||Calculates the minimum value returned by an expression in the values list of a crosstab report|
|CrosstabSum||Calculates the sum of the values returned by an expression in the values list of a crosstab report|
|CumulativePercent||Calculates the total value of the rows up to and including the current row in the specified column as a percentage of the total value of the column (a running percentage)|
|CumulativeSum||Calculates the total value of the rows up to and including the current row in the specified column (a running total)|
|CurrentRow||Reports the number of the current row (the row with focus) in a form|
|Date||Converts a string whose value is a valid date to a value of data type date|
|DateTime||Combines a date and a time value into a DateTime value|
|Day||Obtains the day of the month in a date value|
|DayName||Determines the day of the week in a date value and returns the weekday's name|
|DayNumber||Determines the day of the week of a date value and returns the number of the weekday|
|DaysAfter||Determines the number of days one date occurs after another|
|Describe||Reports the values of attributes of a report or form object and objects within it. Each column and graphic object in the report or form has a set of attributes. You specify one or more attributes as a string and Describe returns the values of the attributes|
|Exp||Raises e to the specified power|
|Fact||Determines the factorial of a number|
|Fill||Builds a string of the specified length by repeating the specified characters until the result string is long enough|
|First||Determines the value in the first row in the specified column|
|GetRow||Reports the number of a row associated with a band in a report|
|GetText||Obtains the text that the user has entered in a column|
|Hour||Obtains the hour in a time value. The hour is based on a 24-hour clock|
|If||Evaluates a condition and returns a value based on that condition|
|Int||Determines the largest whole number less than or equal to a number|
|Integer||Converts the value of a string to an integer|
|IsDate||Tests whether a string value is a valid date|
|IsNull||Reports whether the value of a column or expression is NULL|
|IsNumber||Reports whether the value of a string is a number|
|IsRowModified||Reports whether the row has been modified|
|IsRowNew||Reports whether the row has been newly inserted in the report or form|
|IsSelected||Determines whether the row is selected. A selected row is highlighted using reverse video|
|IsTime||Reports whether the value of a string is a valid time value|
|Large||Finds a large value at a specified ranking in a column (for example, third largest, fifth largest) and returns the value of another column or expression based on the result|
|Last||Determines the value in the last row in the specified column|
|Left||Obtains a specified number of characters from the beginning of a string|
|LeftTrim||Removes spaces from the beginning of a string|
|Len||Reports the length of a string|
|Log||Determines the natural logarithm of a number|
|LogTen||Determines the base 10 logarithm of a number|
|Long||Converts the value of a string to a long|
|LookUpDisplay||Obtains the display value in the code table associated with the data value in the specified column|
|Lower||Converts all the characters in a string to lowercase|
|Match||Determines whether a string's value contains a particular pattern of characters|
|Max||Determines the maximum value in the specified column|
|Median||Calculates the median of the values of the column. The median is the middle value in the set of values, for which there is an equal number of values greater and smaller than it|
|Mid||Obtains a specified number of characters from a specified position in a string|
|Min||Determines the minimum value in the specified column|
|Minute||Obtains the number of minutes in the minutes portion of a time value|
|Mod||Obtains the remainder (modulus) of a division operation|
|Mode||Calculates the mode of the values of the column. The mode is the most frequently occurring value|
|Month||Determines the month of a date value|
|Now||Obtains the current time based on the system time of the client machine|
|Number||Converts a string to a number|
|Page||Determines the number of the current page|
|PageAcross||Determines the number of the current horizontal page. For example, if a report is twice the width of the preview window and the window is scrolled horizontally to display the portion of the report that was outside the preview, PageAcross will return 2 because the current page is the second horizontal page|
|PageCount||Determines the total number of pages|
|PageCountAcross||Determines the total number of horizontal pages when a report is wider than the preview window|
|Percent||Determines the percentage that the current value is of the total of the values in the column|
|Pi||Multiplies pi by a specified number|
|Pos||Finds one string within another string|
|ProfileInt||Obtains the integer value of a setting in the specified profile file|
|ProfileString||Obtains the string value of a setting in the specified profile file|
|Rand||Obtains a random whole number between 1 and a specified upper limit|
|Real||Converts a string value to a real data type|
|RelativeDate||Obtains the date that occurs a specified number of days after or before another date|
|RelativeTime||Obtains a time that occurs a specified number of seconds after or before another time within a 24-hour period|
|Replace||Replaces a portion of one string with another|
|RGB||Calculates the value that represents the color specified by numberic values for the red, green, and blue components of the color|
|Right||Obtains a specified number of characters from the end of a string|
|RightTrim||Removes spaces from the end of a string|
|Round||Rounds a number to the specified number of decimal places|
|RowCount||Obtains the number of rows that are currently available in the report's primary buffer|
|RowHeight||Reports the height of a row associated with a band in a report|
|Second||Obtains the number of seconds in the seconds portion of a time value|
|SecondsAfter||Determines the number of seconds one time occurs after another|
|Sign||Determines the sign of a number. Sign reports whether a number is negative, zero, or positive|
|Sin||Calculates the sine of an angle|
|Small||Finds a small value at a specified ranking in a column (for example, third smallest, fifth smallest) and returns the value of another column or expression based on the result|
|Space||Builds a string of the specified length whose value consists of spaces|
|Sqrt||Calculates the square root of a number|
|StDev||Calculates an estimate of the standard deviation for the specified column. Standard deviation is a measurement of how widely values vary from average|
|StDevP||Calculates the standard deviation for the specified column. Standard deviation is a measurement of how widely values vary from average|
|String||Formats data as a string according to a specified display format mask. You can convert and format date, DateTime, numeric, and time data. You can also apply a display format to a string|
|Sum||Calculates the sum of the values in the specified column|
|Tan||Calculates the tangent of an angle|
|Time||Converts a string to a time data type|
|Today||Obtains the system date|
|Trim||Removes leading and trailing spaces from a string|
|Truncate||Truncates a number to the specified number of decimal places|
|Upper||Converts all the characters in a string to uppercase|
|Var||Calculates an estimate of the variance for the specified column|
|VarP||Calculates the variance for the specified column|
|WordCap||Sets the first letter of each word in a string to a capital letter and all other letters to lowercase (for example, ROBERT E. LEE would be Robert E. Lee)|
|Year||Determines the year of a date value|
A NULL value is a marker used to fill a place in a column where data is missing for any reason. The value may not be applicable, or it may be missing or unknown. When a database table is created, each column in the table either allows NULL values or does not allow them. The column or set of columns that define the primary key cannot allow NULL values. Sometimes it's useful to know how many NULL values there are in a particular column.
You are working with the Fin_code table in the Powersoft Demo Database. The Fin_code table has three columns:
|Column||What the column is||Allows NULL values?|
|Code||Unique financial identifier (primary key)||No|
|Type||Code type: expense or revenue||No|
|Description||Code description: the department incurring the expense or getting the revenue||Yes|
You create a report using the Code and Description columns. You want to know the number of NULL values in the Description column.
In the report, you create a computed field that uses functions to display the number of NULL values in the Description column.
For the sake of demonstrating the use of functions, the following computed fields are created in the Summary band of the report (with text objects that tell you what information each computed field is providing):
Count( description for all )
which counts the number of descriptions (that are not NULL);
Sum( If ( IsNull( description ), 1, 0 ) )
which returns a 1 if the description column is NULL, a 0 if the description column is NOT NULL, and then adds the total;
Count( id for all )
which counts the number of IDs (which is also the number of rows);
Sum( If ( IsNull( description ), 1, 1 ) )
which adds the number of NULLs and NOT NULLs in the description column (which is the total number of rows) and should match the result of the Count( id for all ) function; and
IsNull ( description )
which evaluates whether the last row in the table has a description that is NULL. The return value of the IsNull function is TRUE or FALSE.
Here's the design for the report.
Here's the report. The report shows there are 8 descriptions, 3 of which are NULL and 5 of which are not NULL. The last description for Id=8 is NULL.
The previous example, "Example: counting NULL values in a column", demonstrates the use of the Sum and Count functions. Sum and Count are two examples of a class of functions called aggregate functions.
An aggregate function is a function that operates on a range of values in a column. InfoMaker's aggregate functions are:
Although the crosstab functions (CrosstabAve, CrosstabCount, CrosstabMax, CrosstabMin, and CrosstabSum) behave like aggregate functions, they are not included on the list because they are for crosstabs only and are designed to work in the crosstab matrix.
A few restrictions apply to the use of aggregate functions. You can't use an aggregate function:
The following example demonstrates the use of the Sum aggregate function.
Using the Employee table in the Powersoft Demo Database as the data source, you create a report using at least the Emp_id and the Sex columns. You want the report to display the number of male employees and female employees in the company.
In the summary band in the workspace, add two computed fields to the report that use the Sum and If functions:
Sum( If (sex = "M", 1, 0) )
which counts the number of males in your company;
Sum( If (sex = "F", 1, 0) )
which counts the number of females in your company.
You can also add a Page computed field (by clicking the Page computed field button) in the footer band to display the page number and total pages at the bottom of each page of the report.
Here's what the design of the report looks like.
After previewing the report, here's the last page of the report with the total number of males and females in the company displayed on the third page of the 3-page report.
What if you decide that you also want to know the number of males and females in each department in the company?
Sum( If (sex = "M", 1, 0) for group 1 )
which counts the number of males in each department;
Sum( If (sex = "F", 1, 0) for group 1)
which counts the number of females in each department.
Here's what the design of the grouped report looks like.
After previewing the report, here's the last page of the report with the number of males and females in the Shipping department displayed, followed by the total number of males and females in the company.
The following example demonstrates the use of many functions: Bitmap, Case, CurrentRow, GetRow, and RGB.
The example is presented in PowerBuilder's DataWindow painter, which is the same painter as InfoMaker's Report painter. You can use all the functions shown in the example in the Report painter. However, because you can change the current row and change data in a DataWindow (which you can't do in a report), the example is more interesting to consider in a DataWindow.
InfoMaker forms also allow you to change data, and you can use the CurrentRow function in the Form painter as well. In the Report painter, the CurrentRow function results in a pointer to the first row of the report.
Using the Employee table in the Powersoft Demo Database, you create a report using the Emp_id, Emp_fname, Emp_lname, and Salary columns.
In the DataWindow painter, you want to display a number of items such as: the number of the current row; an arrow that is an indicator of the current row; the salary for an employee with a background color that depends on what the salary is.
In the workspace, add the following:
which displays the number of the current row;
The expression results in an arrow displaying in the current row and no arrow displaying in other rows;
which displays the word "Current" when the row is the current row and "Not current" of all other rows;
If(CurrentRow() = GetRow(),"Current","Not current")
which display an arrow bitmap for the current row and no bitmap for all other rows;
Bitmap(If(CurrentRow()=GetRow(), "c:\pb5i32\ex\code\indicatr.bmp", " "))
Case(salary WHEN IS >60000 THEN RGB(192,192,192) WHEN IS >40000 THEN RGB(0,255,0) ELSE RGB(255,255,255))
The expression results in a salary above $40,000 displaying in green, a salary above $60,000 displaying in gray, and all other salaries displaying in white displaying in white.
Here's what the design of the DataWindow looks like:
After previewing the DataWindow, here's what the data looks like with the second row current.
Notice that the number of the current row is 2; the first row and the third row are "Not current" (and therefore display no bitmap); and the second row, which is the current row, displays the arrow row indicator.
On your screen, the salary in the first row has a green background because it's more than $40,000; the salary in the second row has a gray background because it's more than $60,000, and the salary in the third row has a white background, which matches the background of the DataWindow.
When you create an arithmetic expression that has a NULL value, the value of the expression is NULL. This makes sense since NULL means essentially undefined and the expression is undefined. But sometimes this fact can interfere with what you want to display.
A table in your database has four columns: Id, Corporation, Address1, and Address2. The Corporation, Address1, and Address2 columns allow NULLs. Using this table as the data source, you create a report using the four columns. You now want the report to display both parts of the address, separated by a comma.
You create a computed field to concatenate Address1 and Address2 with a comma separator. Here's the expression that defines the computed field.
address1 + ", " + address2
When you preview the report, if either Address1 or Address2 is NULL, no part of the address displays because the value of the expression is NULL. To display a part of the address, you need to create a computed field that forces evaluation even if Address2 is NULL. Note that we assume that Address2 will only have data if Address1 has data for a particular row.
In the detail band in the Report painter workspace, create a computed field that uses the If and IsNull functions:
If (IsNull ( address1 + address2 ), address1, address1 + ", " + address2 )
The computed field says this: if the concatenation of the addresses is NULL (because address2 is NULL), then display address1 and if it's not NULL, display the both parts of the address separated by a comma.
Here's what the design of the report looks like. The report includes both the computed field that doesn't work and the one that does.
When you run the report, notice that the first computed field displays NULL for ABC Corporation and XYZ Corporation. The second computed field displays the first part of the address, which is not NULL.
In InfoMaker, to see some examples of using functions, examine the reports and forms in TUTOR_IM.PBL, which is InfoMaker's sample library. The reports and forms were created using data in the Powersoft Demo Database.
Look carefully at the reports whose names begin with attrib_ . Each report is a good example of the use of functions in the Expressions property page in an object's property sheet. And look at the design of each report and form in the sample library to see the use of functions in other ways.
For examples of using the Expressions property page in an object's property sheet, see Chapter 18, "Highlighting Information." For an example of using the Integer, Real, and GetText functions in validation rules for forms, see Chapter 44, "Validating Data in Forms."
For an example of the use of each InfoMaker function, see the Reference topic in InfoMaker's online Help.