Using calculated columns

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

Typically, a Column in a StorageDataSet derives its values from data in a database column or as a result of being imported from a text file. A column may also derive its values as a result of a calculated expression. JBuilder supports two kinds of calculated columns: calculated and aggregated.

In order to create a calculated column, you need to create a new persistent Column object in the StorageDataSet and supply the expression to the StorageDataSet object's calcFields event handler. Calculated columns can be defined and viewed in JBuilder. The calculated values are only visible in the running application. JBuilder-defined calculated columns are not resolved to or provided from its data source, although they can be written to a text file. For more information on defining a calculated column in the designer, see "Tutorial: Creating a calculated column in the designer". For more information on working with columns, see "Working with columns".

The formula for a calculated column generally uses expressions involving other columns in the data set to generate a value for each row of the data set. For example, a data set might have non-calculated columns for QUANTITY and UNIT_PRICE and a calculated column for EXTENDED_PRICE. EXTENDED_PRICE would be calculated by multiplying the values of QUANTITY and UNIT_PRICE.

Calculated aggregated columns can be used to group and/or summarize data, for example, to summarize total sales by quarter. Aggregation calculations can be specified completely through property settings and any number of columns can be included in the grouping. Four types of aggregation are supported (sum, count, min, and max) as well as a mechanism for creating custom aggregation methods. For more information, see "Aggregating data with calculated fields".

Calculated columns are also useful for holding lookups from other tables. For example, a part number can be used to retrieve a part description for display in an invoice line item. For information on using a calculated field as a lookup field, see "Creating lookups".

Values for all calculated columns in a row are computed in the same event call.

These are the topics covered:


Tutorial: Creating a calculated column in the designer

This tutorial builds on the example in "Retrieving data for the tutorials". The database table that is queried is EMPLOYEE. The premise for this example is that the company is giving all employees a 10% raise. We create a new column named NEW_SALARY and create an expression that multiplies the existing SALARY data by 1.10 and places the resulting value in the NEW_SALARY column. The completed project is available in the /samples/DataExpress/CalculatedColumn subdirectory of your JBuilder installation under the project name CalculatedColumn.jpr.

  1. Create a new application by following "Retrieving data for the tutorials". This step enables you to connect to a database, read data from a table, and view and edit that data in a data-aware component.
  2. Click the expand icon beside queryDataSet1 in the component tree to display all columns. Select <new column>. Set the following properties in the Inspector:
    Property name Value
    calcType CALC
    caption NEW_SALARY
    columnName NEW_SALARY
    dataType BIGDECIMAL
    currency true

    If you were adding more than one column, you could manually edit the setColumns() method to change the position of the new columns or any other persistent column. No data will be displayed in the calculated column in the table in the designer. The calculations are only visible when the application is running. The data type of BIGDECIMAL is used here because that is the data type of the SALARY column which will be used in the calculation expression. Calculated columns are always read-only.

  3. Select the queryDataSet1 object, select the Events tab of the Inspector, select the calcFields event handler, and double-click its value. This creates the stub for the event's method in the Source window.

  4. Modify the event method to calculate the salary increase, as follows:

    	void queryDataSet1_calcFields(ReadRow changedRow, DataRow
    		calcRow, boolean isPosted) throws DataSetException{
      			//calculate the new salary
      			calcRow.setBigDecimal("NEW_SALARY",
    				changedRow.getBigDecimal("SALARY").multiply(new
    				BigDecimal(1.1)));
    	}
    

    This method is called for calcFields whenever a field value is saved and whenever a row is posted. This event passes in an input which is the current values in the row (changedRow), an output row for putting any changes you want to make to the row (calcRow), and a boolean (isPosted) that indicates whether the row is posted in the DataSet or not. You may not want to recalculate fields on rows that are not posted yet.

  5. Import the java.math.BigDecimal class to use a BIGDECIMAL data type. Add this statement in the Source window to the existing import statements.

    	import java.math.BigDecimal;
    
  6. Run the application to view the resulting calculation expression.

When the application is running, the values in the calculated column will automatically adjust to changes in any columns referenced in the calculated expression. The NEW_SALARY columns displays the value of (SALARY * 1.10). The running application looks like this:

CalculatedColumn sample