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:
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.
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 read-only by default.
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.
import java.math.BigDecimal;
This concludes the calculated columns tutorial. When the application is running, the values in the calculated column will automatically adjust to changes in any columns referenced in the calculated expression.
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 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.
For this option | Make 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 not successful, see Troubleshooting JDBC connections in the tutorials.
For this option | Make this choice |
---|---|
Database | database1 |
SQL Statement | select cust_no, PO_NUMBER, SHIP_DATE, TOTAL_VALUE from SALES |
Click the Test Query button to test the query and ensure its validity.
Property name | Value |
calcType | aggregated |
caption | GROUP_TOTAL |
columnName | GROUP_TOTAL |
dataType | BIGDECIMAL |
If you were adding more than one column, you could manually edit the setColumns() method in the Source code pane to change the position of the new columns or any other 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 TOTAL_VALUE column which will be used in the calculation expression. Aggregated columns are read-only by default.
defineAgg(); //defines the aggregation method
//Set AggregationDescriptor for calculated column public void defineAgg() throws Exception{ column1.setAgg(new AggDescriptor(new String[] {"cust_no"}, "TOTAL_VALUE", new SumAggOperator())); }
import java.math.BigDecimal;
This concludes the aggregated column tutorial. When the application is running, the values in the aggregated calculated column will automatically adjust to changes in any referenced columns.
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.