title: R/shiny drilldown report
body: <p>Trying to have a pie (or preferably, donut) chart that displays totals per category and that allows a drilldown to display specifics per category when clicked. Make sense?</p>
  <p>I think I might not have every system setting correct, since also copy/paste standard examples render an empty page. Unless that's somehow outdated or something.
  My system: Ubuntu 20.04, R 4.0.5, packageVersion(&quot;shiny&quot;) 1.6.0, shiny-server --version 1.5.16.958
  Blank examples: <a href="https://plotly-r.com/linking-views-with-shiny.html#drill-down" rel="nofollow noreferrer">https://plotly-r.com/linking-views-with-shiny.html#drill-down</a> and <a href="https://stackoverflow.com/questions/43263879/creating-drill-down-report-in-r-shiny">Creating drill down report in R Shiny</a> (amongst others)</p>
  <p>My current attempt (not reactive yet because I can't for the life of me figure it out):</p>
  <pre><code>library(shiny)
  library(DBI)
  library(ggplot2)
  library(dplyr)
  library(ggiraph)
  
  ui&lt;-fluidPage(
    titlePanel(&quot;Budget visuals&quot;),
  
    sidebarLayout(
      sidebarPanel(
        selectInput(&quot;fase&quot;, &quot;Choose a budget phase:&quot;, choices = c(&quot;Budget&quot; = &quot;OWB&quot;, &quot;Report&quot; = &quot;JV&quot;)),
        selectInput(&quot;jaar&quot;, &quot;Choose a year:&quot;, choices = c(2021, 2020, 2019, 2018, 2017, 2016, 2015)),
        selectInput(&quot;vuo&quot;, &quot;V/U/O:&quot;, choices = c(&quot;Verplichtingen&quot; = &quot;V&quot;, &quot;Uitgaven&quot; = &quot;U&quot;, &quot;Ontvangsten&quot; = &quot;O&quot;)),
        submitButton(&quot;Submit&quot;)
      ),
  
      mainPanel(
        h4(textOutput(&quot;header&quot;)),
        girafeOutput(&quot;donut&quot;),
        tableOutput(&quot;view&quot;)
      )
    )
  )
  
  server&lt;-function(input, output, session) {
    output$header &lt;- renderText({paste0(&quot;Visual: &quot;, input$fase, &quot; (&quot;, input$vuo, &quot;) &quot;, input$jaar)})
  
    output$donut &lt;- renderGirafe({
      conn &lt;- dbConnect(
        drv = RMySQL::MySQL(),
        dbname = &quot;btabellen&quot;,
        host = &quot;localhost&quot;,
        username = &quot;dbuser&quot;,
        password = &quot;***&quot;)
      on.exit(dbDisconnect(conn), add = TRUE)
      dbGetQuery(conn, 'set character set &quot;utf8&quot;')
      data &lt;- dbGetQuery(conn, paste0(
        &quot;SELECT naam_begroting as begroting, sum(bedrag_t) as bedrag FROM &quot;, input$fase, &quot; WHERE jaar = &quot;, input$jaar, &quot; AND VUO = '&quot;, input$vuo, &quot;' GROUP BY jaar, naam_begroting ORDER BY bedrag DESC;&quot;))
      data$fraction &lt;- data$bedrag / sum(data$bedrag)
      data$fraclbl &lt;- paste0(round(100 * data$fraction, 1), &quot;%&quot;)
      data$ymax &lt;- cumsum(data$fraction)
      data$ymin &lt;- c(0, head(data$ymax, n=-1))
      data$label &lt;- paste0(data$begroting, &quot;: € &quot;, format(data$bedrag, big.mark=&quot;.&quot;, decimal.mark=&quot;,&quot;), &quot; (k)&quot;)
  
      donut_plot &lt;- ggplot(data, aes(y = bedrag, fill = begroting, data_id = begroting)) +
      geom_bar_interactive(
        aes(x = 1, tooltip = label),
        width = 0.1,
        stat = &quot;identity&quot;,
        show.legend = FALSE
        ) +
      coord_polar(theta = &quot;y&quot;) +
      theme_void() +
      theme(legend.position = &quot;bottom&quot;)
  
      girafe(ggobj = donut_plot, opts_selection(type = &quot;single&quot;))
    })
  
    output$view &lt;- renderTable({
      conn &lt;- dbConnect(
        drv = RMySQL::MySQL(),
        dbname = &quot;btabellen&quot;,
        host = &quot;localhost&quot;,
        username = &quot;dbuser&quot;,
        password = &quot;***&quot;)
      on.exit(dbDisconnect(conn), add = TRUE)
      dbGetQuery(conn, 'set character set &quot;utf8&quot;')
      data &lt;- dbGetQuery(conn, paste0(
        &quot;SELECT naam_begroting as Begroting, SUM(bedrag_t) as Bedrag FROM &quot;, input$fase, &quot; WHERE jaar = &quot;, input$jaar, &quot; AND VUO = '&quot;, input$vuo, &quot;' GROUP BY jaar, naam_begroting ORDER BY bedrag DESC;&quot;))
    }, digits=0)
  }
  
  shinyApp(ui=ui, server=server)
  </code></pre>
  <p>So basically, what I would like to achieve is to open the page up with a donut plot of the budget showing all of the totals for the categories. When clicking a category, the donut should update itself to showing the totals per <em>subcategory</em> for the category that was just clicked. Effectively, a click should change the SQL query to <code>&quot;SELECT artikelnaam, sum(bedrag_t) FROM OWB WHERE jaar=2018 AND VUO='U' AND naam_begroting='Financiën'&quot;</code> given the user's selection of these parameters. The renderTable should ideally then show a nested table listing the subcategories, but that's for a different question.</p>
  <p>Any thoughts what I might be doing wrong?</p>