Using calculated columns

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 Querying a database. 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/borland/samples/tutorial/dataset/CalcColumn directory of your JBuilder installation under the project name CalcColumn.jpr.

  1. Complete the example described in How to query your database using the JBuilder UI.

  2. Select Frame1.java from the Navigation pane. Select the Design tab of the UI Designer. Click the plus sign (+) to the left of queryDataSet1 in the Component tree to expose its columns. Select <new column> and set the following properties in the Inspector for the new column:
    Property name Value
    calcType calculated
    caption NEW_SALARY
    columnName NEW_SALARY
    dataType BIGDECIMAL

    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 grid 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, go to the Events tab of the Property 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 readRow, DataRow dataRow, boolean boolean1)
        throws DataSetException{
        //calculate the new salary
        dataRow.setBigDecimal("NEW_SALARY", 
           readRow.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 (readRow, or changedRow), an output row for putting any changes you want to make to the row (dataRow, or calcRow), and a boolean (boolean1, or 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.


Aggregating data with calculated fields

You can use the aggregation feature of a calculated column to summarize your data in a variety of ways. Columns with a calcType of aggregated have the ability to

The aggDescriptor property is used to specify columns to group, the column to aggregate, and the aggregation operation to perform. The aggDescriptor is described in more detail below. The aggregation operation is an instance of one of these classes: CountAggOperator, SumAggOperator, MaxAggOperator, MinAggOperator, or a custom aggregation class that you define.

Creating a calculated aggregated column is simpler than creating a calculated column, because no event method is necessary (unless you are creating a custom aggregation component). The aggregate can be computed for the entire data set, or you can group by one or more columns in the data set and compute an aggregate value for each group. The calculated aggregated column is defined in the data set being summarized, so every row in a group will have the same value in the calculated column (the aggregated value for that group). The column is hidden by default. You can choose to show the column or show its value in another control, which is what we do in the following tutorial section.

Tutorial: Aggregating data with calculated fields

In this example, we will query the SALES table and create a TextFieldControl component to display the sum of the TOTAL_VALUE field for the current CUST_NO field. To do this, we first create a new column called GROUP_TOTAL. Then set the calcType property of the column to aggregated and create an expression that summarizes the TOTAL_VALUE field from the SALES table by customer number and places the resulting value in the GROUP_TOTAL column. The completed project is available in the samples/borland/samples/tutorial/dataset/AggCalc directory of your JBuilder installation under the project name AggCalc.jpr.

  1. Select File|Close all from the menu to close existing applications. Select File|New. Double-click the Application icon to start the Application Wizard. Accept all defaults to create a new application.

  2. Select Frame1.java in the Navigation pane, then select the Design tab of the UI Designer to put JBuilder into Design mode.

  3. Put a Database component from the Data Express tab of the Component palette on the Component Tree and set its connection property as follows:
    For this optionMake this choice

    Connection URL jdbc:odbc:DataSet Tutorial
    Username SYSDBA
    Password masterkey

    Click the Test Connection button to test the connection and ensure its validity. If the connection is successful, click OK. If not successful, see Troubleshooting JDBC connections in the tutorials.

  4. Add a QueryDataSet component from the Data Express tab to the Component Tree. This will form the query to populate the data set with values to be aggregated. Set the query property of queryDataSet1 as follows:
    For this optionMake this choice

    Database database1
    SQL Statement select cust_no, PO_NUMBER, SHIP_DATE, TOTAL_VALUE from SALES
    Place SQL text in resource bundle unchecked

    Click the Test Query button to test the query and ensure its validity. If successful, click OK. If not successful, review the SQL statement for errors.

  5. Add a GridControl component from the JBCL tab of the Component palette and set its dataSet property to queryDataSet1. This enables us to view data in the designer and when the application is running.

  6. Click the plus sign (+) to the left of the queryDataSet1 component in the Component tree to expose the columns. Select <new column> and set the following properties in the Inspector for the new column:
    Property name Value
    caption GROUP_TOTAL
    columnName GROUP_TOTAL
    currency True
    dataType BIGDECIMAL
    calcType aggregated

    A new column is instantiated and the following code is add to the jbInit() method. To view the code, select the Source tab. To view more code, use Alt+Z to toggle between the standard view and an expanded view of code.

        column1.setCurrency(true);
        column1.setCalcType(borland.jbcl.dataset.CalcType.AGGREGATE);
        column1.setCaption("GROUP_TOTAL");
        column1.setColumnName("GROUP_TOTAL");
        column1.setDataType(borland.jbcl.util.Variant.BIGDECIMAL);
    

  7. Add a TextFieldControl from the JBCL tab of the Component palette to the UI Designer. Set its columnName property to GROUP_TOTAL. Set its dataSet property to queryDataSet1. This control displays the aggregated data. You may wish to add a LabelControl to describe what the text field is displaying.

    No data will be displayed in the TextFieldControl 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 TOTAL_VALUE column which will be used in the calculation expression. Aggregated columns are always read-only.

  8. Select <new column> again and set the following properties in the Inspector for the new column:
    Property name Value
    columnName PO_NUMBER
    dataType STRING

    This and the next two steps ensure the columns that will display in the grid are persistent. Persistent columns are enclosed in brackets in the Component tree. Also, when you add more than one column, you can manually edit the setColumns() method in the Source code pane to change the order of display of the columns.

  9. Select <new column> again and set the following properties in the Inspector for the new column:
    Property name Value
    columnName CUST_NO
    dataType INT

  10. Select <new column> again and set the following properties in the Inspector for the new column:
    Property name Value
    columnName SHIP_DATE
    dataType TIMESTAMP

  11. Select the GROUP_TOTAL column in the Component tree. To define the aggregation for this column, double-click on the agg property to display the agg property editor.

    In the agg property editor,

    Based on above selections, you will have a sum of all sales to a given customer.

    This step generate the following source code in the jbInit() method:

      column1.setAgg(new borland.jbcl.dataset.AggDescriptor(new String[] {"CUST_NO"},
             "TOTAL_VALUE", new borland.jbcl.dataset.SumAggOperator()));
    

    When the agg property editor looks like the one below, click OK.

  12. Run the application by selecting Run|Run to view the aggregation results.

When the application is running, the values in the aggregated field will automatically adjust to changes in the TOTAL_VALUE field. Also, the value that displays in the TextFieldControl will display the aggregation for the CUST_NO of the currently selected row.


The AggDescriptor

The agg property editor provides a simple interface for creating and modifying AggDescriptor objects. An AggDescriptor object's constructor requires the following information:

The agg property editor uses CMT to extract possible column names for use as grouping columns, and presents them as a list of Available Columns. Only non-calculated, non-aggregate column names are allowed in the list of grouping columns.

If the DataSet for whose Column the agg property is being defined has a MasterLink descriptor (i.e., is a detail DataSet), the linking column names will be added by default to the list of grouping columns when defining a new AggDescriptor.

The buttons beneath the list of grouping columns and available columns can be used to move the highlighted column name of the list above the button to the opposite list. Also, double-clicking on a column name in a list will move the column name to the opposite list. Entries within both lists are read-only; there should be no way to edit the contents of either list. Note that since the ordering of column names is insignificant within a group, a column name is always appended to the end of its destination list. An empty (null) group is allowed.

The Aggregate Column choice control will contain the list of all non-aggregate column names for the current DataSet, obtained also from CMT. Although the current set of AggOperators provided with JBCL does not provide support for non-numeric aggregate column types, we do not restrict columns in the list to numeric types, since it's possible that a user's customized AggOperator could support string and date types.

The Aggregate Operation choice control displays the list of AggOperators built into JBCL as well as any user-defined AggOperators detectable by CMT within the same class context as the AggDescriptor's Column.

Users desiring to perform calculations on aggregated values (e.g., the sum of line items ordered multiplied by a constant) should check the Calculated Aggregate check box. Doing so disables the Aggregate Column and Aggregate Operation choice controls, and substitutes their values with 'null' in the AggDescriptor constructor, signifying a calculated aggregate type. When the Calculated Aggregate check box is unchecked, the Aggregate Column and Aggreage Operation choice controls are enabled.

Clicking the OK button assigns an AggDescriptor created with the settings defined in the dialog box to the Column's agg property. Clicking the Cancel button restores the Column's agg property to its previous state. Clicking the Help button activates the Help system, displaying "Agg property editor" as the current help page.

Creating a custom aggregation event handler

To use an aggregation method other than the ones provided by JBuilder, you can create a custom aggregation event handler. One way to create a custom aggregation event handler is to code the calcAggAdd and calcAggDelete events through the UI Designer. calcAggAdd and calcAggDelete are StorageDataSet events that are called after the AggOperator is notified of an update operation. A typical use for these events is for totalling columns in a line items table (like SALES). The dollar amounts can be totalled using a built-in SumAggOperator. Additional aggregated columns can be added with the AggDescriptor's aggOperator property set to null. These additional columns might be for applying a tax or discount percentage on the subtotal, calculating shipping costs, and then calculating a final total.

You can also create a custom aggregation class by implementing a custom aggregation operator component by extending from AggOperator and implementing the abstract methods. The advantage of implementing a component is reusability in other DataSets. You may wish to create aggregation classes for calculating an average, standard deviation, or variance.

Check the Borland JBuilder technical publications Web site at http://www.borland.com/techpubs/borland/jbuilder/ for updates to this topic that may include a tutorial or sample custom aggregations. Check the samples directory in case a sample custom aggregation example is created after this documentation was written.