Modern day relational databases are able to perform some interesting feats like creating pivot tables with dynamic columns. I bet if you think about it for a minute you can figure out a million uses for data rolled up in a pivot table, so chances are you will be asked to present data from your database in this format sooner or later. The first question that comes to mind when you dynamically send columns back to the client side browser is, ‘Wow, how do I tell my model, and grid to adapt without knowing what is coming?’.  The following approach takes the MVC approach to coding a fully dynamic grid and model. I must admit I was really surprised by how easy Ext handles this series of complex actions, so here we go:

Create a model that has a fields property that is blank like this:

Ext.define('wcf.model.DataModel, {
extend: 'Ext.data.Model',
fields: []
});

Next create a store with the url that points to your server side script that will return the data. The important part to remember here is that autoLoad should be set to false, as we will be loading this store using an Ajax request later opposed to the usual store.load(). The other thing that is different here is that the sorters are critical in the store if you want to sort by some known column in your dataset like sales region as most database pivot functions will not allow an order by statement.

Ext.define('wcf.store. DataStore, {
    extend      : 'Ext.data.Store',
    model       : 'wcf.model. DataModel,
    sorters: [{
        property: ' sales_region,
        direction: 'ASC'
    }],
        proxy: {
        type: 'ajax',
        url: '/resources/cfms/data.cfm?event=sales_report',
        reader: {
            type: 'json',
            root: 'data'
        }
    },
    autoLoad: false
});

Next you have to create the json on your back end to comply with the following format (or you have to go out and do multiple data calls, but really what’s the point in that since one call would yield all of the information you need).

Pass the columns in an array in the Json making sure to provide the correct data index and header text, plus anything else you would like to add.

 'columns':[ {'dataIndex': 'something', 'text' : ' something' } ….. ]
 

Pass the model in an array in the same Json object

'fields':[{'name': 'something' } …. ]

Make sure you pass the data

  'data':[ {'something': 'else'}, …..]
 

Now that you have done all the hard work on the back end it’s time to connect your grid to the store and update the columns, model, and data.

var      store       = Ext.getStore('DataStore'),
      // automatic getter when using MVC, otherwise reference the
     // dynamic grid.
            grid        = this.getDataGrid();

        Ext.Ajax.request({
            url     : '/resources/cfms/data.cfm?event=sales_report',
            success: function(response){
                var data = Ext.decode(response.responseText);
                store.model.setFields(data.fields);
                grid.reconfigure(store, data.columns);
                store.loadRawData(data.data, false);
            },
            failure: function(response){
	console.log(response);
            }
        });

Notice here we set the model fields with the data that was in the json response using exts store.model.setFields() method (http://docs.sencha.com/ext-js/4-1/#!/api/Ext.data.Model-static-method-setFields). Next we reconfigured the grid with the columns from the json response using the reconfigure method of the grid and passing the store with the columns http://docs.sencha.com/ext-js/4-1/#!/api/Ext.grid.Panel-method-reconfigure. Finally, we don’t want to call our query again so let’s populate the data using the store.loadRawdata() method and indicating false to make sure we remove the existing records http://docs.sencha.com/ext-js/4-1/#!/api/Ext.data.Store-method-loadRawData.

Since the data does not load by itself using autoload I put the following in a function that is called on the grid’s activate event, but that is up to you, you may want to load it earlier rather than later.