how to pass the multiple key in database as an argument from shiny and plotly

shiny
,
1

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)
2

Topics here must be self contained, do not expect for people to look into your various topics for context and sample code, have in mind that people here volunteer their time and the least you can do in return is to make the effort of providing a properly constructed question.

1 Like
3

i am trying to explain what i have done and i what am trying to achieve here...

Screenshot_6
Screenshot_6856×465 67.6 KB

Screenshot_4
Screenshot_4902×424 70.8 KB
Screenshot_5
Screenshot_5902×497 77.3 KB

Screenshot_8jpg
Screenshot_8jpg1298×417 115 KB

4

i am kinda new to this platform so might make couple of mistake and try to avoid them in future and welcome any kind of suggestion of improvement.i appreciate your concern

5

I have referred many articles and didn't get it well and also tried with paste0 where pattern is little bit different to pass the parameter,so any suggestion would be great and if anyone knows how to get it done other way ,please let me know ,even an idea would be helpful