Choose Fields in PivotEngine

By default, the PivotEngine creates fields automatically based on the raw data it gets from the itemsSource property.

In some cases, you may want to turn that feature off and specify the fields explicitly. For example, you may want to:

• Include only some of the fields available in the data source
• Customize the field properties,
• Create multiple fields based on the same binding (clone fields).

Clone Measure Fields

In some cases, you may want to summarize values using different aggregates. For example, you may want to show total and average values on the same view. Or you may want to show values next to calculated values such as running totals, differences, or percentages.

To do this, create multiple fields with the same binding and different aggregate or showAs values.

let ng = new wjOlap.PivotEngine({
  	autoGenerateFields: false, // turn off auto-generation
    fields: [ // specify the fields we want (no date)
    	{ binding: 'buyer', header: 'Person' },
    	{ binding: 'type', header: 'Category' },
    	{ binding: 'amount', header: 'Total', format: 'c0', aggregate: 'Sum' },
    	{ binding: 'amount', header: 'Average', format: 'c0', aggregate: 'Avg' },
    ],
    itemsSource: getData(10000), // raw data
    showRowTotals: 'Subtotals',
    valueFields: ['Total', 'Average'], // show total and average
    rowFields: ['Person', 'Category'] // by Person and Category
});

If you use a PivotPanel control, users may create clone fields by dragging the same source field to the values list multiple times.

Clone Dimension Fields

In some cases, you may want to use a dimension value in multiple ways. For example, you may want to break up the data according to a date's year and/or quarter.

To do this, create multiple fields with the same binding and different format values.

let ng = new wjOlap.PivotEngine({
  	autoGenerateFields: false, // turn off auto-generation
    fields: [ // specify the fields we want (no date)
    	{ binding: 'date', header: 'Year', format: 'yyyy', width: 80 },
    	{ binding: 'date', header: 'Quarter', format: '"Q"q', width: 80 },
    	{ binding: 'buyer', header: 'Person' },
    	{ binding: 'type', header: 'Category' },
    	{ binding: 'amount', header: 'Amount', format: 'c0', aggregate: 'Sum' },
    ],
    itemsSource: getData(10000), // raw data
    showRowTotals: 'Subtotals',
    valueFields: ['Amount'], // show amount spent
    rowFields: ['Year', 'Quarter'] // by Year and Quarter
  });

Deep Binding

You can bind PivotField objects to sub-properties of the data items.

For example, the data in this example below has an 'emotion' member that has four sub-properties. The PivotEngine has fields that bind to each one using binding strings 'emotion.happiness', 'emotion.fear', etc.

let theEngine = new wjOlap.PivotEngine({
    autoGenerateFields: false,
    fields: [
    	{ binding: 'country', header: 'Country', width: 90 }, 
        { binding: 'product', header: 'Product'},
        { header: 'Emotion', subFields: [
	        { binding: 'emotion.happiness', header: 'Happiness', dataType: 'Number' },
    	    { binding: 'emotion.surprise', header: 'Surprise', dataType: 'Number' },
  	        { binding: 'emotion.fear', header: 'Fear', dataType: 'Number' },
      	    { binding: 'emotion.disgust', header: 'Disgust', dataType: 'Number' }
		]}
	],
	rowFields: ['Product'],
	valueFields: ['Happiness', 'Surprise'],
    itemsSource: getData(1000)
  });