Pivot Report Sorting and Filtering

Feature Requests
, , , ,
1

It would be great if the functionality of Pivot Reports was improved to at least be on par with standard tables. Right now, tables have much more sorting and customization options available and I would think it wouldn't be too much work to make some of those customization options available to other report types, especially the powerful Pivot Reports.



- Ability to sort Pivot Report rows by a calculation column
- Ability to limit number of Pivot Table Rows in the report based on sorting



Scenario: I want to see how many times each donor donated, but sort my pivot table by the users who donated the most times, high to low.

2

I would also love to add my voice. Assistance on how to apply this would be greatly appreciated.

3

Directions for altering and simply how to use would be helpful. Thanks!

4

Can someone provide directions on how the code should be altered for a non-coder?  Thank you :)

5

Thank you 114851121671, this works for me. In case it's not obvious to future visitors, it's coded to work with one specific view, so you'll need to fiddle with the code near the bottom.

The only minor issue for me is if there is a column summary (I use a sum of the column), it gets sorted along with the data rows. But I'm not complaining :-)

6

$.fn.fancyTable = function(options) {
var settings = $.extend({
inputStyle: "",
inputPlaceholder: "Search...",
pagination: false,
paginationClass: "btn btn-light",
paginationClassActive: "active",
pagClosest: 3,
perPage: 10,
sortable: true,
searchable: true,
onInit: function(){ },
testing: false
}, options);
var instance = this;
this.tableUpdate = function (elm) {
elm.fancyTable.matches = 0;
$(elm).find("tbody tr").each(function() {
var n=0;
var match = true;
var globalMatch = false;
$(this).find("td").each(function() {
if(!settings.globalSearch && elm.fancyTable.searchArr[n] && !(new RegExp(elm.fancyTable.searchArr[n],"i").test($(this).html()))){
match = false;
} else if(settings.globalSearch && (!elm.fancyTable.search || (new RegExp(elm.fancyTable.search,"i").test($(this).html())))){
globalMatch = true;
}
n++;
});
if((settings.globalSearch && globalMatch) || (!settings.globalSearch && match)){
elm.fancyTable.matches++
if(!settings.pagination || (elm.fancyTable.matches>(elm.fancyTable.perPage*(elm.fancyTable.page-1)) && elm.fancyTable.matches<=(elm.fancyTable.perPage*elm.fancyTable.page))){
$(this).show();
} else {
$(this).hide();
}
} else {
$(this).hide();
}
});
elm.fancyTable.pages = Math.ceil(elm.fancyTable.matches/elm.fancyTable.perPage);
if(settings.pagination){
$(elm).find(".pag").empty();
for(var n=1; n<=elm.fancyTable.pages; n++){
if(n==1 || (n>(elm.fancyTable.page-(settings.pagClosest+1)) && n<(elm.fancyTable.page+(settings.pagClosest+1))) || n==elm.fancyTable.pages){
var a = $("<a>",{
html:n,
"data-n": n,
style:"margin:0.2em",
class:settings.paginationClass+" "+((n==elm.fancyTable.page)?settings.paginationClassActive:"")
}).css("cursor","pointer").bind("click",function(){
elm.fancyTable.page = $(this).data("n");
instance.tableUpdate(elm);
});
if(n==elm.fancyTable.pages && elm.fancyTable.page<(elm.fancyTable.pages-settings.pagClosest-1)){
$(elm).find(".pag").append($("<span>...</span>"));
}
$(elm).find(".pag").append(a);
if(n==1 && elm.fancyTable.page>settings.pagClosest+2){
$(elm).find(".pag").append($("<span>...</span>"));
}
}
}
}
};
this.tableSort = function (elm) {
if(typeof elm.fancyTable.sortColumn !== "undefined" && elm.fancyTable.sortColumn < elm.fancyTable.nColumns){
var rows = $(elm).find("tbody tr").toArray().sort(
function(a, b) {
var stra = $(a).find("td").eq(elm.fancyTable.sortColumn).html();
var strb = $(b).find("td").eq(elm.fancyTable.sortColumn).html();
return((stra<strb)?-elm.fancyTable.sortOrder:(stra>strb)?elm.fancyTable.sortOrder:0);
}
);
$(elm).find("tbody").empty().append(rows);
}
};
this.each(function() {
if($(this).prop("tagName")!=="TABLE"){
console.warn("fancyTable: Element is not a table.");
return true;
}
var elm = $(this);
elm.fancyTable = {
nColumns: $(elm).find("td").first().parent().find("td").length,
nRows : $(this).find("tbody tr").length,
perPage : settings.perPage,
page : 1,
pages : 0,
matches : 0,
searchArr : [],
search : "",
sortColumn : settings.sortColumn,
sortOrder : 1
};

if($(elm).find("tbody").length==0){
var content = $(elm).html();
$(elm).empty();
$(elm).append("<tbody>").append($(content));
}
if($(elm).find("thead").length==0){
$(elm).prepend($("<thead>"));
// Maybe add generated headers at some point
//var c=$(elm).find("tr").first().find("td").length;
//for(var n=0; n<c; n++){
// $(elm).find("thead").append($("<th></th>"));
//}
}
if(settings.sortable){
var n=0;
$(elm).find("thead th").each(function() {
var content = $(this).html();
var a = $("<a>",{
html:content,
"data-n": n,
class:""
}).css("cursor","pointer").bind("click",function(){
if(elm.fancyTable.sortColumn == $(this).data("n")){
elm.fancyTable.sortOrder=-elm.fancyTable.sortOrder;
} else {
elm.fancyTable.sortOrder=1;
}
elm.fancyTable.sortColumn = $(this).data("n");
instance.tableSort(elm);
instance.tableUpdate(elm);
});
$(this).empty();
$(this).append(a);
n++;
});
}
if(settings.searchable){
var searchHeader = $("<tr>");
if(settings.globalSearch){
var searchField = $("<input>",{
"placeholder": settings.inputPlaceholder,
style:"width:100%;"+settings.inputStyle
}).bind("change paste keyup",function(){
elm.fancyTable.search = $(this).val();
instance.tableUpdate(elm);
});
var th = $("<th>",{ style:"padding:2px;" }).attr("colspan",elm.fancyTable.nColumns);
$(searchField).appendTo($(th));
$(th).appendTo($(searchHeader));
} else {
var n=0;
$(elm).find("td").first().parent().find("td").each(function() {
elm.fancyTable.searchArr.push("");
var searchField = $("<input>",{
"data-n": n,
"placeholder": settings.inputPlaceholder,
style:"width:100%;"+settings.inputStyle
}).bind("change paste keyup",function(){
elm.fancyTable.searchArr[$(this).data("n")] = $(this).val();
instance.tableUpdate(elm);
});
var th = $("<th>",{ style:"padding:2px;" });
$(searchField).appendTo($(th));
$(th).appendTo($(searchHeader));
n++;
});
}
searchHeader.appendTo($(elm).find("thead"));
}
// Sort
instance.tableSort(elm);
if(settings.pagination){
$(elm).find("tfoot").remove();
$(elm).append($("<tfoot><tr></tr></tfoot>"));
$(elm).find("tfoot tr").append($("<td class='pag'></td>",{ }).attr("colspan",elm.fancyTable.nColumns));
}
instance.tableUpdate(elm);
});
return this;
};

 

$(document).on('knack-view-render.view_3021', function (event, view) {

// $('#kn-report-view_3021-1').find('.kn-table').find('thead').find('th:eq(2)').click(function() {
// console.log('click');

// });

$("#kn-report-view_3021-1 .kn-table").fancyTable({

sortColumn:0,// column number for initial sorting

sortOrder:'descending',// 'desc', 'descending', 'asc', 'ascending', -1 (descending) and 1 (ascending)

sortable:true,

pagination:false,// default: false

searchable:false,

globalSearch:false,

globalSearchExcludeColumns: [2,5]// exclude column 2 & 5

});


});

 

 

//Use this code

7

I totally agree. I have a current client and really need to sort the pivot table by calculated value. Thanks!

8

+1

9

It's nearly 1.5 years from this post and still pivot tables and bar charts can't be sorted by a calculation field. can this please be addressed? this is a simple enhancement that increases the usability and usefulness of reports by a long shot!

Thanks.

1 Like
10

Definitely add my vote to this. Sorting by value of rows is just basic. A big part of why we use Knack is to try and wean people off spreadsheets. Much harder sell if basic functionality like sorting by value is not included. Seriously, Knack team, this is a simple SQL “ORDER BY”!

2 Likes