jQuery UI Widgets Forums General Discussions JqxPivotGrid and json data

This topic contains 8 replies, has 5 voices, and was last updated by  jbrahy 4 months, 2 weeks ago.

Viewing 9 posts - 1 through 9 (of 9 total)
  • Author
  • JqxPivotGrid and json data #96830

    libibd
    Participant

    Hi,
    I’ve been waiting so long for the new PivotGrid.
    I followed your demo “JavaScript PivotGrid and Pivot Table Designer”, when ever i’m using array as local data everything is working like expected.
    but when ever I’m switching to json, I can see all data fields in the jqxPivotGridDesigner, but the Pivot grid is not loading.
    there is no error throwing, just a white background and nothing more – the pivot grid not shown.
    I’ve spent hours to figure out what am i missing but no luck yet…
    Can you please provide working example with json data instead of local array.
    Thanks

    JqxPivotGrid and json data #96867

    Hristo
    Participant

    Hello libibd,

    I tested this example below and it seems to work fine:

    <!DOCTYPE html>
    <html lang="en">
    <head>
        <title id="Description">JavaScript PivotGrid - Pivot Table Designer</title>
        <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
        <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script> 
        <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxinput.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxwindow.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxdragdrop.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxpivot.js"></script> 
        <script type="text/javascript" src="../../jqwidgets/jqxpivotgrid.js"></script>
        <script type="text/javascript" src="../../jqwidgets/jqxpivotdesigner.js"></script>
        <script type="text/javascript" src="../../scripts/demos.js"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                // prepare sample data
                var jsonData = [
                    { firstname: 'Andrew', lastname: 'Fuller', productname: 'Black Tea', price: "2.25", quantity: "10", total: "22.5" },
                    { firstname: 'Nancy', lastname: 'Davolio', productname: 'Green Tea', price: "1.5", quantity: "11", total: "16.5" },
                    { firstname: 'Shelley', lastname: 'Burke', productname: 'Caffe Espresso', price: "1.5", quantity: "11", total: "16.5" },
                    { firstname: 'Regina', lastname: 'Murphy', productname: 'Doubleshot Espresso', price: "1.5", quantity: "11", total: "16.5" },
                ];
                var source2 =
                    {
                        localdata: jsonData,
                        datatype: "json",
                        datafields:
                        [
                            { name: 'firstname', type: 'string' },
                            { name: 'lastname', type: 'string' },
                            { name: 'productname', type: 'string' },
                            { name: 'quantity', type: 'number' },
                            { name: 'price', type: 'number' },
                            { name: 'total', type: 'number' }
                        ]
                    };
    
                var dataAdapter2 = new $.jqx.dataAdapter(source2);
                dataAdapter2.dataBind();
                
                // create a pivot data source from the dataAdapter
                var pivotDataSource = new $.jqx.pivot(
                    dataAdapter2,
                    {
                        customAggregationFunctions: {                        
                            'var': function (values) {
                                console.log(1001);
    
                                if (values.length <= 1)
                                    return 0;
    
                                // sample's mean
                                var mean = 0;
                                for (var i = 0; i < values.length; i++)
                                    mean += values[i];
    
                                mean /= values.length;
    
                                // calc squared sum
                                var ssum = 0;
                                for (var i = 0; i < values.length; i++)
                                    ssum += Math.pow(values[i] - mean, 2)
    
                                // calc the variance
                                var variance = ssum / values.length;
    
                                return variance;
                            }
                        },
                        pivotValuesOnRows: false,
                        fields: [
                             { dataField: 'firstname', text: 'First Name' },
                             { dataField: 'lastname', text: 'Last Name' },
                             { dataField: 'productname', text: 'Product Name' },
                             { dataField: 'quantity', text: 'Quantity' },
                             { dataField: 'price', text: 'Price' },
                             { dataField: 'total', text: 'Total' }
                        ],
                        rows: [
                               { dataField: 'firstname', text: 'First Name' },
                               { dataField: 'lastname', text: 'Last Name' }
                        ],
                        columns: [{ dataField: 'productname', align: 'left' }],
                        filters: [
                            {
                                dataField: 'productname',
                                text: 'Product name',
                                filterFunction: function (value) {
                                    if (value == "Black Tea" || value == "Green Tea")
                                        return true;
    
                                    return false;
                                }
                            }
                        ],
                        values: [
                            { dataField: 'price', 'function': 'sum', text: 'Sum', align: 'left', formatSettings: { prefix: '$', decimalPlaces: 2, align: 'center' }, cellsClassName: 'myItemStyle', cellsClassNameSelected: 'myItemStyleSelected' },
                            { dataField: 'price', 'function': 'count', text: 'Count', className: 'myItemStyle', classNameSelected: 'myItemStyleSelected' }
                        ]
                    });
    
                var localization = { 'var': 'Variance' };
    
                // create a pivot grid
                $('#divPivotGrid').jqxPivotGrid(
                {
                    localization: localization,
                    source: pivotDataSource,
                    treeStyleRows: false,
                    autoResize: false,
                    multipleSelectionEnabled: true
                });
    
                var pivotGridInstance = $('#divPivotGrid').jqxPivotGrid('getInstance');
    
                // create a pivot grid
                $('#divPivotGridDesigner').jqxPivotDesigner(
                {
                    type: 'pivotGrid',
                    target: pivotGridInstance
                });
            });
        </script>
    </head>
    <body class='default'>
        <table>
            <tr>
                <td>
                    <div id="divPivotGridDesigner" style="height: 400px; width: 250px;">
                    </div>
                </td>
                <td>
                    <div id="divPivotGrid" style="height: 400px; width: 550px;">
                    </div>
                </td>
            </tr>
        </table>
        <div class="example-description">
            <br />
            <h2>Description</h2>
            <div style="width: 800px;">
                This is an example of a Pivot Grid and a Pivot Grid Designer. You can use the pivot grid designer
                to select which fields will be displayed on rows and columns. You can also select different value fields
                and choose an aggregation function. Simply drag and drop fields in the respective area and the pivot grid's
                content will update automatically. You can also click on the menu of each columns, rows, filters or values item
                and select additional options like text alignment, number formatting and numbers alignment.
            </div>
        </div>
    
    </body>
    </html>
    

    (it is based on this “Pivot grid designer” demo)

    Best Regards,
    Hristo Hristov

    jQWidgets team
    http://www.jqwidgets.com

    • This reply was modified 1 year, 11 months ago by  Hristo.
    JqxPivotGrid and json data #96920

    libibd
    Participant

    Hi Hristo,
    thank you for your response and example,

    When ever i set my data as a local json array everything is working like expected.
    The problem occurs when i’m receiving json data from remote data source and not as local data.

    like your example just like your example:

    var source2 = { localdata: jsonData,
    datatype: “json”,
    datafields: [ { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘number’ },
    { name: ‘??’, type: ‘number’ },
    { name: ‘??’, type: ‘number’ } ]
    };

    No Data loaded:
    var source2 = { url: “/???/????????”, type: “POST”,
    datatype: “json”, datafields: [ { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘string’ },
    { name: ‘??’, type: ‘number’ },
    { name: ‘??’, type: ‘number’ },
    { name: ‘??’, type: ‘number’ } ]
    };

    Can you please check this case again with remote json data?

    JqxPivotGrid and json data #97337

    Hristo
    Participant

    Hello libibd,

    I would like to suggest you implement the loadError(jqXHR, status, error) callback to the DataAdapter.
    On this way, we will check one of the possible reasons for that issue. Also, I would like to ask you to share with me if there has an error message.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    http://www.jqwidgets.com

    • This reply was modified 1 year, 10 months ago by  Hristo.
    JqxPivotGrid and json data #97477

    curibe
    Participant

    Hi,
    The same thing happened to me and I managed to find the solution.

    I got the data in a separate function to then pass it localdata. This my solution

    <script>
    $(document).ready(function() {
    ObtenerDatos();
    });

    function ObtenerDatos() {
    var url = “Home/DataHandler”;
    $.ajax({
    type: “POST”,
    url: url,
    success: function(data) {
    BindPivot(data);
    }
    });
    }

    function BindPivot(data)
    {
    var source =
    {
    localdata : data,
    datatype: “json”,
    datafields: [
    { name: “FirstName” }, { name: “LastName” }, { name: “Product” }, { name: “Price”, type: “float” },
    { name: “Quantity”, type: “int” }, { name: “Total”, type: “float” }
    ]
    };

    var dataAdapter = new $.jqx.dataAdapter(source);
    dataAdapter.dataBind();
    // create a pivot data source from the dataAdapter
    var pivotDataSource = new $.jqx.pivot(
    dataAdapter,
    {
    customAggregationFunctions: {
    ‘var’: function (values) {
    if (values.length <= 1)
    return 0;
    // sample’s mean
    var mean = 0;
    for (var i = 0; i < values.length; i++)
    mean += values[i];
    mean /= values.length;
    // calc squared sum
    var ssum = 0;
    for (var i = 0; i < values.length; i++)
    ssum += Math.pow(values[i] – mean, 2)
    // calc the variance
    var variance = ssum / values.length;
    return variance;
    }
    },
    pivotValuesOnRows: false,
    fields: [
    { dataField: ‘FirstName’, text: ‘Name’ },
    { dataField: ‘LastName’, text: ‘Last Name’ },
    { dataField: ‘Product’, text: ‘Product Name’ },
    { dataField: ‘Quantity’, text: ‘Quantity’ },
    { dataField: ‘Price’, text: ‘Price’ },
    { dataField: ‘Total’, text: ‘Total’ }
    ],
    rows: [
    { dataField: ‘FirstName’, text: ‘FirstName’ },
    { dataField: ‘LastName’, text: ‘LastName’ }
    ],
    columns: [{ dataField: ‘productname’, align: ‘left’ }],
    filters: [
    {
    dataField: ‘productname’,
    text: ‘Product name’,
    filterFunction: function (value) {
    if (value == “Black Tea” || value == “Green Tea”)
    return true;
    return false;
    }
    }
    ],
    values: [
    { dataField: ‘price’, ‘function’: ‘sum’, text: ‘Sum’, align: ‘left’, formatSettings: { prefix: ‘$’, decimalPlaces: 2, align: ‘center’ }, cellsClassName: ‘myItemStyle’, cellsClassNameSelected: ‘myItemStyleSelected’ },
    { dataField: ‘price’, ‘function’: ‘count’, text: ‘Count’, className: ‘myItemStyle’, classNameSelected: ‘myItemStyleSelected’ }
    ]
    });
    var localization = { ‘var’: ‘Variance’ };
    // create a pivot grid
    $(‘#divPivotGrid’).jqxPivotGrid(
    {
    localization: localization,
    source: pivotDataSource,
    treeStyleRows: false,
    autoResize: false,
    multipleSelectionEnabled: true
    });
    var pivotGridInstance = $(‘#divPivotGrid’).jqxPivotGrid(‘getInstance’);
    // create a pivot grid
    $(‘#divPivotGridDesigner’).jqxPivotDesigner(
    {
    type: ‘pivotGrid’,
    target: pivotGridInstance
    });
    }

    </script>
    }

    JqxPivotGrid and json data #97486

    Hristo
    Participant

    Hello curibe,

    Thank you for sharing your experience with the community.

    Best Regards,
    Hristo Hristov

    jQWidgets team
    http://www.jqwidgets.com

    JqxPivotGrid and json data #105141

    jbrahy
    Participant

    Any update on this? I’m seeing the same problem with loading remote json and jqxPivot. The workaround of remotely pulling the data and then putting it into a local array works but I was just hoping for a fix to the issue.

    JqxPivotGrid and json data #105150

    Peter Stoev
    Keymaster

    You can bind to json, just set async: false in the data source options. The pivot designer internally works with arrays so if it is async operation, it will not know that data is expected at some point.

    Best Regards,
    Peter Stoev

    jQWidgets Team
    https://www.jqwidgets.com

    JqxPivotGrid and json data #105172

    jbrahy
    Participant

    Thanks!

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.