jQWidgets Forums

Tagged: PIvotGrid Datasource

This topic contains 4 replies, has 2 voices, and was last updated by admin 4 years, 5 months ago.

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

Author

PivotGrid: Load JSON Data Posts
November 20, 2020 at 10:52 am PivotGrid: Load JSON Data #113654

Michael Gross
Participant

I have JSON Data, which are provided by a dataadapter. THe pivotGrid works fine, as long as I use localdata. As soon as I implement the JSON dataadapter, the pivot will not load. I took your code example and replaced only the source:
`<!DOCTYPE html>
<html lang=”en”>
<head>
<title id=’Description’>JavaScript PivotGrid – Pivot Table Designer</title>
<link rel=”stylesheet” href=”/js/jqwidgets-ver10.1.6/jqwidgets/styles/jqx.base.css” type=”text/css” />
<meta http-equiv=”X-UA-Compatible” content=”IE=edge,chrome=1″ />
<meta name=”viewport” content=”width=device-width, initial-scale=1 maximum-scale=1 minimum-scale=1″ />
<link rel=”stylesheet” href=”/js/jqwidgets-ver10.1.6/jqwidgets/styles/jqx.light.css” type=”text/css” />
<script type=”text/javascript” src=”/js/jquery-1.11.1.min.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxcore.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxdata.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxbuttons.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxcheckbox.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxinput.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxscrollbar.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxmenu.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxwindow.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxlistbox.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxdropdownlist.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxdragdrop.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxpivot.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxpivotgrid.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/jqwidgets/jqxpivotdesigner.js”></script>
<script type=”text/javascript” src=”/js/jqwidgets-ver10.1.6/scripts/scripts/demos.js”></script>
<script type=”text/javascript”>
$(document).ready(function () {

// create a data source and data adapter
var source =
{
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’ }
],
url: “http://application.dabac.com/portal/dbz.rpt/get-data.p”,
data: {
mandant: ‘Gesamt’,
jahr: ‘2020’,
monat: ‘1’,
typ: ‘WGRPIVOT’ }
};
var dataAdapter = new $.jqx.dataAdapter(source, {
loadComplete: function (records) {
var records = dataAdapter.records;
var length = records.length;
console.log(“Anzahl Datensätze: ” + length );
// loop through the records and display them in a table.
var html = “<table border=’1′><tr><th align=’left’>First Name</th><th align=’left’>Last Name</th><th align=’left’>Product</th><th align=’right’>Price</th><th align=’right’>Quantity</th></tr>”;
for (var i = 0; i < length; i++) {
var record = records[i];
html += “<tr>”;
html += “<td>” + record.firstname + “</td>”;
html += “<td>” + record.lastname + “</td>”;
html += “<td>” + record.productname + “</td>”;
html += “<td>” + dataAdapter.formatNumber(record.price, ‘c2’) + “</td>”;
html += “<td>” + dataAdapter.formatNumber(record.quantity, ‘n’) + “</td>”;
html += “</tr>”;
}
html += “</table>”;
$(“#table”).html(html);
},
loadError: function(jqXHR, status, error)
{
},
beforeLoadComplete: function (records) {

}
});

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: ‘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
});
});
</script>
</head>
<body class=’default’>
<table>
<tr>
<td>
<div id=”divPivotGrid” style=”height: 700px; width: 750px;”>
</div>
</td>
</tr>
</table>
<div class=”example-description”>
<br />
<h2>Description</h2>
<div style=”width: 800px;” id=”table”>
Loading…
</div>
</div>
</body>
</html>`
What is going wrong?

November 21, 2020 at 2:06 pm PivotGrid: Load JSON Data #113660

admin
Keymaster

Hi Michael,

As far as I see, the pivotDataSource is initialized before the data in the data adapter is loaded. To resolve this, add async: false in your “source” object.

Hope this helps.

Best regards,
Peter Stoev

jQWidgets Team
https://www.jqwidgets.com/

November 23, 2020 at 4:44 pm PivotGrid: Load JSON Data #113672

Michael Gross
Participant

Thank you Peter, this was the solution – it works just great!
Best Regards
Michael

November 25, 2020 at 6:52 am PivotGrid: Load JSON Data #113689
Michael Gross
Participant
Hi Peter,1 more comment on the Pivot Designer: The injection of the classes for the Designer-Object has an error:
class="jqx-rc-all jqx-widget jqx-pivotgrid <strong>jqx-designer-hostjqx-widget-content</strong>"
jqx-designer-host and jqx-widget-content have no space in between and are not active.
I have tried to position the Pivot Designer relative to a table cell, which didn’t work as expected:
```
<table id'someTable><tr><td>Some Conetent</td></tr></table>
<table class='pivotTable' id='pivotTable' width='100%' border='0'>
  <TR>    <td><div id='divPivotGridDesigner' class='pivotDesigner'></div>    </td>
    <td><div id='divPivotGrid' class='pivotGrid'></div>    </td></TR>
</table>
```
The Designer Object is placed always outside of the table cell.
Best Regards
Michael
November 26, 2020 at 7:06 am PivotGrid: Load JSON Data #113698

admin
Keymaster

Hi Michael,

We will take a look at this. Thanks for the feedback.

Best regards,
Peter Stoev

jQWidgets Team
https://www.jqwidgets.com/
Author

Posts

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

You must be logged in to reply to this topic.