Mzw.tgrid: ext JS data pivoting table

Original address: http://www.sencha.com/blog/mzpivotgrid-a-pivot-table-for-ext-js? Mkt_tok = 3rkmmjwwff9wsrokvq3bzkxonjhpfsx77% 2 bsqwk % 2b0lmis % 3d % 3d

At mzsolutions, my job is to create components for ext JS and sencha touch. In this article, I will show you how to use mzw.tgrid in sencha ext Js.

What is a pivot table?

The grid panel of ext JS perfectly displays large datasets and provides many functions. However, it cannot display a type called Pivot table. When it comes to tables, you will use pivot tables, because they help you organize and summarize data and create reports, allowing you to make more informed decisions based on data.

What is mz1_tgrid?

Mzpivotgrid is a component that can be used in ext js to create a pivot table. You can imagine the following situations. Some sales data includes the following fields: country, salesperson, order date, order amoutnt, and order ID ). To edit the data, you can easily use gridpanel and use the cellediting or rowediting plug-in.

How to answer a series of questions:

1. What is the click amount of each salesperson?
2. What is the order amount for each country?
3. How do sales personnel perform operations within the specified year?

You can use mzpivotgrid to create these reports. The following figure shows their display results:

How to Use mzw.tgrid

Mzw.tgrid inherits from ext JS's grid panel component. Therefore, you can use familiar functions such as adjusting the column width, locking the column, cell Renderer, and row/cell events. When you need to use the aggregation and data pivoting functions not provided by gridpanel, you can use mzpivotgrid.

To break down a dataset Based on the salesperson and year, you only need to use the following code to set the axis on the top and left:

leftAxis: [{    width:      80,    dataIndex:  'salesperson',    header:     'Salesperson'}], topAxis: [{    dataIndex:  'year',    header:     'Year',    direction:  'ASC'}]

Because multiple layers are supported, you can specify them on the top or left axis. It is convenient to group rows or columns. Therefore, you can simply set enablegrouping to true.

Now we have split the dataset in the top and left axes to aggregate the values of cells. Available aggregation includes the following types: Sum, average, minimum, maximum, and count. If these cannot meet your needs, you can provide your own Aggregate functions.

aggregate: [{    measure:    'amount',    header:     'Sales',    aggregator: 'sum',    align:      'right',    width:      85,    renderer:   Ext.util.Format.numberRenderer('0,000.00')},{    measure:    'orderid',    header:     'Qnt',    aggregator: function(records, measure, matrix, rowGroupKey, colGroupKey) {        // do your own algorithm        return records.length;    },    align:      'right',    width:      85,    renderer:   Ext.util.Format.numberRenderer('0,000.00')}]

As you have noticed in the above example, you can aggregate multiple data fields based on sales and quantity. You can set all the desired sets to easily REDLINE them.

The setting of the rows or columns used to display the total rows (grand totals) is very simple ("enablerowgrandtotals: True" or "enablecolgrandtotals: True "). You can also enablerowsummary and enablecolsummary to generate a group Total (Group totals. Styles are easy to implement, even by defining the cell Renderer.

Pivot tables can use any type of store in ext JS, so it is quite easy to filter data in store objects. This means that a plug-in can be implemented to process filtering.

The data model class of ext JS is quite good. It can be used with its powerful convert function to expand the model and break down data within a custom grouping interval. For example:

fields: [{name: 'orderid',   type: 'int'},{name: 'salesperson',   type: 'string'},{name: 'country',   type: 'string'},{name: 'orderdate', type: 'date', dateFormat: 'd/m/Y'},{name: 'amount',    type: 'int'},{    name: 'person-range',    convert: function(v, record){            if(/^[a-j]/i.test(record.get('salesperson'))) return 'A-J';            if(/^[k-s]/i.test(record.get('salesperson'))) return 'K-S';            if(/^[t-z]/i.test(record.get('salesperson'))) return 'T-Z';        return v;    }},{    name: 'year',    convert: function(v, record){        return Ext.Date.format(record.get('orderdate'), "Y");    }}] 

The display effect is as follows:

Example:

 var pivotGrid = Ext.create('Mz.pivot.Grid', {    title:          'Pivot grid',    height:         400,    width:          600,    enableLocking:  false,    enableGrouping: true,    viewConfig: {        trackOver:      true,        stripeRows:     false    },    store: store,    aggregate: [{        measure:    'amount',        header:     'Sales',        aggregator: 'sum',        align:      'right',        width:      85,        renderer:   Ext.util.Format.numberRenderer('0,000.00')    }],    caption:  'Sales report',    leftAxis: [{        width:      80,        dataIndex:  'salesperson',        header:     'Sales Person'    }],    topAxis: [{        dataIndex:  'year',        header:     'Year',        direction:  'ASC'    }]});

Future

Because mzw.tgrid is a commercial product, it will continuously improve and have new plug-ins and functions. If you need some special implementations, contact us at any time.

To learn more about mzw.tgrid licensing, click here.

Additional resources:

• Using Grid example used in this article
• Try some more mzw.tgrid examples
• Mzw.tgrid API docs

Author:Adrian Teodorescu
Adrian Teodorescu has more than 15 years experience as a software developer and has been active in the sencha community since 2009. his main focus is creating components for ext JS and sencha touch. you can contact Adrian on his website