Having trouble adding buttons for "download as csv/excel" to my Shiny app DataTable section. It seems that as my "Data" is queried from redshift, it doesn't recognize it as a data frame.
Error thrown: 'data' must be 2-dimensional (e.g. data frame or matrix)
From this document it seems to return it as a data frame which would contradict the error message. Any idea how I could fix this and get the buttons to work?
server <- function(input, output) {
observeEvent(input$executeSQL, {
if (input$selection == "CL7D") {
output$mytable = DT::renderDataTable({
dbGetQuery(conn, "select * from dummy")
extensions = "Buttons"
options = list(paging = TRUE,
scrollX=TRUE,
searching = TRUE,
ordering = TRUE,
dom = 'Bfrtip',
buttons = c('copy', 'csv', 'excel', 'pdf'),
pageLength=5,
lengthMenu=c(3,5,10) )
})
}
})
}
CodePudding user response:
Your first argument to renderDataTable
is indeed an expression, as it should be, but ... your expression is not doing much.
- First, it retrieves some data from
dbGetQuery
, but since it isn't stored anywhere, it is immediately lost. - Then it creates a local variable named
extensions
and assigned the value"Buttons"
. - Last, it creates a local variable named
options
that islist(..)
.
Since the default behavior in R is for an expression with { ... }
is for it to "return" the last expression within it, that means that it doesn't return the value from dbGetQuery
, it doesn't return extensions
, it instead returns the value of options
, which is not 2-dimensional.
Perhaps you meant:
observeEvent(input$executeSQL, {
if (input$selection == "CL7D") {
output$mytable = DT::renderDataTable( # remove the '{'
dbGetQuery(conn, "select * from dummy"), # add a ','
extensions = "Buttons", # add a ','
options = list(paging = TRUE,
scrollX=TRUE,
searching = TRUE,
ordering = TRUE,
dom = 'Bfrtip',
buttons = c('copy', 'csv', 'excel', 'pdf'),
pageLength=5,
lengthMenu=c(3,5,10) )
) # remove the '}'
}
})
Side notes:
I tend to prefer to keep data-creating/querying actions as simple
reactive
components that then may be used elsewhere. For example, let's say you wanted a banner at top of your shiny app to display the number of rows returned. With your current code, you would need to rundbGetQuery
twice.Instead, make it reactive and then use it later; that way, if you need to use the data for any other component in your shiny app, you'll have it available separately.
mydata <- eventReactive(input$executeSQL, { dbGetQuery(conn, "select * from dummy") }) observeEvent(mydata(), { if (input$selection == "CL7D") { output$mytable = DT::renderDataTable( mydata(), extensions = "Buttons", options = list(paging = TRUE, scrollX=TRUE, searching = TRUE, ordering = TRUE, dom = 'Bfrtip', buttons = c('copy', 'csv', 'excel', 'pdf'), pageLength=5, lengthMenu=c(3,5,10) ) ) } })
I've yet to see one working example of
shiny
where having a reactive component nested within another made sense. I have not tested your code here (lacking data and ui and such), but I suspect that your code would operate better, more-cleanly asmydata <- eventReactive(input$executeSQL, { dbGetQuery(conn, "select * from dummy") }) output$mytable <- DT::renderDataTable( if (input$selection == "CL7D") mydata(), extensions = "Buttons", options = list(paging = TRUE, scrollX=TRUE, searching = TRUE, ordering = TRUE, dom = 'Bfrtip', buttons = c('copy', 'csv', 'excel', 'pdf'), pageLength=5, lengthMenu=c(3,5,10) ) )
If that doesn't work, move the
if
conditional out,mydata <- eventReactive(input$executeSQL, { dbGetQuery(conn, "select * from dummy") }) data_CL7D <- reactive({ if (input$selection == "CL7D") mydata() }) output$mytable <- DT::renderDataTable( data_CL7D(), extensions = "Buttons", options = list(paging = TRUE, scrollX=TRUE, searching = TRUE, ordering = TRUE, dom = 'Bfrtip', buttons = c('copy', 'csv', 'excel', 'pdf'), pageLength=5, lengthMenu=c(3,5,10) ) )