SubTotal plugin integration

[stephypeter]

This plugin is great for multiple aggregates but then I am not able to use it with the subtotal plugin where the rows can be expanded and collapsed with total value as well. Can both these plugins be integrated.

[pranjal-goswami]

@stephypeter one needs to rewrite the subtotal renderers the way I have done it for table renderer. I will try to do it over the weekend. PR is welcome!

[lovelyelfpop]

Any progress?

[KingNob]

Hey - This would be an awesome enhancement. Any progress?

[rkhapre]

Hi @pranjal-goswami , eagerly waiting for this feature. Please let me know if you need any help in terms of logic or something. As i this will be a awesome enhancement

[rkhapre]

Any update @pranjal-goswami . Thanks

[codythegreat]

@stephypeter @lovelyelfpop @KingNob @rkhapre

Perhaps this can help some of you, you can write your own subtotalling function. Please see below:

let subtotal_column = (column_header_element) => {
  // get column index
  let index = column_header_element.cellIndex;

  // if already subtotalled column, handle toggling subtotal off
  if (column_header_element.style.textDecoration === "underline") {
    document.querySelectorAll(".subtotal-" + index).forEach(e => {
      let bcr = e.children[0].getBoundingClientRect();
      // reduce rowspan of each subtotal's overarching cell
      [...document.querySelectorAll('tbody tr th.pvtRowLabel')]
        .filter(th => {
          let th_br = th.getBoundingClientRect();
          return th_br["x"] < bcr["x"] && th_br["y"] <= bcr["y"] && th_br["bottom"] >= bcr["bottom"]; 
        })
        .forEach(th => th.setAttribute( "rowspan", Number(th.getAttribute("rowspan")) - 1 ));
      e.remove();
    })
    column_header_element.style.textDecoration = "";
    return;
  } else {
    column_header_element.style.textDecoration = "underline";
  }

  // tracks the x position of the column at a given index
  let pos_x = document.querySelector(`thead tr:last-of-type th:nth-child(${index + 1})`).getBoundingClientRect()["x"];

  // finds the colspan that will be given to each subtotal row's th element
  let colspan = document.querySelectorAll(`thead tr:last-of-type th`).length - index - document.querySelectorAll('thead tr:last-of-type th.pvtTotalLabel').length;

  // finds all elements to subtotal
  let elements = [...document.querySelectorAll(`tbody tr th.pvtRowLabel`)].filter( e => e.getBoundingClientRect()["x"] === pos_x);

  elements.forEach(e => {
    let e_br = e.getBoundingClientRect();
    let row_index = e.parentElement.rowIndex + e.rowSpan - document.querySelectorAll('thead tr').length;
    let table = e.parentElement.parentElement;
    let row = table.children[(row_index)];

    // build the new row to be inserted
    let new_row = document.createElement("tr");
    new_row.classList.add("subtotal-" + index);

    // add in descriptor cell with colspan 
    let th = document.createElement('th')
    th.innerText = e.innerText + " Total";
    th.setAttribute("colspan", colspan);
    // set th excel output attributes
    th.setAttribute("data-b-a-s", "thin");
    th.setAttribute("data-b-a-c", "FFCDCDCD");
    th.setAttribute("data-fill-color", "FFe6eeee");
    th.setAttribute("data-f-bold","true");
    th.style.position = "sticky";
    new_row.appendChild(th);

    // add in financial cells
    let fin_headers;
    if (document.querySelector('select.pvtAggregator').selectedOptions[0].innerText.includes("(Custom)")) {
      fin_headers = document.querySelectorAll('th.pvtMeasureLabel');
    } else {
      fin_headers = document.querySelectorAll('thead tr:first-of-type th.pvtColLabel, thead tr:first-of-type th.pvtTotalLabel');
    }
    for (let i = 0; i < fin_headers.length; i++) {
      // create a new td cell to hold sum
      let current_td = document.createElement('td');

      // custom code to handle % columns, may not be applicable in your usecase.
      if (fin_headers[i].innerText.endsWith("%")) {
        let cell_value = 
          (new_row.lastChild.innerText.replace(/,/g, '') / Math.abs(new_row.querySelector(':nth-last-child(2)').innerText.replace(/,/g, ''))) * 100;
        cell_value = isFinite(cell_value) ? cell_value : 0;
        current_td.innerText = cell_value.toLocaleString(undefined, { minimumFractionDigits: 1, maximumFractionDigits: 1 }) + "%";
        // set cell attributes
        current_td.setAttribute("data-b-a-s","thin");
        current_td.setAttribute("data-b-a-c","FFCDCDCD");
        current_td.setAttribute("data-t","n");
        current_td.setAttribute("data-a-h","right");
        current_td.setAttribute("data-num-fmt","0.0%");
        current_td.setAttribute("data-f-bold","true");
        current_td.setAttribute("data-value-for",fin_headers[i].innerText);
        // set a class name so that future subtotals don't add these cells
        current_td.classList.add("pvtSubtotal");
        // set cell styles
        if (current_td.innerText.startsWith("-")) {
          current_td.setAttribute("data-f-color","FFFF0000");
          current_td.style.color = "red";
        }
        current_td.style.fontWeight = "bold";
        new_row.appendChild(current_td);
        continue;
      }
      let cells = [...document.querySelectorAll(`tbody tr td:nth-of-type(${i+1}):not(.pvtSubtotal)`)];
      let cells_to_sum = 
        cells.filter( td => {
          let td_br = td.getBoundingClientRect();
          return td_br["x"] > e_br["x"] && td_br["y"] >= e_br["y"] && td_br["bottom"] <= e_br["bottom"];
        });
      let sum = 
        cells_to_sum.map(e => e.innerText).reduce((a,b) => {
          a = typeof a === "string" ? Number(a.replace(/,/g, '')) : a;
          b = typeof b === "string" ? Number(b.replace(/,/g, '')) : b;
          return a + b;
        });
      current_td.innerText = sum.toLocaleString(undefined, { minimumFractionDigits: 2, maximumFractionDigits: 2 });
      // set cell excel attributes
      current_td.setAttribute("data-b-a-s","thin");
      current_td.setAttribute("data-b-a-c","FFCDCDCD");
      current_td.setAttribute("data-t","n");
      current_td.setAttribute("data-a-h","right");
      current_td.setAttribute("data-num-fmt","#,##0");
      current_td.setAttribute("data-f-bold","true");
      current_td.setAttribute("data-value-for",fin_headers[i].innerText);
      // set a class name so that future subtotals don't add these cells
      current_td.classList.add("pvtSubtotal");
      // set cell styles
      if (current_td.innerText.startsWith("-")) {
        current_td.style.color = "red";
        current_td.setAttribute("data-f-color","FFFF0000");
      }
      current_td.style.fontWeight = "bold";
      new_row.appendChild(current_td);
    }

    // increase rowspan where applicable
    [...document.querySelectorAll("tbody th")].filter(th => {
      let th_br = th.getBoundingClientRect();
      return th_br["x"] < e_br["x"] && th_br["y"] <= e_br["y"] && th_br["bottom"] >= e_br["bottom"]; 
    }).forEach(th => {
      th.setAttribute( "rowspan", Number(th.getAttribute("rowspan")) + 1 );
    });

    // insert new_row after e
    table.insertBefore(new_row, row);
    // set the th's left position AFTER appending row to the table
    th.style.left = th.getBoundingClientRect()["x"] - document.querySelector('.pvtTable').getBoundingClientRect()["x"];
  });
}

I have this function in my HTML page, and on a user clicks a .pvtAxisLabel value it'll add a subtotal to that row. I can use this code to add click event to the .pvtAxisLabel elements:

              onRefresh: function(config) {
                  // set pvtAxisOnchange
                  let axis_labels = document.querySelectorAll('thead tr:last-child th.pvtAxisLabel');
                  for (let i = 0; i < axis_labels.length-1; i++) {
                    axis_labels[i].addEventListener('click', (e) => subtotal_column(e.target));
                    axis_labels[i].setAttribute("title", "Toggle Subtotal on " + axis_labels[i].innerText);
                    axis_labels[i].style.cursor = "pointer";
                  }
                  . . .
              }

Now I will admit that this code is (1) very verbose and (2) very custom (we use a lot of custom styles and attributes, you'd need to either remove or implement these), but it does show that you can add subtotals. Perhaps this could serve as a rudimentary template for someone to add their own subtotaling functionality.