wizkids121 wizkids121 - 3 months ago 182
R Question

Adding subtotals to a Shiny dashboard?

So I have a dashboard in Shiny. It's just simple table that looks like the image found in this link (apologies that I can't give you something more reproducible. Not sure how I would do that.)

It is using this code for the server.R file:

##install.packages("googleVis", lib = package.directory)
library(googleVis)
library(shiny)
library(shinydashboard)
library(DT)
library(ggplot2)

# Define a server for the Shiny app
shinyServer(function(input, output) {

# Filter data based on selections
output$table <- DT::renderDataTable(DT::datatable({
data <- BreakdownByRep
if (input$rep != "All") {
data <- data[data$'Marketing Pro' == input$rep,]
}
data
}))

})


And for the ui.R file:

shinyUI(
fluidPage(
titlePanel("Marketing Pro Status Breakdown"),

# Create a new Row in the UI for selectInputs
fluidRow(
column(4,
selectInput("rep",
"Marketing Pro Rep:",
c("All",
unique(as.character(BreakdownByRep$marketing_pro_name))))
)
),
# Create a new row for the table.
fluidRow(
DT::dataTableOutput("table")
)
)
)


What I'm looking to do is add a subtotal line at the bottom. But not just a static one, one that will adjust itself if I click the dropdown list. So lets say I want to filter by the rep named "John" (row 6), the subtotal would fluidly adjust. I haven't been able to find a good way to do this and would love some help!

Answer

This piece of code should get you started:

library(DT)
library(htmltools)
sketch <- htmltools::withTags(table(
    class = "display",
    style = "bootstrap",
    tableHeader(c("ID", colnames(iris))),
    tableFooter(c("ID", colnames(iris)))
))



datatable(iris,   container = sketch, caption = "Column sum example", 
      filter = "top", options = list(
      footerCallback = JS(
            "function( tfoot, data, start, end, display ) {",
            "var api = this.api(), data;",
            "total = api.column( 4, { page: 'current'} ).data().reduce(     function ( a, b ) {return a + b;} )",
            "total1 = api.column( 4, { search:'applied'} ).data().reduce( function ( a, b ) {return a + b;} )",
            "$( api.column( 4 ).footer() ).html(total1.toFixed(2) + ' / ' + total.toFixed(2));",
            "}"))

)

If you don't want to use to total of the complete table remove the row that defines the total and remove it from the last line. If you don't want the variable names in the table footer but just the total adjust the tableFooter in the table definition. Hope this helps.

Your code should look like this:

data <- reactive({
            data <- BreakdownByRep  
            if (input$rep != "All") {
                    data <- data[data$'Marketing Pro' == input$rep,]
            }
            data
    })


    output$table <- DT::renderDataTable(data(),   container = sketch, caption = "Column sum example", 
                                        filter = "top", options = list(
                                                footerCallback = JS(
                                                        "function( tfoot, data, start, end, display ) {",
                                                        "var api = this.api(), data;",
                                                        "total = api.column( 4, { page: 'current'} ).data().reduce(     function ( a, b ) {return a + b;} )",
                                                        "total1 = api.column( 4, { search:'applied'} ).data().reduce( function ( a, b ) {return a + b;} )",
                                                        "$( api.column( 4 ).footer() ).html(total1.toFixed(2) + ' / ' + total.toFixed(2));",
                                                        "}"))
    )
Comments