Tutorial: Aggregating data with calculated fields

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. In this example, we will query the SALES table and create a 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.

  1. Close all open projects (select File|Close Project).

  2. Select File|New Project.

  3. Enter a name and location for the project in the Project Wizard. Click Finish.

  4. Select File|New from the menu. Double-click the Application icon.

  5. Specify the package name and class name in the Application Wizard. Click Finish.

  6. Select the Design tab to activate the UI designer.

  7. Click the Database component on the Data Express tab of the component palette, then click in the Structure pane to add the component to the application.

    Open the connection property editor for the Database component by selecting, then double-clicking the connection property ellipsis in the Inspector. Set the connection properties to the JDataStore sample employee table as follows. The Connection URL points to a specific installation location. If you have installed JBuilder to a different directory, point to the correct location for your installation.
    Property name Value
    Driver com.borland.datastore.jdbc.DataStoreDriver
    URL jdbc:borland:dslocal:/usr/local/jbuilder/samples/JDataStore/ datastores/employee.jds

    The employee.jds database is located under the samples directory of your JBuilder installation, which may be different on your system. This sample has been modified to find the file on your system.

    Username Enter your name
    Password not required

    The connection dialog includes a Test Connection button. Click this button to check that the connection properties have been correctly set. Results of the connection attempt are displayed in the status area. When the connection is successful, click OK.

  8. Add a QueryDataSet component to the designer by clicking on the QueryDataSet component on the Data Express tab and then clicking in the Structure pane.

    Select the query property of the QueryDataSet component in the Inspector, double-click its ellipsis to open the QueryDescriptor dialog, and set the following properties:
    Property name Value
    Database database1
    SQL Statement SELECT * FROM EMPLOYEE

    Click Test Query to ensure that the query is runnable. When the status area indicates Success, click OK to close the dialog.

  9. Select a DBDisposeMonitor component from the More dbSwing tab. Click in the Structure pane to add it to the application. The DBDisposeMonitor will close the JDataStore when the window is closed.

  10. Set the DBDisposeMonitor's dataAwareComponentContainer property to this.

To view the data in your application we need to add some UI components and bind them to the data set. To do this,

  1. Select contentPane(BorderLayout) in the Structure pane. In the Inspector, set its layout property to null.

  2. Select a JdbNavToolBar component from the dbSwing tab. Drop the component in the area slightly above the panel in the Design frame. JdbNavToolBar automatically attaches itself to whichever DataSet has focus, so you do not need to set its dataSet property.

    The JdbNavToolBar will enable you to move quickly through the data set when the application is running, as well as provide a default mechanism for saving changes back to your data source.

  3. Click on the JdbStatusLabel component on the dbSwing tab of the component palette. Drop the component in the area slightly below the panel in the Design pane. JdbStatusLabel automatically attaches itself to whichever DataSet has focus, so you do not need to set its dataSet property.

    Among other information, the status label displays information about the current record or current operation.

  4. Add a TableScrollPane component from the dbSwing tab to the designer.

    Scrolling behavior is not available by default in any Swing component or dbSwing extension, so, to get scrolling behavior, we add the scrollable Swing or dbSwing components to a JScrollPane or a TableScrollPane. TableScrollPane provides special capabilities to JdbTable over JScrollPane. See the dbSwing documentation for more information.

  5. Drop a JdbTable component from the dbSwing tab into the TableScrollPane component. Set its dataSet property to queryDataSet1.

    You'll notice that the designer displays live data at this point.

To add aggregating functionality,

  1. Select the QueryDataSet component from the Structure pane. 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.

  2. Select the expand glyph beside queryDataSet1 in the Structure pane. 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);
    

  3. 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.

  4. 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 Structure pane.

  5. Select the GROUP_TOTAL column in the Structure pane. To define the aggregation for this column, double-click 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.

    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()));
    

  6. Run the application by selecting Run|Run Project to view the aggregation results.

Modify a value in the TOTAL_VALUE column. When the application is running, the values in the aggregated field (GROUP_TOTAL) will automatically adjust to changes in the TOTAL_VALUE field. Also, the value that displays in the JdbTextField is the sum of the TOTAL_VALUE entries for the customer in the currently selected row.