Shiny应用报错：找不到变量，寻求清晰解决方案

核心问题分析 #

你的代码存在几个关键问题导致变量找不到的错误：

1. Reactive提前返回：MyDT reactive中，DataOutput语句直接返回了原始查询结果，后续的数据处理代码（如数据融合、创建AsOfDate和variable列）完全没有执行，导致下游代码无法找到这些变量。
2. Reactive调用错误：访问MyDT这个reactive对象时，没有使用括号MyDT()来获取其实际数据值。
3. 嵌套Reactive不当：在renderPlot内部定义filtered_data_1为reactive是不必要的，会引发作用域问题。
4. UI结构错误：plotOutput所在的mainPanel没有放在对应的tabPanel内，且sidebarLayout缺少sidebarPanel包裹输入控件。

修正后的完整代码 #

UI代码

library(shiny)
library(lubridate)

ui <- fluidPage(
  titlePanel("ABR Dashboard"),
  
  tabsetPanel(      
    tabPanel("Time Series",
             sidebarLayout(
               sidebarPanel(
                 selectInput(inputId = "Date1", 
                             label = "Select Date", 
                             choices = seq(from = as_date(now())-16, to = as_date(now())-1, by = "day")
                 ),
                 selectInput(inputId = "Metric1",
                             label = "Which Metric",
                             choices = c("Login","Queue"))
               ),
               mainPanel(
                 plotOutput("distPlot1")
               )
             )
    )
  ) 
)

Server代码

library(shiny)
library(lubridate)
library(data.table)
library(ggplot2)
library(hms)
library(RODBC)

server <- function(input, output, session) {
  
  MyDT <- reactive({
    AbrQuery <- "
      SELECT * 
      FROM SQ_ML_MODEL.dbo.DataSci_ABR_Preds_Vs_Actuals_updated
    "
    
    SecretValuesLst <- GetSecret(Debug = TRUE)
    
    connectionString <- paste0(
      "Driver={ODBC Driver 18 for SQL Server};Server=", SecretValuesLst$SERVER,
      ";Database=", SecretValuesLst$DATABASE,
      ";UID=", SecretValuesLst$UID,
      ";PWD=", SecretValuesLst$PWD, 
      ";TrustServerCertificate=yes;"
    )
    
    DbCon <- odbcDriverConnect(connectionString)
    DataOutput <- sqlQuery(DbCon, query = AbrQuery)
    odbcClose(DbCon) # 关闭数据库连接避免资源泄漏
    
    # 完整执行数据处理流程
    VizAbrData <- melt.data.table(DataOutput[, .(MinuteOfDay,
                                                 AgentCategory,
                                                 Queue,
                                                 Login,
                                                 Tier,
                                                 Abr_Actuals,
                                                 Abr_Preds,
                                                 Abr_Diff)],
                                  id.vars = c("MinuteOfDay", "AgentCategory"))
    
    VizAbrData[, AsOfDate := as_date(MinuteOfDay)]
    VizAbrData$value[is.na(VizAbrData$value)] <- 0
    VizAbrData[, MinuteOfDay_TIME := as_hms(MinuteOfDay)]
    
    mydat <- VizAbrData
    mydat[, AsOfDate := as.Date(AsOfDate)]
    mydat[, variable := as.character(variable)]
    
    # 返回处理后的数据集
    mydat
  })
  
  output$distPlot1 <- renderPlot({
    # 使用用户输入筛选数据，正确调用reactive对象
    filtered_data <- subset(MyDT(), 
                            AsOfDate == input$Date1 & 
                              variable == input$Metric1)
    
    ggplot(data = filtered_data,
           aes(x = MinuteOfDay, y = value)) +
      geom_point(stat = "identity", col = "black") +
      geom_line(stat = "identity", linewidth = 1.6, col = "red") + 
      labs(x = "MinuteOfDay", y = input$Metric1) +
      ggtitle(paste0("Time Series: ", input$Metric1)) +
      theme(
        plot.title = element_text(size=16, face= "bold", colour= "black", hjust = 0.5, margin = margin(t=10,b=-20)),
        axis.title.x = element_text(size=16, face="bold", colour = "black"),
        axis.title.y = element_text(size=14, face="bold", colour = "black"),
        axis.text.x = element_text(size=14, face="bold", colour = "black"),
        axis.text.y = element_text(size=14, face="bold", colour = "black"),
        strip.text.x = element_text(size = 14, face="bold", colour = "black" ),
        strip.text.y = element_text(size = 14, face="bold", colour = "black"),
        axis.line.x = element_line(color="black", size = 0.3),
        axis.line.y = element_line(color="black", size = 0.3),
        panel.border = element_rect(colour = "black", fill=NA)
      ) +
      theme_classic()  
  })
}

# 运行App
shinyApp(ui = ui, server = server)

额外优化点 #

• 添加了odbcClose(DbCon)关闭数据库连接，避免资源泄漏。
• 将所有库加载语句移到脚本顶部，符合R代码规范。
• 筛选逻辑改为使用用户选择的Date1和Metric1，而非固定值，更符合Dashboard交互需求。

内容的提问来源于stack exchange，提问作者Abraham Mathew