Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.
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
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
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.
JdbTextField
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/DataExpress/Aggregating subdirectory of your JBuilder installation.
Click on queryDataSet1
in the component tree. This forms the query to populate the data set with values to be aggregated. Open the query
property of queryDataSet1
, and modify the SQL Statement to read:
SELECT CUST_NO, PO_NUMBER, SHIP_DATE, TOTAL_VALUE from SALES
Click the Test Query button to test the query and ensure its validity. When successful, click OK.
Click on the expand icon beside queryDataSet1
in the component tree. Select <new column>. In the Inspector, set the following properties:
Property name | Value |
caption |
GROUP_TOTAL |
columnName |
GROUP_TOTAL |
currency |
True |
dataType |
BIGDECIMAL |
calcType |
aggregated |
visible |
Yes |
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, select the Design tab to continue.
column1.setCurrency(true); column1.setCalcType(com.borland.dx.dataset.CalcType.AGGREGATE); column1.setCaption("GROUP_TOTAL"); column1.setColumnName("GROUP_TOTAL"); column1.setDataType(com.borland.dx.dataset.Variant.BIGDECIMAL);
Add a JdbTextField
from the dbSwing tab of the component palette to the UI designer. Set its dataSet
property to queryDataSet1
. Set its columnName
property to GROUP_TOTAL. This control displays the aggregated data. You may wish to add a JdbTextArea
to describe what the text field is displaying.
No data will be displayed in the JdbTextField
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.
Select each of the following columns, and set the visible property of each to yes
.
This step ensures the columns that will display in the table are persistent. Persistent columns are enclosed in brackets in the content pane.
Select the GROUP_TOTAL column in the content pane. To define the aggregation for this column, double-click the agg
property to display the agg
property editor.
In the agg
property editor,
Select TOTAL_VALUE from the Aggregate Column list to select this as the column that contains the data to be aggregated.
Select SumAggOperator
from the Aggregate Operation list to select this as the operation to be performed.
Based on above selections, you will have a sum of all sales to a given customer.
When the agg
property editor looks like the one below, click OK.
This step generates the following source code in the jbInit()
method:
column1.setAgg(new com.borland.dx.dataset.AggDescriptor(new String[] {"CUST_NO"}, "TOTAL_VALUE", new com.borland.dx.dataset.SumAggOperator()));
Run the application by selecting Run|Run Project to view the aggregation results.
The running application looks like this:
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 JdbTextField
will display the aggregation for the CUST_NO of the currently selected row.
The agg
property editor provides a simple interface for creating and modifying AggDescriptor
objects. An AggDescriptor
object's constructor requires the following information:
DataSet
over which the aggregation should occur.
AggOperator
type which performs the actual
aggregate operation.
The agg
property editor extracts 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. 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
. Although the current set of AggOperator
s provided with DataExpress package do 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 AggOperator
s built into DataExpress package as well as any user-defined AggOperator
s 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
Aggregate Operation choice controls are enabled.
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 totaling columns in a line items table (like SALES). The dollar amounts can be totaled 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 DataSet
s. You may wish to create aggregation classes for calculating an average, standard deviation, or variance.