Sql

Some projects evolve naturally by combining previous efforts into something new and useful. This happened recently when I fullfiled a client request by extending an existing RStudio Shiny application using what initially seemed like unrelated, separate components.
End users could upload a software vendor inventory text file in the existing Shiny application. Key attributes in that file were joined across several database views and a Shiny data table report returned the aggregated data to the user.

I built a Shiny application recently for a client that accepts uploaded files and matches data attributes in those files to corporate directory information stored on the server in an RDS file. After some trial and error I was able to piece together something useful and produce a self-service Shiny solution. This example illustrates the key concept of properly referencing reactive data objects when accessing them outside the function that created them in the following “contents()” and “mergedData()” references.

Using recursive SQL common table expressions (CTE) to build corporate hierachy reports is well documented use case for SQL CTE’s. I leveraged a SQL CTE to build a Shiny data table report that returns a client’s top down hierarchy based on user selection of an employee ID. The Shiny app uses the squr and RODBC pacakges to call the SQL script.
That report was useful but you never really know what someone wants until they ask.