Thursday, July 22, 2010

Table Pivoting using ExtJs




















Hello,

Recently, I have completed a project in which I have implemented Table Pivoting using ExtJs.
Please refer the above image. Here data will be displayed in a tabular format. Data will be fetched using real time Ajax Request. Axe locations are also stored in a database. For example, here year and country are the horizontal axes and gender, and area are the vertical axes. Axe location will be fetched from a database. Data will be stored in JSON object and axes location will be stored in JavaScript array.

User has all the freedom of changing location of axes and swapping of axes as can be seen from image below. As user change location of axes data will be automatically reordered as per new format.
















Furthermore, user can swap one axe with any axes from horizontal or vertical locations. Data will be reordered as per new format.

















Furthermore, user can save new layout so next time when a user views this report save format will be applied to table. This component was built to support a business report application. Where a user can view reports in different formats. User can save preferences to a database.

This is implemented using ExtJs panel with a table layout. All the pivoting functions are depended on only three things.

1) Data which is stored in JSON object.
2) Vertical and horizontal axes array.
3) Value axes name.

Using above three variables and ExtJs panel this complete component has been built.
Well, I cannot display complete code here. However, I will explain certain functions here.

How table is created?

This was the first challenge for me when I started this project. Because this is the code which is going to be more crucial for all the functions. When you use table layout in ExtJs panel, first you have to specify a number of columns. After this when you add items to the panel ExtJs will automatically position to correct location. For example, if you specify totalcolumns=2 and add three items to the panel. First two items will be on row 0 and third item will be in row one column 0.

So total number of columns is calculated using a number of vertical axes and total number of values in horizontal axes. After this ExtJs panel object is created. First of all, values of horizontal axes and vertical axes will be added in panel. Following is the code for it.

panel.add({height:40,html:uniqueHorizontalAxesValues[i][2][j],colspan:totalcolspan});

panel.add({height:40,html:uniqueVerticalAxesValues[i][2][j],colspan:totalcolspan});

With this all the necessary controls are added. Change and Save layout button, Column to Row/Row to Column buttons, swap axes split button, etc.

After these all controls are added, data will be added to the table.

How swapping and pivoting function works?

This is quite simple. All we have to do is just change arrays of vertical and horizontal axes and regenerate the table. For example, if a user chose to swap gender with area. Just remove gender from array and place area instead of it and remove area from array and place gender instead of it.

This is how this component is implemented. This is a completely reusable component. User can add this to any website easily. All you need to do is supply necessary data.

Hope this post helps.

3 comments:

  1. Hi Hiren,

    Plesae help me, I have a question regarding Pivot grids. I use ext js pivot grid, left axis is fit to content, but data rows didn't change their height. How you implement that?

    Thanks,
    Serine

    ReplyDelete
  2. Dear Serine

    Sorry for the late reply. I show your comment today. I implemented this before they have introduced pivot grid. This is I have done through custom JavaScript coding.

    ReplyDelete
  3. Hello Hiren,
    really helpful post..
    I am dynamically adding the contents(cell/row data) to the table layout. But I am not able to remove the components.Can you please help me?
    here is my code to remove old data and add new
    Ext.getCmp('chartPreviewLayout').removeAll();
    Ext.getCmp('chartPreviewLayout').doLayout();
    Ext.getCmp('chartPreviewLayout').add(eval(jsonData));
    Ext.getCmp('chartPreviewLayout').doLayout();

    ReplyDelete