Sybase Technical Library - Product Manuals Home
 inside 
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search

Appendix B:  Operators and Expressions [Table of Contents]

InfoMaker User's Guide

[-] Part 8 Appendixes
[-] Appendix C: InfoMaker Functions

Appendix InfoMaker Functions

About this chapter

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.

Using InfoMaker functions

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.

Use InfoMaker's online Help

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.

Creating an expression that uses a function

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.

raster

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.

raster

List of functions

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.
Function Description
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

Example: counting NULL values in a column

Introduction

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.

What you want to do

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.

How to do it

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.

What you get

Here's the design for the report.

raster

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.

raster

Example: counting male and female employees

Introduction

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:

Ave Large Mode Sum
Count Last Percent Var
CumulativePercent Max Small VarP
CumulativeSum Median StDev
First Min StDevP

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.

What you want to do

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.

How to do it

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.

What you get

Here's what the design of the report looks like.

raster

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.

raster

If you now want more information in the report

What if you decide that you also want to know the number of males and females in each department in the company?

Here's what the design of the grouped report looks like.

raster

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.

raster

Example: creating a row indicator

Introduction

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.

What you want to do

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.

How to do it

In the workspace, add the following:

What you get

Here's what the design of the DataWindow looks like:

raster

After previewing the DataWindow, here's what the data looks like with the second row current.

raster

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.

Example: displaying all data when a column allows NULLs

Introduction

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.

What you want to do

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.

How to do it

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.

What you get

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.

raster

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.

raster

Other examples

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.


Appendix B:  Operators and Expressions [Table of Contents]