changing Grand Total Position

Answered

Siva

• October 11, 2017 05:05

Hi Team,

In pivot tables, we would like to show column grand totals on top of the table and row grand totals at front. Please let us know how to alter positions.

Regards,
Sai Krishna.

0

• 

  Brian Bontrager

  • October 18, 2017 13:19

  Here's part of what you are looking for. This script will move the grand total row to the top.  
  
  

  
  widget.on('ready', function(sender, ev){
  
   if ($(".p-grand-total-row-head", element).length > 0)
   {
   setTimeout(function(){
   $('table#pivot_',element).prepend($('table#pivot_', element).find('tr:last'));
   $('div.table-container table', element).prepend($('div.table-container table', element).find('tr:last'));
   $('tbody',element).each(function(){
   $(this).find('tr:first td').not('.phantom').css('border-bottom', '1px solid #ccc').find($('span, .p-value')).css('font-weight', 'bold');
   });
  
  $('.p-grand-total-row-head.p-first-data-col').css('border-left', 'none');
   },500);
   }
  
  });

   

  1
• 

  Elliott Herz

  • October 18, 2017 13:48

  Hey Sai,

  I built a script that moves 1 of the grand totals from the right so that it is the first column on the left. Not sure of how many grand totals you were trying to move to the front, but you can use this as an example:

  

  widget.on("ready", function(widget, args) {
  
  //move the right grand total header as the first field on the left
  var header = $('td.p-grand-total-head','[id="' + widget.oid+ '"]')[0];
  $(header).insertAfter($('tr#pivot__ACCRowArea')[0].children[0]);
  
  //find all the right grand total tags (specific to the fidx).
  var tags2 = $('[id="' + widget.oid+ '"]').find("[fidx=2]");
  var grandTotalTags = [];
  for(var i=0; i<tags2.length; i++) {
  if(tags2[i].className === 'p-value p-total-col-val' || tags2[i].className === 'p-value p-first-data-col p-total-col-val') {
  grandTotalTags.push(tags2[i]);
  }
  }
  var rows = $('td.p-dim-member','[id="' + widget.oid+ '"]');
  
  //move the right grand total before the other columns
  for(var i=0; i<rows.length; i++) {
  $(grandTotalTags[i]).insertAfter($(rows[i]));
  }
  
  //move right grand total for bottom grand total
  var bottom = $('td.p-grand-total-row-head','[id="' + widget.oid+ '"]')[0];
  var lastElement = bottom.parentElement.lastElementChild;
  $(lastElement).insertAfter($(bottom));
  
  });

  1
• 

  Oxana Noa Umansky

  • October 24, 2017 23:31

  Thanks so much Brian and Elliot!

  @devops, let us know if you've achieved what you've been looking for!

   

  0
• 

  Siva

  • October 26, 2017 09:14

  Hi Brian and Elliot,

  Thanks Brian and Elliot. I have implemented these scripts in widget. It is working fine. We will try to edit the script given Elliot to work in all the scenarios.

  We are using Sisense only by embedded JS in our application. So, we want to make sure that there will not be any performance impact after using the script.

  Regards,
  Sai Krishna.

  0
• 

  Analytics & Insights

  • August 17, 2018 13:41

  Hi all! Hope you are going well.

  Does anyone knows how to do the same thing to the subtotal lines?

   

  Best,

  Camila.

  0
• 

  Siva

  • November 08, 2018 12:20

  We have same required. Please post if someone has the solution.

  0
• 

  Brandan Spears

  • January 03, 2019 22:29

  @Elliott Herz do you have a script for two Grand Total columns? 

  0
• 

  Elliott Herz

  • January 07, 2019 06:02

  Hey Brandan, I don't have a script offhand that does it.

  0
• 

  Sam Barger

  • June 18, 2020 20:45

  Hi, I'm attempting to display column grand totals at the top of a pivot table rather than the bottom. I tried using the script provided by Brian Bontrager above to no avail. I am on Version: L8.0.5.212. 

  Any ideas? 

  Thanks

  Sam

  0
• 

  Raimonda Staniute

  • June 25, 2020 10:47
  • Edited

  Hi All,

   

  I am also on Linux version: L8.0.5.212. I have asked Sisense support on why the script is not working. They have replied me this:

  Indeed this script will not be effective in Linux since we have released a new version of Pivot that has new structure.
  The script should be re-written, feel free to use Pivot 2.0 API article for details.
  
  

  If anyone has a script already, would be great if you could please share it.

  Thanks!

   

   

  0
• 

  Jayson Yurcho

  • September 16, 2020 13:05

  The below should work with Pivot 2.0. 

  widget.on('ready', function(sender, ev){
  
  //Define Variables
  var lastRowClass = $('tr.table-grid__row:contains(Grand Total)',element).last().attr('class')
  var firstClass = lastRowClass.split(' ')[0]
  var secondClass = lastRowClass.split(' ')[1]
  var firstRowClassBase = $('div.' + firstClass).first().attr('class').split(' ')[0]
  var firstRowClassNumber = $('div.' + firstClass).first().attr('class').split(' ')[1].match(/\d+/g)
  
  //If the first row class number does NOT equal zero...
  if(firstRowClassNumber[0] != 0){
  
  $('div.' + firstRowClassBase + ' div.table-grid__cell').css({'border-top':'','font-weight':''})
  $('tr.' + firstClass).first().insertAfter($('tr.table-grid__row-' + (firstRowClassNumber - 1)))
  $('div.' + firstClass).first().insertAfter($('div.table-grid__row-' + (firstRowClassNumber - 1)))
  }
  
  //Move Grand Total row to top
  $('div.' + secondClass).insertBefore($('div.' + firstClass).first())
  $('tr.' + secondClass).insertBefore($('tr.' + firstClass).first())
  $('div.' + secondClass + ' div.table-grid__cell').css({'border-bottom':'1px solid #ccc','border-top':'1px','font-weight':'bold'})
  $('tr.' + secondClass + ' td').css({'border-bottom':'1px solid #ccc','border-top':'1px','font-weight':'bold'})
  
  });

   

  0

Please sign in to leave a comment.