Prepare Data

Pivottable takes a model for its data population. Analogous to ListModel for Grid component, the model interface we use for Pivottable is PivotModel.

However, unlike Grid or Listbox, due to the logic of pivot table, we cannot specify data as children components of a Pivottable, so you always need to prepare a PivotModel prior to using Pivottable.

Construct a PivotModel

TabularPivotModel is a standard implementation of PivotModel. It's constructor takes two List, for raw data and column labels.

public TabularPivotModel(List<? extends List<?>> data, List<String> columns) { ... }

Here is a simple example of constructing a TabularPivotModel.

	public static TabularPivotModel getModel() {
		return new TabularPivotModel(getData(), getColumns());
	}
	
	// raw data
	public static List<List<Object>> getData() {
		Object[][] objs = new Object[][] {
				{ "Carlene Valone", "Tameka Meserve",    "ATB Air", "AT15",  "Berlin",     "Paris",     186.6, 545  },
				{ "Antonio Mattos", "Sharon Roundy",     "Jasper",  "JS1",   "Frankfurt",  "Berlin",    139.5, 262  },
				{ "Russell Testa",  "Carl Whitmore",     "Epsilon", "EP2",   "Dublin",     "London",    108.0, 287  },
				{ "Antonio Mattos", "Velma Sutherland",  "Epsilon", "EP5",   "Berlin",     "London",    133.5, 578  },
				{ "Carlene Valone", "Cora Million",      "Jasper",  "JS30",  "Paris",      "Frankfurt", 175.4, 297  },
				{ "Richard Hung",   "Candace Marek",     "DTB Air", "BK201", "Manchester", "Paris",     168.5, 376  },
				{ "Antonio Mattos", "Albert Briseno",    "Fujito",  "FJ1",   "Berlin",     "Osaka",     886.9, 5486 },
				{ "Russell Testa",  "Louise Knutson",    "HST Air", "HT6",   "Prague",     "London",    240.6, 643  },
				{ "Antonio Mattos", "Jessica Lunsford",  "Jasper",  "JS9",   "Munich",     "Lisbon",    431.6, 1222 },
				// more rows...
				{ "Russell Testa",  "Velma Sutherland",  "Epsilon", "EP4",   "London",     "Berlin",    155.7, 578  }
		};
		
		List<List<Object>> list = new ArrayList<List<Object>>();
		for(Object[] a : objs)
			list.add(Arrays.asList(a));
		return list;
	}
	
	// column labels
	public static List<String> getColumns() {
		return Arrays.asList(new String[]{
				"Agent", "Customer", "Airline", "Flight", "Origin", "Destination", "Price", "Mileage"
		});
	}

Determine fields on rows and columns

In addition to providing data, you also need to specify how you want to categorize it. For example, given the previously constructed TabularPivotModel

// what to show on column headers (how you categorize the x-axis)
model.addColumnField("Airline");

// what to show on row headers (how you categorize the y-axis)
model.addRowField("Agent");

// which field to show in data cell
model.addDataField("Price");

This will result in a Pivottable that looks like

Of course, to utilize the power of Pivottable, you can specify multiple fields as column, row, and data fields.

// columns are categorized by Airline, then Flight
model.addColumnField("Airline");
model.addColumnField("Flight");

// rows are categorized by Agent, then Customer
model.addRowField("Agent");
model.addRowField("Customer");

// show sum of Price and Mileage in each cell, if any
model.addDataField("Price");
model.addDataField("Mileage");

Now the Pivottable shall look like

Note:

• If you do not specify any fields on row or column, the model will still work (as it makes sense for the model). In this case, only grand total row/column will be shown.
• You need at least one data field.

Hint: If you can't wait to play around with the component, you can jump to next section and come back to read further if necessary.

Summary and Subtotals

After adding fields on a PivotModel, you can change the summary type for the data, or set subtotals to rows or columns.

For example, to change summary type:

// assume "Price" and "Mileage" were added as data fields
TabularPivotField field = model.getField("Price");
field.setSummary(Calculators.AVERAGE); 
// now the values in data cells are averages of Prices, instead of sums
// Mileage values are still sum, not average

Note:

• TabularPivotField is an implementation of PivotField that comes with TabularPivotModel
• TabularPivotField provides a default summary, which sums up raw values if they are numeric, or count their numbers otherwise.
• A summary is represented by interface Calculator.
• Calculators is a utility class which provides a set of predefined Calculator.

To set subtotals on column or row, you have to specify on a column field or row field:

// assume "Agent", "Customer" are column fields
TabularPivotField field = model.getField("Agent");
field.setSubtotals(new Calculator[]{
		Calculators.AVERAGE, Calculators.COUNT
});
// now when you open a header node of Agent, there will be two subtotal columns

Note:

• By default, TabularPivotField has no subtotal on each field.
• Setting subtotals on a field is only effective if it is a column or row field, and it is NOT the last field among column or row fields.
• Subtotals are shown only when the corresponding node is open. When the node is closed, it's underlying data are summarized by data field's summary.
• Setting subtotals on a field does not affect other fields. In other words, if you have A, B, C as column fields and specify a subtotal on B, then A has no subtotal.

Grouping

On row and column, we categorize data by fields in the given order, but sometimes you may want to group by a coarser fashion. For example, you may have a field of dates, but you want to categorize the data just by month.

In this case we can specify a GroupHandler to the field:

// suppose "Date" is a column field, whose value is a Java Date object
TabularPivotField field = model.getField("Date");
field.setGroupHandler(new GroupHandler(){
	public Object getGroup(Object data) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy/MM");
		return format.format((Date) data);
	}
});
// now it converts the Date to a String of format "yyyy/MM" for comparison, so the data will be categorized by month

Version History

Version	Date	Content