i am just trying to get the sub-barchart on click on parent barchart, does anyone knows how to pass multiple keys, for the single key (dynamic parameter) is working for me, how to get it done for more than one.
library(RODBC)
library(RODBCext)
#>
#> Attaching package: 'RODBCext'
#> The following objects are masked from 'package:RODBC':
#>
#> odbcFetchRows, sqlFetchMore
library(plotly)
#> Loading required package: ggplot2
#>
#> Attaching package: 'plotly'
#> The following object is masked from 'package:ggplot2':
#>
#> last_plot
#> The following object is masked from 'package:stats':
#>
#> filter
#> The following object is masked from 'package:graphics':
#>
#> layout
library(shinydashboard)
#>
#> Attaching package: 'shinydashboard'
#> The following object is masked from 'package:graphics':
#>
#> box
library(tidyverse)
library(readr)
library(shiny)
library(reprex)
library(DT)
#>
#> Attaching package: 'DT'
#> The following objects are masked from 'package:shiny':
#>
#> dataTableOutput, renderDataTable
library(dplyr)
COL = c("#9B59B6","#58D68D","#F4D03F","#909497","#D35400","#5D6D7E","#C39BD3","#5499C7","#CD6155","#BDC3C7","#808B96","#58D68D","#D7BDE2","#A93226","#B3B6B7","#138D75","#34495E","#F1948A","#D7BDE2" ,"#D35400", "#CD6155", "#5D6D7E", "#C39BD3" ,"#138D75" ,"#808B96", "#A93226" ,"#909497", "#B3B6B7" ,"#34495E" ,"#5499C7", "#9B59B6", "#58D68D" ,"#F1948A" ,"#F4D03F", "#BDC3C7", "#58D68D")
dbcnd <- odbcDriverConnect('Driver={SQL Server};Server=20.342.12.123;Database=;Uid=dan;Pwd=566$abc')
#> Warning in odbcDriverConnect("Driver={SQL
#> Server};Server=20.342.12.123;Database=;Uid=dan;Pwd=566$abc"): [RODBC]
#> ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver]
#> [DBNETLIB]SQL Server does not exist or access denied.
#> Warning in odbcDriverConnect("Driver={SQL
#> Server};Server=20.342.12.123;Database=;Uid=dan;Pwd=566$abc"): [RODBC]
#> ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver]
#> [DBNETLIB]ConnectionOpen (Connect()).
#> Warning in odbcDriverConnect("Driver={SQL
#> Server};Server=20.342.12.123;Database=;Uid=dan;Pwd=566$abc"): ODBC
#> connection failed
ui<-dashboardPage(
dashboardHeader(title = "dashboard",titleWidth = 300),
dashboardSidebar(
sidebarMenuOutput("menu"),
selectInput(inputId = "category",
label = "STATE",
choices = c("RAJ","OTHER_STATES")),
selectInput(inputId = "category1",
label = "DISTRICT", choices = c("ANISTEN","BIAN")),
selectInput(inputId = "category2",
label = "CENTER_NAME", choices = c("ASTER","CLANE","JABELIN"))
),
dashboardBody(
fluidRow(plotlyOutput("plott1")),
fluidRow(plotlyOutput("plott2")),
fluidRow(plotlyOutput("plott3")),
fluidRow(plotlyOutput("plott4")),
fluidRow(plotlyOutput("plott5")),
fluidRow(plotlyOutput("plott6")),
fluidRow(plotlyOutput("plott7")),
verbatimTextOutput("check")
))
server<-function(input,output){
output$plott1 <- renderPlotly({
qrydd=paste("exec database_map..[spformother]'','99','','','0','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
ds <- data.frame(labels = c("Raj","Other_State"),
values = c(dtd$TotRaj,dtd$TotOtherState)
)
closeAllConnections()
plot_ly(ds, labels = ~labels, values = ~values,type = 'pie',source ='listenhere1',
textposition = 'inside',
textinfo = 'label+percent',
insidetextfont = list(color = '#FFFFFF'),
marker = list(colors = colors,
line = list(color =colors , width = 1)),showlegend = FALSE) %>%
layout(title = 'state wise',
xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))
})
output$plott2 <- renderPlotly({
s <- event_data("plotly_click", source = "listenhere1")
req(!is.null(s))
if(s$pointNumber == 0){
qrydd=paste("exec database_map..[spformother] '0','0','','0','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
tablename=rowSums(cbind(dtd$TotRaj,dtd$TotOtherState),na.rm=TRUE)
layout <- list(
font = list(size = 12),
title = "",
xaxis = list(title = "district"),
yaxis = list(title = "records",automargin = TRUE)
)
p <- plot_ly(dtd, colors = COL, marker = list(color = COL),source = 'link3',key = ~paste(dtd$DID, sep = "")) %>%
add_trace( x = dtd$DistrictName, y =~tablename,name = "",type = 'bar') %>%
layout( font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)
}
else {
qrydd=paste("exec database_map..[spformother] '','','','','0','0','2','0','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
tablename=rowSums(cbind(dtd$TotRaj,dtd$TotOtherState),na.rm=TRUE)
layout <- list(
font = list(size = 12),
title = "OTHER_STATES",
xaxis = list(title = "RECORDS"),
yaxis = list(title ="STATES" ,automargin = TRUE)
)
p <- plot_ly(dtd, colors = COL, marker = list(color = COL),source = 'linkhere',orientation='h') %>%
add_trace( x =dtd$TotFormFOtherState,y = dtd$StateName,name = "states",type = 'bar') %>%
layout( font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)
}
})
output$plott3 <- renderPlotly({
d<-event_data('plotly_click',source = 'link3')
if(is.null(d)==F){
qrydd=paste("exec database_map..[spformother]",d$key,",'3','','0','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
data <- data.frame(dtd$BlockName, dtd$TotRaj, dtd$TotOtherState)
p <- plot_ly(dtd, x = dtd$BlockName, y = dtd$TotRaj, type = 'bar', name = 'rajasthan',key = ~paste(dtd$DID,sep = ""), source = 'link1') %>%
add_trace(y = dtd$TotOtherState, name = 'other-state') %>%
layout(yaxis = list(title = ''), barmode = 'group',title=dtd$BlockName)}
else{
return(NULL)
}
})
output$plott4 <- renderPlotly({
d<-event_data('plotly_click',source = 'link1')
if(is.null(d)==F){
qrydd=paste("exec database_map..[spformother]",d$key,",'4','1','0','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
data <- data.frame(dtd$CenterName, dtd$TotRaj, dtd$TotOtherState)
p <- plot_ly(data, x = dtd$TotRaj, y =dtd$CenterName , type = 'bar',source = 'data1', name = 'rajasthan',key = ~paste(dtd$CID, sep = ""),
marker = list(color = 'rgba(246, 78, 139, 0.6)',
line = list(color = 'rgba(246, 78, 139, 1.0)'))) %>%
add_trace(x = dtd$TotFormFOtherState, name = 'other-state',
marker = list(color = 'rgba(58, 71, 80, 0.6)',
line = list(color = 'rgba(58, 71, 80, 1.0)'))) %>%
layout(barmode = 'stack',
xaxis = list(title = ""),
yaxis = list(title =""))}
else{return(NULL)}
})
output$plott5 <- renderDataTable({
event.data <- event_data("plotly_click", source = "data1")
})
output$plott6 <- renderPlotly({
d<-event_data('plotly_click',source = 'linkhere')
if(is.null(d)==F){
qrydd=paste("exec database_map..[spformother] ",d$key,",'','','','',",d$key1,",'2','15','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
layout <- list(
font = list(size = 12),
title = "",
xaxis = list(title = "district"),
yaxis = list(title = "records",automargin = TRUE)
)
p <- plot_ly(dtd, colors = COL, marker = list(color = COL),key = ~paste(dtd$StateCode, sep = ""),key1 = ~paste(dtd$DID, sep = "")) %>%
add_trace( x = dtd$DistrictName, y = dtd$TotFormFOtherState,name = "",type = 'bar') %>%
layout(font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)}
else
{
return(NULL)
}
})
output$plott6 <- renderPlotly({
d<-event_data('plotly_click',source = 'linkhere')
if(is.null(d)==F){
qrydd=paste("exec database_map..[spformother]",d$key,",'','','','','0','2','15','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
layout <- list(
font = list(size = 12),
title = "",
xaxis = list(title = "district"),
yaxis = list(title = "records",automargin = TRUE)
)
p <- plot_ly(dtd, colors = COL, marker = list(color = COL),key=~paste(dtd$DID,sep = ""),source = "link10") %>%
add_trace(x = dtd$DistrictName, y = dtd$TotOtherState,name = "",type = 'bar') %>%
layout(font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)}
else
{
return(NULL)
}
})
output$plott7 <- renderPlotly({
d<-event_data('plotly_click',source = 'link10')
if(is.null(d)==F){
qrydd=paste("exec database_map..[spformother] '4',",d$key,",'','','','4','2','','','01/01/2017','31/12/2017'")
qrydd
dtd <- sqlQuery(dbcnd,qrydd)
dtd <- data.frame(dtd)
dtd
closeAllConnections()
layout <- list(
font = list(size = 12),
title = "centre details",
xaxis = list(title = "records"),
yaxis = list(title = "centre names",automargin = TRUE)
)
p <- plot_ly(dtd, colors = COL, marker = list(color = COL),orientation='h',source = "link12",key= ~paste(dtd$DID,sep="")) %>%
add_trace(x =dtd$TotOtherState , y =dtd$CenterAddress ,name = "centre",type = 'bar') %>%
layout(font = layout$font, title = layout$title, xaxis = layout$xaxis, yaxis = layout$yaxis)
}
else
{
return(NULL)
}
})
output$check<-renderPrint({
d<-event_data('plotly_click',source ='link12')
if(length(d)==0){
return ('click on chart')
}
else{
as.list(d)
}
})
}
shinyApp(ui,server)