Report Example

Source: vignettes/report_example.Rmd

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:

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()
#> .
#> ├── Excel
#> │   └── template.xlsx
#> ├── Outputs
#> ├── R
#> ├── SQL
#> ├── config.yml
#> └── run-report.R

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)
#> [1] "CO2"     "beaver1" "mtcars"

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")
#> SQL
#> ├── Active Beavers.sql
#> ├── High MPG Cars.sql
#> └── Mean C02 Uptake.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)
#> 
#> ── Creating new Excel report [1m[36m2021-08-26-runner-demo-report.xlsx[39m[22m ────────────────
#> • Reading SQL scripts from [1m[36mSQL[39m[22m
#> • Reading [1m[36m3[39m[22m datasets from [1m[36m:memory:[39m[22m
#>   • Reading [1m[36mActive Beavers[39m[22m
#>   • Reading [1m[36mHigh MPG Cars[39m[22m
#>   • Reading [1m[36mMean C02 Uptake[39m[22m
#> • Initialising workbook using template [1m[36mdefault[39m[22m
#> • Populating [1m[36m1[39m[22m with report information
#> • Writing [1m[36m3[39m[22m datasets to workbook
#>   • Writing [1m[36mActive Beavers[39m[22m
#>   • Writing [1m[36mHigh MPG Cars[39m[22m
#>   • Writing [1m[36mMean C02 Uptake[39m[22m
#> • Saving workbook as [1m[36mOutputs/2021-08-26-runner-demo-report.xlsx[39m[22m
#> ✔ File saved successfully!

We can see that a file Outputs/2021-08-26-runner-demo-report.xlsx 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]
#> $`Active Beavers`
#>   day time  temp activ
#> 1 346 1730 37.07     1
#> 2 346 1950 37.10     1
#> 3 346 2150 37.53     1
#> 4 346 2230 37.25     1
#> 5 346 2300 37.24     1
#> 6 347  340 37.15     1
#> 
#> $`High MPG Cars`
#>    mpg cyl disp
#> 1 32.4   4 78.7
#> 2 30.4   4 75.7
#> 3 33.9   4 71.1
#> 4 30.4   4 95.1
#> 
#> $`Mean C02 Uptake`
#>    Treatment   uptake
#> 1    chilled 23.78333
#> 2 nonchilled 30.64286

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
)

Looking at this second report, (conveniently named Outputs/2021-08-26-runner-demo-report-transformed.xlsx), we can confirm that the transformations have been applied:

read_worksheets(report_2)[-1]
#> $`Active Beavers`
#>   day time temp activ
#> 1 346 1730   37     1
#> 2 346 1950   37     1
#> 3 346 2150   38     1
#> 4 346 2230   37     1
#> 5 346 2300   37     1
#> 6 347  340   37     1
#> 
#> $`High MPG Cars`
#>    mpg cyl disp best_mpg
#> 1 32.4   4 78.7    FALSE
#> 2 30.4   4 75.7    FALSE
#> 3 33.9   4 71.1     TRUE
#> 4 30.4   4 95.1    FALSE
#> 
#> $`Mean C02 Uptake`
#>    Treatment uptake
#> 1    chilled     24
#> 2 nonchilled     31

To finish, let’s inspect the directory once more to see how our report looks:

dir_tree()
#> .
#> ├── Excel
#> │   └── template.xlsx
#> ├── Outputs
#> │   ├── 2021-08-26-runner-demo-report-transformed.xlsx
#> │   └── 2021-08-26-runner-demo-report.xlsx
#> ├── R
#> ├── SQL
#> │   ├── Active Beavers.sql
#> │   ├── High MPG Cars.sql
#> │   └── Mean C02 Uptake.sql
#> ├── config.yml
#> └── run-report.R