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

  Comment actions 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

  Comment actions 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.

  1

  Comment actions Permalink
• 

  Abbe Lefkowitz April 15, 2020 13:27

  Would it be possible to have something similar work in a pivot table where there is a field in the Columns, creating a colspan? I am trying to hide a single column of nulls within a colspan where the same column in the other colspans is not null.

  0

  Comment actions Permalink
• 

  Nir Vaknin May 11, 2020 19:59

  The above script is very useful to hide Value columns. After some tweaks we can achieve the same for DIm Columns.

  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 = 0; i < dims; i++) {
  //var stuff = $('#pivot_ td.p-value[fidx='+i+']:not(.p-total-row-val)').text().trim() //check value columns
  var stuff = $('#pivot_ td.p-dim-member[fidx='+i+']:not(.p-total-row-val)').text().trim() //check row/dim columns
  console.log(stuff);
  //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
  }
  };
  });

  0

  Comment actions Permalink

Please sign in to leave a comment.