In wurli/quickReport: Generate Excel Reports from SQL using R

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

dir <- file.path(tempdir(), "example-report")
dir.create(dir)
knitr::opts_knit$set(root.dir = dir)

library(usethis)
library(RSQLite)
library(DBI)

This vignette will run through the steps needed to produce an Excel report from SQL using {quickReport}. We'll be looking at folder structures a bit, so we'll load {fs} too:

library(quickReport)
library(fs)

Setting up a report project

Now we'll use create_report() to set up a project with the recommended folder structure:

create_report(path = ".")

create_report() creates a project with several non-standard files and folders:

Excel is used for storing Excel templates (and comes with one you can edit)
Outputs is the default folder where {quickReport} will try to place created
SQL is used for storing SQL scripts that will be used to create a report
config.yml can be used to configure an SQL database (powered by the {config} package)
run-report.R is an R script containing the minimal code needed to run the report.

dir_tree()

Setting up SQL

{quickReport} provides a function to create an example SQLite database in memory containing 3 base datasets: mtcars, beaver1 and C02.

db <- example_db()
RSQLite::dbListTables(db)

Let's add some SQL scripts to the SQL directory. These are the files that will be used to create a report when we run report_from_sql()

# Define some SQL queries
queries <- list(
  "Active Beavers.sql" = "select * from beaver1 where activ = 1;",
  "High MPG Cars.sql"  = "select mpg, cyl, disp from mtcars where mpg >= 30;",
  "Mean C02 Uptake.sql" = "
    select Treatment, avg(uptake) as uptake
    from CO2
    group by Treatment;
  "
)

# Write the queries to the SQL directory
for (q in names(queries)) {
  readr::write_file(queries[[q]], file.path("SQL", q))
}

Let's check the SQL directory to confirm the files were written correctly:

dir_tree("SQL")

Running the Report

We now have what we need to create a {quickReport} report from SQL: a project with the correct file structure and an SQL database. With these in place, we can create a fairly decent report by simply running report_from_sql():

report_1 <- nice_filename("demo-report", dir = "Outputs")
report_from_sql(report_1, sql_connection = db, file_template = NULL)

We can see that a file r report_1 has been created. Let's check the report to see what's been written on each sheet. Notice that the sheets have been given the same names as our SQL scripts:

read_worksheets(report_1)[-1]

Using Transformers

{quickReport} allows the use of 'transformer' functions when producing a report. These can be very useful when reporting from SQL, as many manipulations are much easier to do using R code instead of SQL. Let's rerun the report, but this time we'll apply some transformers too:

library(dplyr, warn.conflicts = FALSE)

# 'High MPG Cars' gets its own transformer that adds an extra column, while 
# the unnamed function will be applied to all other datasets
transformers <- list(
  "High MPG Cars" = function(data) data %>% mutate(best_mpg = mpg == max(mpg)),
  function(data) data %>% mutate(across(where(is.numeric), round))
)

# Generate a second report using transformations
report_2 <- nice_filename("demo-report-transformed", dir = "Outputs")
report_from_sql(
  report_2, sql_connection = db, transformers = transformers, 
  file_template = NULL
)