Total of a column in DT dataTables in shiny

Question

I am trying to calculate the total of a column in my shiny app using DT::datatable. By total I mean the sum of all elements in a table not just what is showing in the current pagination. Following this example the following code should work (but it doesn't):

jsCode <- "function(row, data, start, end, display) {

                  var api = this.api(), data;

                  total = api.column(1, {page: 'all'}).data().reduce( function(a, b) { return a + b}, 0); 

                  $( api.column(1).footer() ).html('Total: ' + total);
                  }"

All I am getting from this is the sum of the elements in the current pagination. Full code below:

library(shiny)
library(DT)

set.seed(2282018)
company <- data.frame(Company = letters[1:30], Units = round(runif(30, 
                                                             1000, 10e6), 0), 
                      Price = scales::dollar(runif(30, 200, 1230)), stringsAsFactors = F)

jsCode <- "function(row, data, start, end, display) {

                  var api = this.api(), data;

                  total = api.column(1, {page: 'all'}).data().reduce( function(a, b) { return a + b}, 0); 

                  $( api.column(1).footer() ).html('Total: ' + total);
                  }"

# UI ---- 
ui <- function(){

  fluidPage(

    sidebarLayout(

      sidebarPanel(numericInput("nums", label = "Num Input", value = 1, min = 1, max = 10)),

      mainPanel(dataTableOutput("mytable"))

    )

  )

}

# server ----
server <- function(input, output, session){

  cont <- htmltools::withTags(table(
    tableHeader(names(company)),tableFooter(names(company))
  ))

  output$mytable <- DT::renderDataTable(  {

    DT::datatable(company,
                  container = cont,
                  caption = tags$caption("Example"), 
                  filter = "none", 
                  rownames = F,
                  options = list(autoWidth = T, 
                                 pageLength = 10, 
                                 scrollCollapse = T,
                                 dom = 'lftp', 
                                 footerCallback = JS(jsCode))
                  )
  }
  )
}

runApp(list(ui = ui, server = server))

Thank you

@PorkChop no it isn't. That thread helped me to get this far but it only gives me the subtotal (or the total by pagination). I need the total of all elements in the column. Thanks for answering the other thread btw... — JdeMello, Mar 06 '18 at 16:40
I marked as duplicated because the questions are the same, you should maybe bump the other one — Pork Chop, Mar 06 '18 at 16:42
Ok, so how do I get my question answered there? I agree that the nature of the question is similar but the solution in the first question does not work in this particular scenario. — JdeMello, Mar 06 '18 at 16:44
That works outside Shiny, or inside Shiny but at condition to set the option `server = FALSE`. As said [here](https://datatables.net/reference/type/selector-modifier), *When using DataTables in server-side processing mode [...] the selector can only select those rows which are on the current page.*. — Stéphane Laurent, Feb 01 '19 at 20:31

score 8 · Accepted Answer · answered Mar 06 '18 at 17:31

Maybe you can write a workaround: such as below:

library(shiny)
library(DT)

set.seed(2282018)
company <- data.frame(Company = letters[1:30], Units = round(runif(30,  1000, 10e6), 0), Price = scales::dollar(runif(30, 200, 1230)), stringsAsFactors = F)
jsCode <- "function(row, data, start, end, display) {var api = this.api(), data;$( api.column(1).footer() ).html('Total: ' + MYTOTAL);}"

# Workaround
getTotal <- function(data,index){

  if(index < 1 || index > ncol(data)){
    return("")
  }
  col <- data[,index]
  col <- gsub("[$]","",col)
  col <- gsub("[£]","",col)
  col <- gsub("[,]","",col)
  col <- suppressWarnings(as.numeric(col))
  if(all(is.na(col))){
    return("")
  }
  sum(col)
}


ui <- function(){
  fluidPage(
    sidebarLayout(
      sidebarPanel(numericInput("nums", label = "Num Input", value = 1, min = 1, max = 10)),
      mainPanel(dataTableOutput("mytable"))
    )
  )
}

server <- function(input, output, session){

  Total <- reactive({
    getTotal(company,2)
  })

  cont <- htmltools::withTags(table(
    tableHeader(names(company)),tableFooter(names(company))
  ))

  output$mytable <- DT::renderDataTable(  {
    jsCode <- sub("MYTOTAL",Total(),jsCode)
    DT::datatable(company,
                  container = cont,
                  caption = tags$caption("Example"), 
                  filter = "none", 
                  rownames = F,
                  options = list(autoWidth = T, 
                                 pageLength = 10, 
                                 scrollCollapse = T,
                                 dom = 'lftp', 
                                 footerCallback = JS(jsCode))
    )
  }
  )
}

runApp(list(ui = ui, server = server))

Thanks. Do you know why the `.colum(x, {page: 'all'}' does not work? — JdeMello, Mar 06 '18 at 17:47
I did try this line by line with the one provided by the footer callback but it doesnt work. https://datatables.net/examples/advanced_init/footer_callback.html its very strange. I suspect there is some dependency missing. I did "hacks" like displaying the whole table without paging and calculating it and only show 500px, but its not a good solution — Pork Chop, Mar 06 '18 at 17:50
btw I was trying to get `.column(x).data()` to work with no success — Pork Chop, Mar 06 '18 at 17:53
PorkChop, see my comment below the OP. That explains why `colum(x, {page: 'all'})` does not work. This is due to the server mode processing. — Stéphane Laurent, Feb 01 '19 at 20:46

Total of a column in DT dataTables in shiny

1 Answers1

Linked