jQuery UI Widgets › Forums › General Discussions › JqxPivotGrid and json data
Tagged: #pivotgrid, javascript pivotgrid, jquery pivotgrid, jqwidgets pivotgrid, jqxpivotgrid, json data
This topic contains 8 replies, has 5 voices, and was last updated by jbrahy 5 years, 8 months ago.
-
Author
-
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.
ThanksHello 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 HristovjQWidgets team
http://www.jqwidgets.comHi 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?
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 HristovjQWidgets team
http://www.jqwidgets.comHi,
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>
}Hello curibe,
Thank you for sharing your experience with the community.
Best Regards,
Hristo HristovjQWidgets team
http://www.jqwidgets.comAny 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.
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 StoevjQWidgets Team
https://www.jqwidgets.comThanks!
-
AuthorPosts
You must be logged in to reply to this topic.