Remove Columns That Contain All Nulls From Pivot Table
Application
Use this script in a pivot table where a column may have all NULL values to clean up the UI.
The irrelevant columns are dropped after being loaded
widget.on('ready', function(){
var tlen = parseInt($('.p-container thead tr td').last().prev().attr('fidx')) + 2 //counts how many cols total
var dims = $('#pivot_ td.p-dim-head').length // counts the number of dims
for (var i = dims; i < tlen; i++) {
var stuff = $('#pivot_ td.p-value[fidx='+i+']:not(.p-total-row-val)').text().trim()
//if the text content is empty then hide the column
if(stuff ==''){
//$('td[fidx="' + i +'"]').css('display', 'none'); //hides column
$('td[fidx="' + i +'"]').remove(); //hides column
}
};
});
-
With 0s, the method above won't work because it relies on the jQuery .text() method which returns a single string of the combined text of all matching elements. If the entire column is blank, this text will also be blank, however with 0s it will end up like "0 0 0 0 0 0..." corresponding to the number of rows in the view.
To change this script to hide all-0 columns as well, you can instead use the jQuery "filter" method on the list of cells, with a function to evaluate if a given cell has content according to your specifications. Then if none are found, implying that no cells in the column have what you consider a valid value, you can hide the column.
I would add that the above implementation will benefit from limiting the scope of the initial jQuery selector to the widget element, otherwise it will select all column headers on the page, including for other pivots, which can cause undesired behavior.
Please sign in to leave a comment.
Comments
3 comments