1
Help for ParamQuery Pro / Re: Group summary that is a formula
« on: July 11, 2018, 03:18:56 pm »
Thank you very much! This was exactly what I needed.
ParamQuery Grid
Show Posts
This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.
2
Help for ParamQuery Pro / Re: Group summary that is a formula
« on: July 11, 2018, 12:20:23 pm »
I have added a column to your demo at https://paramquery.com/pro/demos33/export
I added a column below but the result is empty because I did not define summary for that column. The summary function kicks in for the summary row and overwrites whatever was rendered. If summary is empty list then that cell is empty. If it is a sum, then it will be sum of the column.
{
title: "Product",
width: 120,
dataIndx: 'Product',
format: "##,###.00",
dataType: "float",
render: function(ui){
var rd = ui.rowData;
if( rd.pq_gsummary )
return rd.Freight * 2;
}
},
I added a column below but the result is empty because I did not define summary for that column. The summary function kicks in for the summary row and overwrites whatever was rendered. If summary is empty list then that cell is empty. If it is a sum, then it will be sum of the column.
{
title: "Product",
width: 120,
dataIndx: 'Product',
format: "##,###.00",
dataType: "float",
render: function(ui){
var rd = ui.rowData;
if( rd.pq_gsummary )
return rd.Freight * 2;
}
},
3
Help for ParamQuery Pro / Re: Group summary that is a formula
« on: July 11, 2018, 05:21:32 am »
What I need in the summary is not a sum but a formula involving the sum of other columns e.g. sum(ui.rowData.Freight) / sum(ui.rowData.Deposit)
4
Help for ParamQuery Pro / Re: Group summary that is a formula
« on: July 10, 2018, 12:27:54 pm »
Using this discussion on formula for Grand Summary, I was able to do it from Grand Summary at the bottom of the grid. However I need it for the Group Summary too.
https://paramquery.com/forum/index.php?topic=2122.msg8407
https://paramquery.com/forum/index.php?topic=2122.msg8407
5
Help for ParamQuery Pro / Group summary that is a formula
« on: July 10, 2018, 09:23:05 am »
I need to have a group summary that is not an aggregate function (which is based on data in that column) but a formula based on data in other columns e.g. val = rd.Freight / rd.Deposit
How can I acheive this functionality?
I am using ParamQuery Pro 3.4
How can I acheive this functionality?
I am using ParamQuery Pro 3.4
6
Help for ParamQuery Pro / Count summary on date column is not displaying correctly
« on: February 19, 2018, 03:34:32 pm »
I am referring to demo at https://paramquery.com/pro/demos33/group_rows
and I have count on 'Shipped Date' column with format "dd/M/yy"
The coding is shown below. And the output for some rows for 'Shipped Date' is:
Count: 01/Jan/1970
Looks like it is trying to convert the number into a date for some rows.
and I have count on 'Shipped Date' column with format "dd/M/yy"
The coding is shown below. And the output for some rows for 'Shipped Date' is:
Count: 01/Jan/1970
Looks like it is trying to convert the number into a date for some rows.
Code: [Select]
$(function () {
function fillOptions(grid) {
var column = grid.getColumn({ dataIndx: 'ShipCountry' });
column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
column.filter.cache = null;
grid.refreshHeader();
}
var colM = [
{ title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
filter: {
type: 'select',
prepend: { '': 'All Countries' },
valueIndx: 'ShipCountry',
labelIndx: 'ShipCountry',
condition: 'equal',
listeners: ['change']
}
},
{ title: "Customer Name", width: 130, dataIndx: "ContactName" },
{ title: "Freight", width: 120, format: '$##,###.00',
summary: {
type: "sum"
},
dataType: "float", dataIndx: "Freight"
},
{ title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date",
format: "dd/M/yy",
summary: {
type: "count"
},
},
{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
var dataModel = {
location: "remote",
dataType: "JSON",
method: "GET",
url: "/Content/orders.json"
//url: "/pro/orders/get",//for ASP.NET
//url: "orders.php",//for PHP
};
var groupModel = {
on: true,
dataIndx: ['ShipCountry'],
showSummary: [true], //to display summary at end of every group.
collapsed: [true],
title: [
"{0} ({1})",
"{0} - {1}"
]
};
var obj = {
height: 500,
toolbar: {
items: [{
type: 'button',
label: "Toggle grouping",
listener: function (evt) {
this.groupOption({
on: !grid.option('groupModel.on')
});
}
}]
},
dataModel: dataModel,
scrollModel: { autoFit: true },
colModel: colM,
numberCell: { show: false },
filterModel: { on: true, header: true, type: "local" },
selectionModel: { type: 'cell' },
groupModel: groupModel,
load: function (evt, ui) {
//options for ShipCountry filter.
fillOptions(grid);
},
showTitle: false,
resizable: true,
virtualX: true,
virtualY: true,
hwrap: false,
wrap: false
};
var grid = pq.grid("#grid_group_rows", obj);
});
7
Help for ParamQuery Pro / Re: How to do aggregate sum if numbers displayed are in European format?
« on: November 29, 2017, 05:42:34 am »I realize that I can do what I want. I must have done it wrongly. Thanks a lot.
I used your sample at https://paramquery.com/pro/demos/group_rows
This is the code:
Code: [Select]
$(function () {
function fillOptions(grid) {
var column = grid.getColumn({ dataIndx: 'ShipCountry' });
column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
column.filter.cache = null;
grid.refreshHeader();
}
var agg = pq.aggregate,
format = function(val){
return pq.formatNumber(val, "##.###,00");
}
//define custom aggregate "all" and now it can be used in any column.
agg.sum_max_min = function(arr, col){
return "Sum: " + format( agg.sum(arr, col) )
+", Max: " + format( agg.max(arr, col) )
+", Min: " + format( agg.min(arr, col) );
}
var colM = [
{ title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
filter: {
type: 'select',
prepend: { '': 'All Countries' },
valueIndx: 'ShipCountry',
labelIndx: 'ShipCountry',
condition: 'equal',
listeners: ['change']
}
},
{ title: "Customer Name", width: 130, dataIndx: "ContactName" },
{ title: "Freight", width: 120, format: '##.###,00',
summary: {
type: "sum_max_min"
},
dataType: "float", dataIndx: "Freight"
},
{ title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
var dataModel = {
location: "remote",
dataType: "JSON",
method: "GET",
wrap: true,
url: "/Content/orders.json"
//url: "/pro/orders/get",//for ASP.NET
//url: "orders.php",//for PHP
};
var groupModel = {
on: true,
summaryInTitleRow: 'all', //to display summary in the title row.
dataIndx: ['ShipCountry', 'ContactName'],
//showSummary: [true], //to display summary at end of every group.
title: [
"{0} ({1})",
"{0} - {1}"
]
};
var obj = {
height: 500,
toolbar: {
items: [{
type: 'button',
label: "Toggle grouping",
listener: function () {
this.Group().option({
on: !this.option('groupModel.on')
});
}
}]
},
dataModel: dataModel,
scrollModel: { autoFit: true },
colModel: colM,
numberCell: { show: false },
filterModel: { on: true, header: true, type: "local" },
selectionModel: { type: 'cell' },
groupModel: groupModel,
load: function (evt, ui) {
//options for ShipCountry filter.
fillOptions(this);
},
showTitle: false,
resizable: true,
virtualX: true,
virtualY: true,
hwrap: false,
wrap: true
};
pq.grid("#grid_group_rows", obj);
});
8
Help for ParamQuery Pro / Re: How to do aggregate sum if numbers displayed are in European format?
« on: November 28, 2017, 04:59:51 pm »
In your sample, I used your example at https://paramquery.com/pro/demos/group_rows_custom_summary
And the result of agg.sum(arr, col) is NaN
This is the whole coding
Code: [Select]
fnVal = pq.formatNumber( agg.sum(arr, col), col.format);
And the result of agg.sum(arr, col) is NaN
This is the whole coding
Code: [Select]
$(function () {
function fillOptions(grid) {
var column = grid.getColumn({ dataIndx: 'ShipCountry' });
column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
column.filter.cache = null;
grid.refreshHeader();
}
var agg = pq.aggregate;
agg.max1 = function(arr, col) {
var maxNum = 0;
if (col.dataType == "float" || col.dataType == "integer") {
var arr1 = [];
for (var i=0; i<arr.length; i++) {
var item = parseInt(arr[i]);
if (item == NaN) {item = 0;}
arr1.push(item);
}
maxNum = Math.max.apply(null, arr1);
}
if (col.dataType == "date") {
}
return pq.formatNumber(maxNum, col.format);
}
agg.sum_null = function(arr, col) {
return myAgg(arr, col, "sum");
}
function myAgg(arr, col, fnName) {
var ret = "";
var res = fnName.split("_");
for(var i=0; i<res.length; i++) {
txt = "";
if (i>0) {txt = "<BR>";}
var fn = res[i];
var fnVal = 0;
switch (fn) {
case "avg":
fnVal = pq.formatNumber( agg.avg(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Avg: " + fnVal;
break;
case "max":
if ( col.dataType == "date"){
var val1 = agg.max(arr, col);
var timestamp = Date.parse(val1);
var date1 = (isNaN(timestamp)) ? "" : new Date(val1);
txt += "Max: " + $.datepicker.formatDate( col.format, date1);
}
else{
txt += "Max: " + agg.max1(arr, col);
}
break;
case "min":
if ( col.dataType == "date"){
var val1 = agg.min(arr, col);
var timestamp = Date.parse(val1);
var date1 = (isNaN(timestamp)) ? "" : new Date(val1);
txt += "Min: " + $.datepicker.formatDate( col.format, date1);
}
else{
fnVal = pq.formatNumber( agg.min(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Min: " + fnVal;
}
break;
case "sum":
fnVal = pq.formatNumber( agg.sum(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Sum: " + fnVal;
break;
case "count":
txt += "count: " + agg.count(arr, col);
break;
case "var":
fnVal = pq.formatNumber( agg.sum(arr, col), col.format);
fnVal = isNaN(fnVal) ? "" : fnVal;
txt += "Variance: " + fnVal;
break;
}
ret += txt;
}
ret = ret.replace("undefined", "");
ret = ret.replace("NaN", "");
return ret;
}
var colM = [
{ title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
filter: {
type: 'select',
prepend: { '': 'All Countries' },
valueIndx: 'ShipCountry',
labelIndx: 'ShipCountry',
condition: 'equal',
listeners: ['change']
}
},
{ title: "Customer Name", width: 130, dataIndx: "ContactName" },
{ title: "Freight", width: 120, format: '$##.###,00',
summary: {
type: "sum_null"
},
dataType: "float", dataIndx: "Freight"
},
{ title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
var dataModel = {
location: "remote",
dataType: "JSON",
method: "GET",
url: "/Content/orders.json"
//url: "/pro/orders/get",//for ASP.NET
//url: "orders.php",//for PHP
};
var groupModel = {
on: true,
summaryInTitleRow: 'all', //to display summary in the title row.
dataIndx: ['ShipCountry', 'ContactName'],
//showSummary: [true], //to display summary at end of every group.
title: [
"{0} ({1})",
"{0} - {1}"
]
};
var obj = {
height: 500,
toolbar: {
items: [{
type: 'button',
label: "Toggle grouping",
listener: function () {
this.Group().option({
on: !this.option('groupModel.on')
});
}
}]
},
dataModel: dataModel,
scrollModel: { autoFit: true },
colModel: colM,
numberCell: { show: false },
filterModel: { on: true, header: true, type: "local" },
selectionModel: { type: 'cell' },
groupModel: groupModel,
load: function (evt, ui) {
//options for ShipCountry filter.
fillOptions(this);
},
showTitle: false,
resizable: true,
virtualX: true,
virtualY: true,
hwrap: false,
wrap: false
};
pq.grid("#grid_group_rows", obj);
});
9
Help for ParamQuery Pro / How to do aggregate sum if numbers displayed are in European format?
« on: November 28, 2017, 01:28:40 pm »
How to do aggregate sum if numbers displayed are in European format?
Is it possible that the column.format is applied but the array of numbers in aggregate would still have the numbers in their original form - NumberDecimal Separator = "." ?
I noticed all the numbers in the column have been changed to NumberDecimal Separator = "," or European format and agg.sum is gives me NaN (Not a number)
Is it possible that the column.format is applied but the array of numbers in aggregate would still have the numbers in their original form - NumberDecimal Separator = "." ?
I noticed all the numbers in the column have been changed to NumberDecimal Separator = "," or European format and agg.sum is gives me NaN (Not a number)
10
Help for ParamQuery Pro / Re: Need to get the record number range in current viewport
« on: October 06, 2017, 05:11:12 am »
Thank you very much for this undocumented method!
It now works well.
It now works well.
11
Help for ParamQuery Pro / Re: Need to get the record number range in current viewport
« on: October 04, 2017, 01:53:33 pm »
In the case of scrolling one record at a time, your description of initV in the API documentation is misleading. Is there a way to make initV behave as you described in the API documentation when scrolling one record at a time?
initV
Type: Integer
Index of first row displayed in the unfrozen viewport.
initV
Type: Integer
Index of first row displayed in the unfrozen viewport.
12
Help for ParamQuery Pro / Re: Need to get the record number range in current viewport
« on: October 04, 2017, 12:23:08 pm »
The user wants to see the number of records being displayed like when there is paging since the scrollbar position cannot tell more accurately which record number is being displayed.
So the finalV is not accurate but the initV can be used.
So the finalV is not accurate but the initV can be used.
13
Help for ParamQuery Pro / Re: Need to get the record number range in current viewport
« on: October 04, 2017, 10:14:41 am »
I have used your demo at https://paramquery.com/pro/demos/group_rows
The display of initV, finalV and total number of rows is shown in the grid title bar
I have enabled numberCell.
I am surprised that initially it shows 1 to 17 of 940 rows when what I actually see is only 1 to 13.
If I were to move the scroll to the top, it shows 1 to 15 of 940 rows which is not the actual rows I see.
I am using Google Chrome Version 61.0.3163.100 (Official Build) (64-bit) on Windows 10.
Also when I click on the scrollbar below the scroller - to get the next "page"
The title now shows 13 to 27 rows when what I actually see is only 13 to 25 rows.
Looks like there is a discrepancy here.
I have attached an image but the stipulation of less than 100 kB - I had to crop the image of the grid
The display of initV, finalV and total number of rows is shown in the grid title bar
I have enabled numberCell.
I am surprised that initially it shows 1 to 17 of 940 rows when what I actually see is only 1 to 13.
If I were to move the scroll to the top, it shows 1 to 15 of 940 rows which is not the actual rows I see.
I am using Google Chrome Version 61.0.3163.100 (Official Build) (64-bit) on Windows 10.
Also when I click on the scrollbar below the scroller - to get the next "page"
The title now shows 13 to 27 rows when what I actually see is only 13 to 25 rows.
Looks like there is a discrepancy here.
I have attached an image but the stipulation of less than 100 kB - I had to crop the image of the grid
Code: [Select]
$(function () {
var numDataRecords;
var gridTitle = "Order Details";
var gridDiv = "#grid_group_rows";
function fillOptions(grid) {
var column = grid.getColumn({ dataIndx: 'ShipCountry' });
column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
column.filter.cache = null;
grid.refreshHeader();
}
var colM = [
{ title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
filter: {
type: 'select',
prepend: { '': 'All Countries' },
valueIndx: 'ShipCountry',
labelIndx: 'ShipCountry',
condition: 'equal',
listeners: ['change']
}
},
{ title: "Customer Name", width: 130, dataIndx: "ContactName" },
{ title: "Freight", width: 120, format: '$##,###.00',
summary: {
type: "sum"
},
dataType: "float", dataIndx: "Freight"
},
{ title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
var dataModel = {
location: "remote",
dataType: "JSON",
method: "GET",
url: "/Content/orders.json"
//url: "/pro/orders/get",//for ASP.NET
//url: "orders.php",//for PHP
};
var groupModel = {
on: true,
summaryInTitleRow: 'all', //to display summary in the title row.
dataIndx: ['ShipCountry', 'ContactName'],
//showSummary: [true], //to display summary at end of every group.
title: [
"{0} ({1})",
"{0} - {1}"
]
};
var obj = {
height: 500,
toolbar: {
items: [{
type: 'button',
label: "Toggle grouping",
listener: function () {
this.Group().option({
on: !this.option('groupModel.on')
});
}
}]
},
dataModel: dataModel,
scrollModel: { autoFit: true },
colModel: colM,
numberCell: { show: true },
filterModel: { on: true, header: true, type: "local" },
selectionModel: { type: 'cell' },
groupModel: groupModel,
load: function (evt, ui) {
//options for ShipCountry filter.
fillOptions(this);
},
load: function( event, ui ) {
var data = this.options.dataModel.data;
numDataRecords = data.length
var title = gridTitle + " - " + numDataRecords + " records";
$(gridDiv).pqGrid( "option", "title", title );
},
beforeTableView: function( event, ui ) {
var initV = ui.initV + 1;
var finalV = ui.finalV + 1;
var numDataRows = (ui.pageData) ? ui.pageData.length : 0;
var title = gridTitle + " - " + initV + " to " + finalV + " of " + numDataRows + " rows";
$(gridDiv).pqGrid( "option", "title", title );
},
showTitle: true,
resizable: true,
virtualX: true,
virtualY: true,
hwrap: false,
wrap: false
};
pq.grid("#grid_group_rows", obj);
});
14
Help for ParamQuery Pro / Need to get the record number range in current viewport
« on: October 03, 2017, 12:30:24 pm »
I need to get the record number range in current viewport. When I use beforeTableView event, it gives me initV and finalV which are the starting row of viewport and ending row of viewport.
I have used your demo at https://paramquery.com/pro/demos/group_rows
You will see that the initV and finalV when scrolled right to the bottom will exceed the number of records = 830
The JavaScript code:
I have used your demo at https://paramquery.com/pro/demos/group_rows
You will see that the initV and finalV when scrolled right to the bottom will exceed the number of records = 830
The JavaScript code:
Code: [Select]
$(function () {
var numDataRecords;
var gridTitle = "Order Details";
var gridDiv = "#grid_group_rows";
function fillOptions(grid) {
var column = grid.getColumn({ dataIndx: 'ShipCountry' });
column.filter.options = grid.getData({ dataIndx: ['ShipCountry'] });
column.filter.cache = null;
grid.refreshHeader();
}
var colM = [
{ title: "ShipCountry", width: 120, dataIndx: "ShipCountry",
filter: {
type: 'select',
prepend: { '': 'All Countries' },
valueIndx: 'ShipCountry',
labelIndx: 'ShipCountry',
condition: 'equal',
listeners: ['change']
}
},
{ title: "Customer Name", width: 130, dataIndx: "ContactName" },
{ title: "Freight", width: 120, format: '$##,###.00',
summary: {
type: "sum"
},
dataType: "float", dataIndx: "Freight"
},
{ title: "Shipping Via", width: 130, dataIndx: "ShipVia" },
{ title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
{ title: "Shipping Address", width: 220, dataIndx: "ShipAddress" },
{ title: "Shipping City", width: 130, dataIndx: "ShipCity" }
];
var dataModel = {
location: "remote",
dataType: "JSON",
method: "GET",
url: "/Content/orders.json"
//url: "/pro/orders/get",//for ASP.NET
//url: "orders.php",//for PHP
};
var groupModel = {
on: true,
summaryInTitleRow: 'all', //to display summary in the title row.
dataIndx: ['ShipCountry', 'ContactName'],
//showSummary: [true], //to display summary at end of every group.
title: [
"{0} ({1})",
"{0} - {1}"
]
};
var obj = {
height: 500,
toolbar: {
items: [{
type: 'button',
label: "Toggle grouping",
listener: function () {
this.Group().option({
on: !this.option('groupModel.on')
});
}
}]
},
dataModel: dataModel,
scrollModel: { autoFit: true },
colModel: colM,
numberCell: { show: false },
filterModel: { on: true, header: true, type: "local" },
selectionModel: { type: 'cell' },
groupModel: groupModel,
load: function (evt, ui) {
//options for ShipCountry filter.
fillOptions(this);
},
complete: function( event, ui ) {
var data = this.options.dataModel.data;
numDataRecords = data.length
var title = gridTitle + " - " + numDataRecords + " records";
$(gridDiv).pqGrid( "option", "title", title );
},
beforeTableView: function( event, ui ) {
var initV = ui.initV + 1;
var finalV = ui.finalV + 1;
var title = gridTitle + " - " + initV + " to " + finalV + " of " + numDataRecords + " records";
$(gridDiv).pqGrid( "option", "title", title );
},
showTitle: true,
resizable: true,
virtualX: true,
virtualY: true,
hwrap: false,
wrap: false
};
pq.grid("#grid_group_rows", obj);
});
15
Help for ParamQuery Pro / Re: How to find which individual sub-group was collapsed?
« on: August 28, 2017, 01:00:29 pm »
Thank you for the information.