Remove Columns That Contain All Nulls From Pivot Table

Follow

Artem Yevtushenko

February 19, 2019 20:12

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
      }
   };
});

1

• 

  Moti Granovsky March 13, 2019 15:48

  This would be awesome as a plugin, with a custom menu item to toggle this behavior on and off!

  0

  Permalink
• 

  Dot Q May 15, 2019 16:05

  What about for values that are 0s? In other BI tools, when values don't exist, no column is created. However, I'm getting columns from calculations I've created that just have running 0s. Instead, I would like to hide them. How would I do that? Thank you!

  0

  Permalink
• 

  Moti Granovsky May 19, 2019 15:13

  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.

  0

  Permalink

Please sign in to leave a comment.