You can use computed fields in any band of the DataWindow object. Typical uses with examples include:
-
Calculations based on column data that change for each retrieved row
If you retrieve yearly salary, you can define a computed field in the detail band that displays monthly salary: Salary / 12.
-
Summary statistics of the data
In a grouped DataWindow object, you can use a computed field to calculate the totals of a column, such as salary, for each group: sum (salary for group 1).
-
Concatenated fields
If you retrieve first name and last name, you can define a computed field that concatenates the values so they appear with only one space between them: Fname + " " + Lname.
-
System information
You can place the current date and time in a DataWindow object's header using the built-in functions Today() and Now() in computed fields.
When creating a DataWindow object, you can define computed columns and computed fields as follows:
-
In the SQL Select painter, you can define computed columns when you are defining the SELECT statement that will be used to retrieve data into the DataWindow object.
-
In the DataWindow painter, you can define computed fields after you have defined the SELECT statement (or other data source).
The difference between the two ways
When you define the computed column in the SQL Select painter, the value is calculated by the DBMS when the data is retrieved. The computed column's value does not change until data has been updated and retrieved again.
When you define the computed field in the DataWindow painter, the value of the column is calculated in the DataWindow object after the data has been retrieved. The value changes dynamically as the data in the DataWindow object changes.
Example
Consider a DataWindow object with four columns: Part number, Quantity, Price, and Cost. Cost is computed as Quantity * Price.
If Cost is defined as a computed column in the SQL Select painter, the SELECT statement is as follows:
SELECT part.part_num, part.part_qty, part.part_price, part.part_qty * part.part_price FROM part;
If the user changes the price of a part in the DataWindow object in this scenario, the cost does not change in the DataWindow object until the database is updated and the data is retrieved again. The user sees a display with the changed price but the unchanged, incorrect cost.
If Cost is defined as a computed field in the DataWindow object, the SELECT statement is as follows, with no computed column:
SELECT part.part_num, part.part_qty, part.part_price FROM part;
The computed field is defined in the DataWindow object as Quantity * Price.
In this scenario, if the user changes the price of a part in the DataWindow object, the cost changes immediately.
Recommendation
If you want your DBMS to do the calculations on the server before bringing data down and you do not need the computed values to be updated dynamically, define the computed column as part of the SELECT statement.
If you need computed values to change dynamically, define computed fields in the DataWindow painter Design view, as described next.
To define a computed field in the DataWindow painter Design view
-
Select Insert>Control>Computed Field from the menu bar.
-
Click where you want to place the computed field.
If the calculation is to be based on column data that changes for each row, make sure you place the computed field in the detail band.
The Modify Expression dialog box displays, listing:
-
DataWindow expression functions you can use in the computed field
-
The columns in the DataWindow object
-
Operators and parentheses
-
-
Enter the expression that defines the computed field as described in Entering the expression.
-
(Optional) Click Verify to test the expression.
PowerBuilder analyzes the expression.
-
Click OK.
You can enter any valid DataWindow expression when defining a computed field. You can paste operators, columns, and DataWindow expression functions into the expression from information in the Modify Expression dialog box. Use the + operator to concatenate strings.
You can use any built-in or user-defined global function in an expression. You cannot use object-level functions.
DataWindow expressions
You are entering a DataWindow expression, not a SQL expression processed by the DBMS, so the expression follows the rules for DataWindow expressions. For complete information about DataWindow expressions, see DataWindow Expression Functions in DataWindow Reference.
Referring to next and previous rows
You can refer to other rows in a computed field. This is particularly useful in N-Up DataWindow objects when you want to refer to another row in the detail band. Use this syntax:
ColumnName[x]
where x is an integer. 0 refers to the current row (or first row in the detail band), 1 refers to the next row, –1 refers to the previous row, and so on.
Examples
The following table shows some examples of computed fields.
|
To display |
Enter this expression |
In this band |
|---|---|---|
|
Current date at top of each page |
Today() |
Header |
|
Current time at top of each page |
Now() |
Header |
|
Current page at bottom of each page |
Page() |
Footer |
|
Total page count at bottom of each page |
PageCount() |
Footer |
|
Concatenation of Fname and Lname columns for each row |
Fname + " " + Lname |
Detail |
|
Monthly salary if Salary column contains annual salary |
Salary / 12 |
Detail |
|
Four asterisks if the value of the Salary column is greater than $50,000 |
IF(Salary> 50000, "****", "") |
Detail |
|
Average salary of all retrieved rows |
Avg(Salary) |
Summary |
|
Count of retrieved rows, assuming each row contains a value for EmpID |
Count(EmpID) |
Summary |
For complete information about the functions you can use in computed fields in the DataWindow painter, see DataWindow Reference.
Menu options and buttons for common functions
PowerBuilder provides a quick way to create computed fields that summarize values in the detail band, display the current date, or show the current page number.
To summarize values
-
Select one or more columns in the DataWindow object's detail band.
-
Select one of the options at the bottom of the cascading menu: Average, Count, or Sum.
The same options are available at the bottom of the Controls drop-down toolbar on the PainterBar.
PowerBuilder places a computed field in the summary band or in the group trailer band if the DataWindow object is grouped. The band is resized automatically to hold the computed field. If there is already a computed field that matches the one being generated, it is skipped.
To insert a computed field for the current date or page number
-
Select Insert>Control from the menu bar.
-
Select Today() or Page n of n from the options at the bottom of the cascading menu.
The same options are available at the bottom of the Controls drop-down toolbar on the PainterBar.
-
Click anywhere in the DataWindow object.
If you selected Today, PowerBuilder inserts a computed field containing this expression: Today(). For Page n of n, the computed field contains this expression: 'Page ' + page() + ' of ' + pageCount().
Adding custom buttons that place computed fields
You can add buttons to the PainterBar in the DataWindow painter that place computed fields using any of the aggregate functions, such as Max, Min, and Median.
To customize the PainterBar with custom buttons for placing computed fields
-
Place the mouse pointer over the PainterBar and select Customize from the pop-up menu.
The Customize dialog box displays.
-
Click Custom in the Select palette group to display the set of custom buttons.
-
Drag a custom button into the Current toolbar group and release it.
The Toolbar Item Command dialog box displays.
-
Click the Function button.
The Function For Toolbar dialog box displays.
-
Select a function and click OK.
You return to the Toolbar Item Command dialog box.
-
Specify text and microhelp that displays for the button, and click OK.
PowerBuilder places the new button in the PainterBar. You can click it to add a computed field to your DataWindow object the same way you use the built-in Sum button.
