I'm building a shiny app using flexdashboard and reactive shiny that fetches data from a SQL server data base.First execution of query in r global( not reactive) works fine in all pages but the code in Input {.sidebar} when I try to select an specific dateRange (shiny widget) coded as reactive does not work , I don't even see the calendar
Please can someone help me to solve this issue? I think the reactive structure I'm coding is wrong also , I'm not sure if I'm passing correctly the new dates to the query. I have been trying to fix it during a couple of days but I don't find how to solve it. Many thanks for your help.
I got this error :
Quitting from lines 86-147 (Test-FLEXDASH-v1.32-RowsFormat-DataRange.RMD)
Warning: Error in : operator is invalid for atomic vectors.
title: "RESST Datawarehouse "
runtime: shiny
output:
flexdashboard::flex_dashboard:
# logo: logo_ phe
theme: united #sandstone #spacelab
orientation: rows #columns
vertical_layout: "fill" # "scroll"
storyboard: true
library(flexdashboard)
library(shiny)
library(ggplot2)
library(gganimate)
library (plotly)
library(ggthemes)
library(ggthemr)
library(plyr)
library(dplyr) # For filtering and manipulating data
library(RODBC) # The package to connect to odbc db
library(RODBCext) #Using parameterized queries
library(tidyr)
library(DataExplorer)
library(gghighlight)
library(treemap)
library(highcharter)
library(reshape2)
library(scales)
channel <- odbcConnect("RESSTDW");
SQLRESSTDW <- sqlQuery(channel,"
SELECT
A.[DATE_SK] AS [Date]
,C.[PHECNM] AS [PHE CENTER]
,B.[SYNDROME] AS Synd
,B.[MEDIUM_LEVEL]
,B.[HIGH_LEVEL]
,E.[SYSTEM_NAME] AS [System]
,D.[RESST_AGE_GROUP2] AS [Age]
,sum([COUNT_PER_DAY]) as [Calls]
from [dbo].[FACT_RESST] A
left join [dbo].[DIMENSION_SYNDROME] B on A.SYNDROME_SK = B.[SYNDROME_ID]
left join [dbo].[DIMENSION_GEOG] C on A.GEOGRAPHY_SK=C.GEOG_ID
inner join [dbo].[DIMENSION_DEMOGRAPHIC] D on A.Demographic_SK=D.DEMOGRAPHIC_SK
left join [dbo].[DIMENSION_SYSTEM] E on A.[SYSTEM_SK]=E.[SYSTEM_ID]
LEFT JOIN [dbo].[DIMENSION_DATE] f ON A.[Date_SK]=F.[Date_SK]
where
F.[Date] >= getdate()-30
group by A.[Date_SK]
,B.[SYNDROME]
,B.[MEDIUM_LEVEL]
,B.[HIGH_LEVEL]
,C.[PHECNM]
,C.[UTLANM]
,E.[SYSTEM_NAME]
,D.RESST_AGE_GROUP2
")
odbcClose(channel)
dates_fixed <- as.Date(paste0(SQLRESSTDW$Date, "01"), format = "%Y%m%d") #to transform Date from numeric format to Date
SQLRESSTDW <- cbind(dates_fixed,SQLRESSTDW) #to combine (including) new column with existed ones
SUMMARY
Input {.sidebar}
dateRangeText <- reactive ({
dateInput('dateRange',
label = 'Date range input: yyyy-mm-dd',
start = input$dateRange , end = input$dateRange2,
max = (Sys.Date()-1)
)
})
renderText({
paste("input$dateRange is",
paste(as.character(input$dateRange), collapse = " to ")
)
})
channel <- odbcConnect("RESSTDW");
SQLRESSTDW <- sqlQuery(channel,"
SELECT
A.[DATE_SK] AS [Date]
,C.[PHECNM] AS [PHE CENTER]
,B.[SYNDROME] AS Synd
,B.[MEDIUM_LEVEL]
,B.[HIGH_LEVEL]
,E.[SYSTEM_NAME] AS [System]
,D.[RESST_AGE_GROUP2] AS [Age]
,sum([COUNT_PER_DAY]) as [Calls]
from [dbo].[FACT_RESST] A
left join [dbo].[DIMENSION_SYNDROME] B on A.SYNDROME_SK = B.[SYNDROME_ID]
left join [dbo].[DIMENSION_GEOG] C on A.GEOGRAPHY_SK=C.GEOG_ID
inner join [dbo].[DIMENSION_DEMOGRAPHIC] D on A.Demographic_SK=D.DEMOGRAPHIC_SK
left join [dbo].[DIMENSION_SYSTEM] E on A.[SYSTEM_SK]=E.[SYSTEM_ID]
LEFT JOIN [dbo].[DIMENSION_DATE] f ON A.[Date_SK]=F.[Date_SK]
where
F.[Date] between 'as.date(input$dateRange[1])' and 'as.date(input$dateRange[2])'
group by A.[Date_SK]
,B.[SYNDROME]
,B.[MEDIUM_LEVEL]
,B.[HIGH_LEVEL]
,C.[PHECNM]
,C.[UTLANM]
,E.[SYSTEM_NAME]
,D.RESST_AGE_GROUP2
")
odbcClose(channel)
dates_fixed <- as.Date(paste0(SQLRESSTDW$Date, "01"), format = "%Y%m%d") #to transform Date from numeric format to Date
SQLRESSTDW <- cbind(dates_fixed,SQLRESSTDW) #to combine (including) new column with existed ones
return(SQLRESSTDW)