TreeGrid with Nested JSON

[PabloLMartinezParticipant]

Hi,

I have a problem to build a TreeGrid, first I can not map well the elements on the JSON object to take the values and second I need to build columns dynamically.

I will share the code with you guys:

var objectTree = {
        "metrics": [
            {
                "id": 1, "name": "P&L", "period_type": "QUARTER", "denomination_type": "MILLIONS", "currency": "USD",
                "rows": [
                    { "id": 1, "name": "Revenue", "type": "numeric",
                        "periods": [
                            {"id": 1, "month": "", "year": "2015", "quarter": "1", "value": "5449000"},
                            {"id": 5, "month": "", "year": "2015", "quarter": "2", "value": "10367000"},
                            {"id": 2, "month": "", "year": "2015", "quarter": "3", "value": "6307000"},
                            {"id": 3, "month": "", "year": "2015", "quarter": "4", "value": "7509000"},
                            {"id": 4, "month": "", "year": "2016", "quarter": "1", "value": "8862000"}
                        ]
                    },
                    { "id": 2, "name": "Cost of Revenue", "type": "numeric",
                        "periods": [
                            {"id": 6, "month": "", "year": "2015", "quarter": "1", "value": "900000" },
                            {"id": 10, "month": "", "year": "2015","quarter": "2", "value": "1633000"},
                            {"id": 7, "month": "", "year": "2015", "quarter": "3", "value": "1019000"},
                            {"id": 8, "month": "", "year": "2015", "quarter": "4", "value": "1291000"},
                            {"id": 9,"month": "","year": "2016", "quarter": "1","value": "1573000"}
                        ]
                    },
                    {"id": 3,"name": "Gross Profit", "type": "numeric", "formula": "",
                        "periods": [
                            { "id": 41, "month": "", "year": "2015", "quarter": "1", "value": "" },
                            { "id": 65, "month": "", "year": "2015", "quarter": "2", "value": "" },
                            { "id": 47, "month": "", "year": "2015", "quarter": "3", "value": "" },
                            { "id": 53, "month": "", "year": "2015", "quarter": "4", "value": "" },
                            { "id": 59, "month": "", "year": "2016", "quarter": "1", "value": "" }
                        ]
                    }
                ]
            }
        ]
    };

var source =
    {
        datatype: "json",
        datafields: [
            { name: "name", map: "name" },
            { name: "year", map: 'metrics>0>rows>0>periods>0>year' } // trying to took the value of the year
        ],
        localData: objectTree,
        hierarchy: {root: "rows>periods"},
        id: "id"
    };

var dataAdapter = new $.jqx.dataAdapter(source);

    $("#treeGrid-pl").jqxTreeGrid(
        {
            width: '90%',
            pageable: false,
            source: dataAdapter,
            showAggregates: true,
            showSubAggregates: true,
            aggregatesHeight: 70,
            sortable: true,
            ready: function()
            {
                $("#treeGrid-pl").jqxTreeGrid('expandRow', '34');
            },
            columns:
                [

                    { text: objectTree.name, cellclassname: 'first-column', datafield: 'name', width: '20%' },
                    { text: 'year', datafield: 'year', width: '10%' }

                ]
        });

How can I build columns with the year and the quarter values dynamically?

Thanks

[ChristopherParticipant]

Hi PabloLMartinez,

I fixed your mapping issue, but the JSON array that you’re using can’t show all of the periods for each rows, because the structure of the JSON doesn’t allow that. The way the JSON is now, you can only show a particular record of the periods( in the example below, the first).
Here is how to map the JSON:

var objectTree = {
                "metrics": [
                    {
                        "id": 1, "name": "P&L", "period_type": "QUARTER", "denomination_type": "MILLIONS", "currency": "USD",
                        "rows": [
                            {
                                "id": 1, "name": "Revenue", "type": "numeric",
                                "periods": [
                                    { "id": 1, "month": "", "year": "2015", "quarter": "1", "value": "5449000" },
                                    { "id": 5, "month": "", "year": "2015", "quarter": "2", "value": "10367000" },
                                    { "id": 2, "month": "", "year": "2015", "quarter": "3", "value": "6307000" },
                                    { "id": 3, "month": "", "year": "2015", "quarter": "4", "value": "7509000" },
                                    { "id": 4, "month": "", "year": "2016", "quarter": "1", "value": "8862000" }
                                ]
                            },
                            {
                                "id": 2, "name": "Cost of Revenue", "type": "numeric",
                                "periods": [
                                    { "id": 6, "month": "", "year": "2015", "quarter": "1", "value": "900000" },
                                    { "id": 10, "month": "", "year": "2015", "quarter": "2", "value": "1633000" },
                                    { "id": 7, "month": "", "year": "2015", "quarter": "3", "value": "1019000" },
                                    { "id": 8, "month": "", "year": "2015", "quarter": "4", "value": "1291000" },
                                    { "id": 9, "month": "", "year": "2016", "quarter": "1", "value": "1573000" }
                                ]
                            },
                            {
                                "id": 3, "name": "Gross Profit", "type": "numeric", "formula": "",
                                "periods": [
                                    { "id": 41, "month": "", "year": "2015", "quarter": "1", "value": "" },
                                    { "id": 65, "month": "", "year": "2015", "quarter": "2", "value": "" },
                                    { "id": 47, "month": "", "year": "2015", "quarter": "3", "value": "" },
                                    { "id": 53, "month": "", "year": "2015", "quarter": "4", "value": "" },
                                    { "id": 59, "month": "", "year": "2016", "quarter": "1", "value": "" }
                                ]
                            }
                        ]
                    }
                ]
            };

            var source =
                {
                    datatype: "json",
                    datafields: [
                        { name: "name", type: 'string' },
                        { name: "year", type: 'number', map: 'periods>0>year' }, // trying to take the value of the year
                        { name: "quarter", type: 'string', map: 'periods>0>quarter' },
                    ],
                    localData: objectTree,
                    root: "metrics>0>rows",
                    //record:
                    id: "id"
                };

            var dataAdapter = new $.jqx.dataAdapter(source);

            $("#treeGrid-pl").jqxTreeGrid(
                {
                    width: '90%',
                    pageable: false,
                    source: dataAdapter,
                    showAggregates: true,
                    showSubAggregates: true,
                    aggregatesHeight: 70,
                    sortable: true,
                    ready: function () {
                        $("#treeGrid-pl").jqxTreeGrid('expandRow', '34');
                    },
                    columns:
                        [

                            { text: 'name', cellclassname: 'first-column', datafield: 'name', width: '20%' },
                            { text: 'year', datafield: 'year', width: '10%' },
                        { text: 'Quarter', datafield: 'quarter', width: '10%' }

                        ]
                });

What exactly do you mean by “building columns dynamically” ? Please clearify that point so we can propose a solution.

Best Regards,
Christopher

jQWidgets Team
http://www.jqwidgets.com

[PoojaGumalwarParticipant]

Hi Christopher,

{
“ouId”: 1,
“ouCode”: “AM”,
“ouLevels”: 1,
“ouName”: “Area Managements”,
“createdBy”: {
“userId”: 1,
“email”: “aditi@gmail.com”,
“firstName”: “Aditi”,
“lastName”: “D”
},
“backupReportTo”: {
“userId”: 1,
“email”: “aditi@gmail.com”,
“firstName”: “Aditi”,
“lastName”: “D”
},
“reportTo”: {
“userId”: 2,
“email”: “yuga@gmail.com”,
“firstName”: “Yuga”,
“lastName”: “K”
},
“parentOU”: null,
“children”: [
{
“ouId”: 4,
“ouCode”: “AdM”,
“ouLevels”: 1,
“ouName”: “Admin Management”,
“createdBy”: {
“userId”: 2,
“email”: “yuga@gmail.com”,
“firstName”: “Yuga”,
“lastName”: “K”
},
“backupReportTo”: {
“userId”: 3,
“email”: “abhijeet@gmail.com”,
“firstName”: “Abhijeet”,
“lastName”: “D”
},
“reportTo”: {
“userId”: 4,
“email”: “neha@gmail.com”,
“firstName”: “neha”,
“lastName”: “j”
},
“parentOU”: 1,
“children”: [],
“parent”: null
},
{
“ouId”: 22050,
“ouCode”: “SM”,
“ouLevels”: 1,
“ouName”: “siddhatech Managements”,
“createdBy”: {
“userId”: 4,
“email”: “neha@gmail.com”,
“firstName”: “neha”,
“lastName”: “j”
},
“backupReportTo”: {
“userId”: 1,
“email”: “aditi@gmail.com”,
“firstName”: “Aditi”,
“lastName”: “D”
},
“reportTo”: {
“userId”: 4,
“email”: “neha@gmail.com”,
“firstName”: “neha”,
“lastName”: “j”
},
“parentOU”: 1,
“children”: [
{
“ouId”: 2,
“ouCode”: “DM”,
“ouLevels”: 1,
“ouName”: “Division Management”,
“createdBy”: {
“userId”: 4,
“email”: “neha@gmail.com”,
“firstName”: “neha”,
“lastName”: “j”
},
“backupReportTo”: {
“userId”: 4,
“email”: “neha@gmail.com”,
“firstName”: “neha”,
“lastName”: “j”
},
“reportTo”: {
“userId”: 1,
“email”: “aditi@gmail.com”,
“firstName”: “Aditi”,
“lastName”: “D”
},
“parentOU”: 22050,
“children”: [],
“parent”: null
}
],
“parent”: null
}
],
“parent”: null
}

This is my response I want reportTo,backupReportTo and createdBy show to treegrid
this.source =
{
dataType: ‘json’,
datafields: [
{ name: ‘ouName’ },
{ name: ‘ouCode’ },
// { name: ‘reportTo’, map: ‘reportTo>firstName’ },
{ name: ‘isDisabled’ },
{ name: ‘children’ },
{ name: ‘expanded’ },
{ name: “reportTo”, type: “array”, map:”reportTo>firstName” },
],
hierarchy:
{
root: ‘children’
},
id: ‘ouId’,
localData: this.ouList
};

this.dataAdapter = new jqx.dataAdapter(this.source);

this.columns =
[
{ text: ‘Name’, dataField: ‘ouName’, width: “350px” },
{ text: ‘Code’, dataField: ‘ouCode’, width: “130px” },
{ text: ‘Is Disable’, dataField: ‘isDisabled’, width: “110px” },
{ text: ‘Report To’, dataField: ‘reportTo’,width: “250px” },
{
text: ‘Action’,width: “110px”, cellsAlign: ‘center’, align: “center”, columnType: ‘none’, editable: false, sortable: false, dataField: null, cellsRenderer: function (row, column, value) {
// render custom column.
return “<button data-row='” + row + “‘ class=’editButtons fa fa-edit custom-fa’ (click)=’UpdateButton(oudata)’></button><i margin-left: 15px;’ data-row='” + row + “‘ class=’fa fa-trash-o custom-fa’ (click)=’DeleteButton(oudata)’></i>”;
}
}
];

On first reportTo proper data coming but on children data not getting properly.[Object Object] shows in tablegrid.

[ChristopherParticipant]

Hi PoojaGumalwar,

In order to properly visualize your data you have to use cellsRenderer on the Report To column because in the case where it’s a sub-property of children the map to the firstName property of reportTo is different. Here’s how your ‘columns’ variable should look:

  this.columns =
                [
                    { text: 'Name', dataField: 'ouName', width: "350px" },
                    { text: 'Code', dataField: 'ouCode', width: "130px" },
                    { text: 'Is Disable', dataField: 'isDisabled', width: "110px" },
                    {
                        text: 'Report To', dataField: 'reportTo', width: "250px",
                        cellsRenderer: function (rowKey, column, cellValue, rowData, cellText) {
                            if (rowData.level !== 0 && typeof cellText === 'object') {
                                return cellText.firstName;
                            }

                            return cellText;
                        }
                    },
                    {
                        text: 'Action', width: "110px", cellsAlign: 'center', align: "center", columnType: 'none', editable: false, sortable: false, dataField: null, cellsRenderer: function (row, column, value) {
                            // render custom column.
                            return "<button data-row='" + row + "' class='editButtons fa fa-edit custom-fa' (click)='UpdateButton(oudata)'></button><i margin-left: 15px;' data-row='" + row + "' class='fa fa- trash - o custom- fa' (click) ='DeleteButton(oudata) '></i >";
                        }
                    }
                ];

Best Regards,
Christopher

jQWidgets Team
http://www.jqwidgets.com

[Author]

Posts