Skip to content

Transposed grid


As of version 5.7 Guriddo jqGrid included a method to transpose data which lets you to rotate the data from rows to columns.

For example, if your data looks like this

Screenshot

The Transpose method will rearrange the table such that the Quarters are showing in the column headings and the Sales Regions can be seen on the left, like this:

Screenshot

Using jqTranspose

jqTranspose inherits the jqGrid, so you can use most of the features you are already familiar with: column re-sizing, formatter, cell renders, row/cell events etc. The jqTranspose can be used when you want to rotate the columns to rows in the jqGrid.

To use the method you will need only to specify the data source and optionally grid parameters.

Currently the pivot grid uses JSON data only as data source.

Configuration

jqTranspose has the following calling convention

jQuery("#grid").jqGrid('jqTranspose', data, transposeopt, gridoptions, ajaxoptions);

Where

  • #grid is the id of the table element as used in the jqGrid.

It is not needed to call first jqGrid to build the table. It is needed to call jqTranspose method. This method internally reconstruct the data and creates automatically jqGrid with predefined colModel.

When called the jqTranspose method automatically set the datatype to local and all future calculations are done at client side.

Bellow is description of all parameters

data

Commonly the method uses currently only JSON data as data source. The data parameter can be:

  • array
  • string

When the data is array jqTranspose expect the data in a name:value pair like in the following example structure :

[
    { "Sales" : "Q1" , "Europe" : "21300", "Asia" : "3200", "NorthAmerica" : "4522" },
    { "Sales" : "Q2" , "Europe" : "11300", "Asia" : "3100", "NorthAmerica" : "4032" },
    { "Sales" : "Q3" , "Europe" : "31300", "Asia" : "3300", "NorthAmerica" : "4622" },
    { "Sales" : "Q4" , "Europe" : "41300", "Asia" : "3500", "NorthAmerica" : "4122" }
...
]

In case the parameter is a string a ajax request is made. The data that is returned from the server should have a name value pair like this:

{"rows":[
    { "Sales" : "Q1" , "Europe" : "21300", "Asia" : "3200", "NorthAmerica" : "4522" },
    { "Sales" : "Q2" , "Europe" : "11300", "Asia" : "3100", "NorthAmerica" : "4032" },
    { "Sales" : "Q3" , "Europe" : "31300", "Asia" : "3300", "NorthAmerica" : "4622" },
    { "Sales" : "Q4" , "Europe" : "41300", "Asia" : "3500", "NorthAmerica" : "4122" }
...
]}

The rows property name can be configured from the ajaxoptions - see below.

The method expect all the needed data to be passed to it. This means that no other special handling on server should be done. All other next transformations are done at client side and the jqPivotGrid build its new data to be displayed.

transposeopt

This is a object with following properties and default values.

Property Type Description Default
nameprefix string Since the method build automatically the column model (colModel) this is the prefix for the created name in colModel + index. col
labelprefix string This is the string with which start the label in colModel + the index. This is true only if the parameter RowAsHeader is set to false. See below. value
baseindex integer Describes which index from the source row data will be used to build the colModel. Change this parameter if your rowdata has diffrent length - in this case use the row with the biger number of columns 0
beforeCreateGrid function This function, if defined, is executed immediate after the source data is transformed and before the creation of the grid. Two parameters are passed to this event - the first is the transformed data and the second is the source data. The first parameter is a object with two properties - colModel and rows. colModel contain the automatically build column model and the rows contain the transformed data. These parameter can be changed as well before the cretion of the grid. null
loadMsg boolean If set to true a loading message will appear during the building of the transposed grid. false
RowAsHeader mixed This parameter describes which row from the transformed data will be set as headers in the grid. Note that if this parameter is set (zero based index) - i.e >=0 then it will be deleted from the transformed data and will be put as header. Value of false will disable this beahaviour and the header will be build as described in labelprefix or eventually using the beforeCreateGrid event. 0
excludeSrcCols array Array list containing which column(s) from the source data should be not take in account when the transformation is build ed empty

Note

When build the new data, as we say in options above, we use nameprefix to create the propery name columns. By default this is name col. This mean that the new rows data will have the following columns col1, col2,...,coln. The only exception is the first column. The name of the first column is always col_name (it is get from the source data). Actually the final colums in the row are as follow col_name, col1,...,coln

gridoptions

These options are options of the grid. You can set any of them.

ajaxoptions

When the data is obtained via ajax we can set additional ajax options. Note that currently we support only JSON data, so some ajax options like dataType is not recommended to be changed. Again with this we have a reader option which defines the root data. By example if the data that is returned from the server has the following structure:

{"root":[
    { "Sales" : "Q1" , "Europe" : "21300", "Asia" : "3200", "NorthAmerica" : "4522" },
    { "Sales" : "Q2" , "Europe" : "11300", "Asia" : "3100", "NorthAmerica" : "4032" },
    { "Sales" : "Q3" , "Europe" : "31300", "Asia" : "3300", "NorthAmerica" : "4622" },
    { "Sales" : "Q4" , "Europe" : "41300", "Asia" : "3500", "NorthAmerica" : "4122" }
...
]}

In order to correctly read this data, we need to define the reader property like this:

jQuery("#grid").jqGrid('jqTranspose',
   "jsondataurl.json",
   {transposeopt},
   {gridoptions},
   {
     reader:"root"
   }
);

Example

With this example we will try to explain and build a grid from source data to final transposed one. Now - lets go first with the data.

Source data

Let’s say that we have a table with sales data by continents with the following fields:

  • sales period (quarter)
  • Europe
  • Asia
  • North America

This can be seen on the picture below:

Screenshot

This grid view is achieved with jqGrid with the following code:

jQuery(document).ready(function(){
...
$("#grid").jqGrid({
    datatype : 'local',
    data: matrix,
    colModel : [
        {name : "Sales"},
        {name : "Europe"},
        {name : "Asia"},
        {name : "NorthAmerica"}
    ],
    sortname : "Sales",
    pager : "gridPager"
});
 ...
});

where matrix data parameter is array with the following values:

matrix = [
    { "Sales" : "Q1" , "Europe" : "21300", "Asia" : "3200", "NorthAmerica" : "45222" },
    { "Sales" : "Q2" , "Europe" : "11300", "Asia" : "3200", "NorthAmerica" : "45222" },
    { "Sales" : "Q3" , "Europe" : "31300", "Asia" : "3200", "NorthAmerica" : "45222" },
    { "Sales" : "Q4" , "Europe" : "41300", "Asia" : "3200", "NorthAmerica" : "45222" }
];

Let say that your boss do not like this presentation and want to see clear the diffrence by Quarters by regions. To make your boss happy you can simple use jqTranspose to convert the inforamtions as requested. You can simple do:

$("#grid").jqGrid('jqTranspose',
    matrix,
    {},
    {
        pager:"gridPager",
    });

and the result will be as seen in the picture below:

Screenshot

but there is a little problem - the text NorthAmerica is not correctly displayed since it is converted from property which should not contain space. What to do to fix this? - it is easy - we can use beforeCreateGrid event. We know that the first column has a name col_name (see the note above) and using this we can find the needed text and fix it. Here is the code:

$("#grid").jqGrid('jqTranspose',
    matrix,
    {
        beforeCreateGrid : function( result, source) {
            var res = result.rows;
            for(var i=0;i<res.length; i++) {
                if(res[i]['col_name'] === 'NorthAmerica') {
                    res[i]['col_name'] = 'North America';
                }
            }
        }
    },
    {
        pager:"gridPager",
    });