Automated R Markdown Reporting Pipeline

Project roadmap

1. 01

   Create a sample database

   ~1.5h

   Write setup_db.R that creates a SQLite database (through DBI and RSQLite) with realistic sample sales data: customers with a segment (for example retail, wholesale, online), products and orders with dates and amounts, covering at least 12 months. You can later point the pipeline at PostgreSQL or another database by changing only the connection. Done when: running setup_db.R creates the database with data in every table.

2. 02

   Build the parameterized template

   ~2h

   Create report.Rmd with params in its YAML header: start_date, end_date and segment, with sensible defaults. Show the parameters on the first page, and validate them in the first chunk: dates in the right order and a known segment, stopping with a clear error otherwise. Done when: rendering with rmarkdown::render and custom params shows them in the report, and invalid params stop with a clear message.

3. 03

   Query the data with DBI

   ~2h

   In the report, connect with DBI and run queries with parameters (dbGetQuery with params, never pasting values into the SQL) to get revenue by week, the top 10 products, order counts and average order value for the chosen segment and dates. Disconnect in on.exit so the connection always closes. Handle a period with no orders by showing a message instead of empty charts. Done when: the report shows correct numbers for different params.

4. 04

   Add tables and charts

   ~2.5h

   Build the report sections: a summary with key figures and the change from the previous period of the same length, a weekly revenue chart with ggplot2, a top products table formatted with knitr::kable (or gt), and an orders-by-weekday chart. Use a consistent theme, number formats (thousands separators, currency) and captions. Done when: the HTML report looks professional and reads well.

5. 05

   Render batches from the command line

   ~2h

   Write render_reports.R, run with Rscript and taking arguments for the month (default: last month), the segments (default: all) and the formats (HTML, and PDF if a LaTeX distribution such as TinyTeX is installed). Render each report in a clean environment into reports/YYYY-MM/segment.html. Wrap each render in tryCatch so one failure doesn't stop the rest. Done when: one command renders a report per segment into the right folders.

6. 06

   Log runs and schedule them

   ~1.5h

   Log each render (start, end, duration, output file, success or error message) to logs/render.log with the logger package, and exit with a non-zero code if any report failed. Document in the README how to run the script automatically on the 1st of every month with cron, including the full Rscript path and the working directory. Done when: the log shows every run, and a deliberately broken segment appears as a failure while the rest succeed.

7. 07

   Test the pipeline

   ~2h

   Move the queries and calculations into functions in R/ and test them with testthat against a small test database created in the test setup. Add one test that renders the report with test params into a temporary folder and checks that the file exists and contains the expected headings. Done when: the tests pass.

Resources

• DocsR Markdown: parameterized reports
• DocsDBI package documentation
• Docsknitr documentation
• Docslogger package
• Docscrontab.guru (cron schedule helper)