Sort By Pivot row and column labels

[mevans5415Participant]

Is it possible to sort a jqxpivotgrid by row and/or column names.
Example,
Pivot column = LedgerType which includes 4 transaction types (Charge, Adjustment, Payment, Transfer)
Pivot value = Amount
Pivot row = PostedDate
So I imagine the report to look like the below example where it is sorted in ascending order by row (1st) then column(2nd) and not the actual $ dollar amounts. Also including the function called to generate the report:

Adjustment Charge Payment Transfer
PostedDate
2020-09-01 (50.00) 100.00 (35.00) (15.00)
2020-09-02 (20.00) 200.00 (145.00) (35.00)
2020-09-03 (40.00) 250.00 (135.00) (75.00)

<script type=”text/javascript”>
$(document).ready(function () {
$(“#jqxButtonMainReport”).click(function () {
var v = performValidation();
if (v == true) {
jqxreportfilterexpandercollapse();
GetData();
} else if (v == false) {
alert(“Missing Required Filter Alert – Please select an Acct Per., Posted or DOS range!”);
}
});
function GetData() {
dbtblcols = “”;
dtfldsel = [];
var rowindexes = $(‘#jqxGridReportFields’).jqxGrid(‘getselectedrowindexes’);
dtfldsel.push({ name: “Ar_Amount”, type: ‘number’, format: ‘c2’ })
for (var ia = 0; ia < rowindexes.length; ia++) {
var fields = $(‘#jqxGridReportFields’).jqxGrid(‘getcellvalue’, rowindexes[ia], “fields”);
var fformat = $(‘#jqxGridReportFields’).jqxGrid(‘getcellvalue’, rowindexes[ia], “format”);
if (fformat == ‘date’) {
dtfldsel.push({
name: fields,
type: ‘string’,
format: ‘yyyy-MM-dd’
})
} else {
dtfldsel.push({
name: fields,
type: fformat
})
}
dbtblcols += fields;
if (ia < rowindexes.length – 1) {
dbtblcols += “, “;
}
}
query = “”;
query = “SELECT SUM(Ar_Amount) Ar_Amount, ” + dbtblcols + ” FROM SomeDynamicTable WHERE” + dynamicwhereclause + ” GROUP BY ” + dbtblcols + ” FOR JSON PATH”;
var source =
{
datatype: “json”,
contentType: ‘application/json; charset=utf-8’,
datafields: dtfldsel,
async: false, //must have this set to false when json data is not obtained locally “static” and coming from remote sql server for jqxpivotgrid
url: ‘Ajax/GetSomeReport.aspx?Func=ss&Sql=’ + query
};
var dataAdapter = new $.jqx.dataAdapter(source);
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,
totals: {
rows: {
subtotals: false,
grandtotals: true
},
columns: {
subtotals: false,
grandtotals: true
}
}
, values: [
{ dataField: ‘Ar_Amount’, function: ‘sum’, text: ‘Amount’, align: ‘center’, formatSettings: { thousandsSeparator: ‘,’, decimalPlaces: 2, align: ‘center’, negativeWithBrackets: true }, className: ‘myItemStyle’, classNameSelected: ‘myItemStyleSelected’ }
]
});
var localization = { ‘var’: ‘Variance’ };
$(‘#divPivotGrid’).jqxPivotGrid(
{
localization: localization,
source: pivotDataSource,
treeStyleRows: false,
autoResize: false,
multipleSelectionEnabled: true,
theme: ‘energyblue’,
itemsRenderer: function (pivotItem) {
var sortElement = ”;
if (pivotItem.hierarchy.getSortItem() == pivotItem) {
var elementClass = pivotItem.hierarchy.getSortOrder() == ‘desc’ ? “jqx-icon-arrow-down” : “jqx-icon-arrow-up”;
sortElement = “<div id=’sortElement’ class='” + elementClass + “‘ style=’margin-right: 0px; width: 16px; height: 11px; font-size: smaller; vertical-align: bottom; padding-top: 4px;’></div>”;
}
var additionalItem = ”;
var classStyle = ‘jqx-pivotgrid-expand-button’;
if (pivotItem.isExpanded) {
classStyle = ‘jqx-pivotgrid-collapse-button’;
}
additionalItem = ‘<div style=”position: relative; top: 5px; padding: 5px; width: 11px; height: 11px;” class=”‘ + classStyle + ‘”></div>’
if (pivotItem.items.length == 0) {
additionalItem = ”;
}
return additionalItem + “<div style=’width: calc(100% – 8px); font-size: smaller; text-align: center; vertical-align: bottom; padding-top: 4px;’>” + pivotItem.text + sortElement + “</div>”;
},
cellsRenderer: function (pivotCell) {
var cellText = pivotCell.value == 0 ? ” : pivotCell.formattedValue;
return “<div style=’width: calc(100%-8px); height: 11px; text-align: center; font-size: smaller; vertical-align: bottom; padding-top: 4px;’>” + cellText + “</div>”;
}
});

var pivotGridInstance = $(‘#divPivotGrid’).jqxPivotGrid(‘getInstance’);
$(‘#divPivotGridDesigner’).jqxPivotDesigner(
{
type: ‘pivotGrid’,
theme: ‘energyblue’,
target: pivotGridInstance
});
}
});
</script>

[adminKeymaster]

Hi mevans5415,

Looking at the Pivot Grid API, it should be possible to sort by rows and by columns at the same time.

Pivot Rows:

Sorts the items collection

Parameters
Name Type Description
pivotItem Object
sortOrder String Sort order of the collection – ‘asc’ or ‘desc’

The Pivot Grid also has API for getting the rows and columns collections: getPivotRows and getPivotColumns i.e you can use these and then call the respective “sortBy” methods.

Best regards,
Peter Stoev

[mevans5415Participant]

Is there a way to have the report auto sort in ascending order for column and row field names no matter how many are added.

Example #1
BEFORE
Payment Charge Adjustment Transfer Total
PostedDate
09/02/2020 (145.00) 200.00 (20.00) (35.00) 0.00
09/03/2020 (135.00) 250.00 (40.00) (75.00) 0.00
09/01/2020 (35.00) 100.00 (50.00) (15.00) 0.00

AFTER
Adjustment Charge Payment Transfer Total
PostedDate
09/01/2020 (50.00) 100.00 (35.00) (15.00) 0.00
09/02/2020 (20.00) 200.00 (145.00) (35.00) 0.00
09/03/2020 (40.00) 250.00 (135.00) (75.00) 0.00

Example #2
BEFORE
Payment Charge Adjustment Transfer Total
PostedDate Clinician
09/02/2020 Jim Madis (145.00) 200.00 (20.00) (35.00) 0.00
09/02/2020 Brian Scott (145.00) 200.00 (20.00) (35.00) 0.00
09/03/2020 Alex Smith (135.00) 250.00 (40.00) (75.00) 0.00
09/03/2020 Adam Mavis (135.00) 250.00 (40.00) (75.00) 0.00
09/01/2020 Trevor James (35.00) 100.00 (50.00) (15.00) 0.00
09/01/2020 Mark Edwards (35.00) 100.00 (50.00) (15.00) 0.00

AFTER
Adjustment Charge Payment Transfer Total
PostedDate Clinician
09/01/2020 Mark Edwards (50.00) 100.00 (35.00) (15.00) 0.00
09/01/2020 Trevor James (50.00) 100.00 (35.00) (15.00) 0.00
09/02/2020 Brian Scott (20.00) 200.00 (145.00) (35.00) 0.00
09/02/2020 Jim Madis (20.00) 200.00 (145.00) (35.00) 0.00
09/03/2020 Adam Mavis (40.00) 250.00 (135.00) (75.00) 0.00
09/03/2020 Alex Smith (40.00) 250.00 (135.00) (75.00) 0.00

[adminKeymaster]

Hi mevans5415,

Such built-in functionality is not available. You can use the pivot component’s API to apply sorting as discussed in the previous reply.

Best regards,
Peter Stoev

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

[Author]

Posts