1

Currently I am building a KPI dashboard using shinydashboard, R and MS Access. In order to fulfill user requirements to be able to drill down to atomic data I am using a MS Access database (soon SQL) as the underlying data architecture. I am using RODBC as the DB driver connection library.

Reactive inputs include {warehouse, date1, date2}. Date inputs are in date range format on the ui.R page.

To reactively calculate the KPIs in question I will need the metrics in the database to be interactively queried based on user inputs, then calculated. I am able to get interactively generated query to work when doing it manually, but not in shinydashboard. I am also able to generate the query's output data table using renderDataTable within R & shiny, indicating that the query is working. However I cannot figure out why I can't do a simple proof-of-principle with the simple "sum" statement below.

server.R

query_brk = reactive({
      q = ifelse(input$house == 'Saint Louis', 
                 paste0("SELECT * 
                        FROM T_Breakage 
                        WHERE Warehouse = 'STL' AND Date BETWEEN #",
                        as.character(format(input$dates[1], "%m/%d/%Y")), 
                        "# AND #", 
                        as.character(format(input$dates[2], "%m/%d/%Y")), "#"),
                 paste0("SELECT * 
                        FROM T_Breakage 
                        WHERE Warehouse = 'KC' AND Date BETWEEN #",
                        format(input$dates[1], "%m/%d/%Y"), 
                        "# AND #", 
                        format(input$dates[2], "%m/%d/%Y"), "#"))
      q
    })

t_breakage = reactive({
  odbc_connection = odbcConnectAccess2007(reporting_db)
  t = sqlQuery(odbc_connection, "SELECT * FROM T_Breakage WHERE Warehouse = 'STL' AND Date BETWEEN #06/01/2015# AND #03/31/2016#")# query=query_brk())
  t
}) 

output$total_breakage = reactive({
  valueBox(
    scales::comma((round(sum(t_breakage()[, 'Cases'], na.rm=TRUE)))), 
    'Warehouse Breakage', icon=icon('trash-o')
  )
})

I have checked to make sure (1) the query is rendering properly and in the correct format for Access, and (2) that the query works as specified. I am reasonably certain that the breakdown appears at the "output$total_breakage" stage in the code.

I am likely missing something simple -- any help resolving this issue is greatly appreciated. Thanks for your time & thought.

pabz
  • 319
  • 3
  • 10
  • Can you change that to `sum(t_breakage()$Cases)` and see what it does? I am seeing some problem accessing the way you are, but that may not be your problem. – Gopala May 17 '16 at 17:09
  • Don't know what volumes you are going to operate but don't expect it to be really reactive, as it needs to hit disk in order to retrieve data. If you expect to get nice reactivity you should have your data in R session memory. Also be aware ODBC is considered slow interface, but using ms access you probably don't have an alternative. – jangorecki May 17 '16 at 17:20
  • jangorecki, apologies for the late response. You're right, and I noticed this reality as I went. You're also right that I didn't have much of a choice, our IT infrastructure is pitiful and mgmt. typically won't give me a server with the specs I need. – pabz Jun 07 '17 at 17:30

0 Answers0