DataTables allow subsets of data to be selected and aggregated. They also allow calculated columns to be created on the fly when creating new DataTables or object arrays.


 
var data = [ 
    {product: "apples", order: "PRN001", quantity: 10, price: 5.5},
    {product: "oranges", order: "PRN002", quantity: 15, price: 6},
    {product: "bananas", order: "PRN002", quantity: 20, price: 7.5}
];
 
var table = new eb.data.DataTable(data);
 
// select two columns from the table and create a new one
table = table.select("product", "price").table();


To format columns, reference the column in the column method and then use the format method. Number formatting is supported using P1 and N1 for percentage and numeric formatting respectively. Change 1 to any decimal place.


// format price with 1 decimal place
table = table.select("product").column("price").format("N1").table();


Use as to give any column a descriptive title. This will be set into the Title property for the column in the result table.


// change the title for product
table = table.select().column("product").as("Product Title").table();


Calculated Columns


New columns can be created using the following aggregation methods.

  • avg
  • count
  • distinctCount
  • max
  • min
  • sum


var data = [ 
    {product: "apples", order: "PRN001", quantity: 10, price: 5.5},
    {product: "oranges", order: "PRN002", quantity: 15, price: 6},
    {product: "bananas", order: "PRN002", quantity: 20, price: 7.5}
];
 
// total quantity with a new title and average price
table = table.select()
    .sum("quantity").as("Total Quantity")
    .avg("price").format("N1")
    .table();



Aggregates can also be used with implicit grouping by selecting other columns to be included in the result. The example below includes the order column so the calculated column Total Quantity is broken out by order.


 // select order and Total Quantity
table = table.select("order").sum("quantity").as("Total Quantity").table();



Calculated columns can also be created using custom formulas by using the calc method.


// return the quantity column times 10 in a new column using the sum aggregation method
table = table.select()
    .sum().calc(function(row){
        return row["quantity"] * 10;
    })
    .as("More Quantity").table();