How to give column total in footer of table with grouped rows using Javascript?

[rdatasculptor]

First of all, thank you very much for this brilliant package! It surely has given my dashboards a boost.

I did not intend to cross post this issue, but I really would like to have a solution: https://stackoverflow.com/q/62393123/1983395
Maybe it's my lack of javascript knowledge, but I tried several things, with no luck.

[glin]

Hi,

Questions like this are definitely welcome here. This was an oversight in documentation -- the custom rendering docs don't mention what colInfo.data looks like when you have grouped/nested data: https://glin.github.io/reactable/articles/custom-rendering.html#footers

Usually, data will be an array of individual row data objects, like

{
  "Manufacturer": "Acura",
  "Model": "Integra",
  "Type": "Small",
  "Price": 15.9
}

However, when a row is aggregated, the individual sub row data will be nested under a _subRows property:

// For an aggregated row with 2 sub rows
{
  "Manufacturer": "Acura",
  "_subRows": [
    {
      "Manufacturer": "Acura",
      "Model": "Integra",
      "Type": "Small",
      "Price": 15.9
    },
    {
      "Manufacturer": "Acura",
      "Model": "Legend",
      "Type": "Midsize",
      "Price": 33.9
    }
  ]
}

You can insert a console.log(colInfo) in the JS function to explore the full data structure in your browser's JavaScript console:

footer = JS("function(colInfo) {
  console.log(colInfo)
}")

Since the individual row data is now nested, it's a little more work to sum it all up, but here's one method that uses two forEach loops:

library(reactable)

data <- MASS::Cars93[1:20, c("Manufacturer", "Model", "Type", "Price")]

reactable(
  data,
  groupBy = "Manufacturer",
  searchable = TRUE,
  columns = list(
    Price = colDef(
      aggregated = JS("function(cellInfo) {
        // Calculate sum of all values in visible rows
        var values = cellInfo.subRows.map(function(row) { return row.Price})
        var total = values.reduce(function(a, b) { return a + b }, 0)
        return total.toFixed(1)
      }"),
      footer = JS("function(colInfo) {
        var total = 0
        colInfo.data.forEach(function(row) { 
          row._subRows.forEach(function(data) { 
            total += data[colInfo.column.id]
          })
        })
        return '$' + total.toFixed(2)
      }")
    ),
    Manufacturer = colDef(footer = "Total")
  ),
  defaultColDef = colDef(footerStyle = list(fontWeight = "bold"))
)

I'll fix up the docs and probably add an example like this somewhere.

[rdatasculptor]

Hi Glin,

This solved it perfectly for me! Thank you so much.
(If you please, ofcourse, you can copy paste this answer to stackoverflow)

For now I'll close this issue here.

[glin]

While adding this example to the docs, I found a slightly nicer way to do this. If you use an aggregate function rather than an aggregated renderer, the aggregated total will show up in the row data:

{
  "Manufacturer": "Acura",
  "Price": 49.8,
  "_subRows": [...]
}

Then you can just sum up the row values as usual. You don't have to get into the sub rows:

library(reactable)

data <- MASS::Cars93[1:20, c("Manufacturer", "Model", "Type", "Price")]

reactable(
  data,
  groupBy = "Manufacturer",
  columns = list(
    Manufacturer = colDef(footer = "Total"),
    Price = colDef(
      aggregate = "sum",
      footer = JS("function(colInfo) {
        var total = 0
        colInfo.data.forEach(function(row) {
          total += row[colInfo.column.id]
        })
        return '$' + total.toFixed(2)
      }")
    )
  ),
  defaultColDef = colDef(footerStyle = list(fontWeight = "bold"))
)

https://glin.github.io/reactable/articles/cookbook/cookbook.html#totals-with-aggregated-rows