Hands-on_Exercise10

Author

Chen Ho Tung

Published

March 22, 2026

Modified

March 22, 2026

31  Information Dashboard Design: R methods

31.1 Overview

31.2 Getting started

pacman::p_load(lubridate, ggthemes, reactable,
reactablefmtr, gt, gtExtras, tidyverse,readr)

31.3 Importing Microsoft Access database

31.3.1 The data set

For the purpose of this study, a personal database in Microsoft Access mdb format called Coffee Chain will be used.

31.3.2 Importing database into R

In the code chunk below, odbcConnectAccess() of RODBC package is used used to import a database query table into R.

library(RODBC)
con <- odbcConnectAccess2007('data/Coffee Chain.mdb')
coffeechain <- sqlFetch(con, 'CoffeeChain Query')
readr::write_rds(coffeechain, "data/CoffeeChain.rds")
odbcClose(con)

31.3.3 Data Preparation

The code chunk below is used to import CoffeeChain.rds into R.

coffeechain <- read_rds("data/rds/CoffeeChain.rds")

Note: This step is optional if coffeechain is already available in R.

The code chunk below is used to aggregate Sales and Budgeted Sales at the Product level.

product <- coffeechain %>%
  group_by(`Product`) %>%
  summarise(`target` = sum(`Budget Sales`),
            `current` = sum(`Sales`)) %>%
  ungroup()

31.3.4 Bullet chart in ggplot2

The code chunk below is used to plot the bullet charts using ggplot2 functions.

ggplot(product, aes(Product, current)) + 
  geom_col(aes(Product, max(target) * 1.01),
           fill="grey85", width=0.85) +
  geom_col(aes(Product, target * 0.75),
           fill="grey60", width=0.85) +
  geom_col(aes(Product, target * 0.5),
           fill="grey50", width=0.85) +
  geom_col(aes(Product, current), 
           width=0.35,
           fill = "black") + 
  geom_errorbar(aes(y = target,
                    x = Product, 
                    ymin = target,
                    ymax= target), 
                width = .4,
                colour = "red",
                size = 1) +
  coord_flip()

31.4 Plotting sparklines using ggplot2

31.4.1 Preparing the data

sales_report <- coffeechain %>%
  filter(Date >= "2013-01-01") %>%
  mutate(Month = month(Date)) %>%
  group_by(Month, Product) %>%
  summarise(Sales = sum(Sales)) %>%
  ungroup() %>%
  select(Month, Product, Sales)

The code chunk below is used to compute the minimum, maximum and end othe the month sales.

mins <- group_by(sales_report, Product) %>% 
  slice(which.min(Sales))
maxs <- group_by(sales_report, Product) %>% 
  slice(which.max(Sales))
ends <- group_by(sales_report, Product) %>% 
  filter(Month == max(Month))

The code chunk below is used to compute the 25 and 75 quantiles.

quarts <- sales_report %>%
  group_by(Product) %>%
  summarise(quart1 = quantile(Sales, 
                              0.25),
            quart2 = quantile(Sales, 
                              0.75)) %>%
  right_join(sales_report)

31.4.2 sparklines in ggplot2

ggplot(sales_report, aes(x=Month, y=Sales)) + 
  facet_grid(Product ~ ., scales = "free_y") + 
  geom_ribbon(data = quarts, aes(ymin = quart1, max = quart2), 
              fill = 'grey90') +
  geom_line(size=0.3) +
  geom_point(data = mins, col = 'red') +
  geom_point(data = maxs, col = 'blue') +
  geom_text(data = mins, aes(label = Sales), vjust = -1) +
  geom_text(data = maxs, aes(label = Sales), vjust = 2.5) +
  geom_text(data = ends, aes(label = Sales), hjust = 0, nudge_x = 0.5) +
  geom_text(data = ends, aes(label = Product), hjust = 0, nudge_x = 1.0) +
  expand_limits(x = max(sales_report$Month) + 
                  (0.25 * (max(sales_report$Month) - min(sales_report$Month)))) +
  scale_x_continuous(breaks = seq(1, 12, 1)) +
  scale_y_continuous(expand = c(0.1, 0)) +
  theme_tufte(base_size = 3, base_family = "Helvetica") +
  theme(axis.title=element_blank(), axis.text.y = element_blank(), 
        axis.ticks = element_blank(), strip.text = element_blank())

31.5 Static Information Dashboard Design: gt and gtExtras methods

In this section, you will learn how to create static information dashboard by using gt and gtExtras packages. Before getting started, it is highly recommended for you to visit the webpage of these two packages and review all the materials provided on the webpages at least once. You done not have to understand and remember everything provided but at least have an overview of the purposes and functions provided by them.

31.5.1 Plotting a simple bullet chart

In this section, you will learn how to prepare a bullet chart report by using functions of gt and gtExtras packages.

product %>%
  gt::gt() %>%
  gt_plt_bullet(column = current, 
              target = target, 
              width = 60,
              palette = c("lightblue", 
                          "black")) %>%
  gt_theme_538()
Product current
Amaretto
Caffe Latte
Caffe Mocha
Chamomile
Colombian
Darjeeling
Decaf Espresso
Decaf Irish Cream
Earl Grey
Green Tea
Lemon
Mint
Regular Espresso

31.6 sparklines: gtExtras method

Before we can prepare the sales report by product by using gtExtras functions, code chunk below will be used to prepare the data.

report <- coffeechain %>%
  mutate(Year = year(Date)) %>%
  filter(Year == "2013") %>%
  mutate (Month = month(Date, 
                        label = TRUE, 
                        abbr = TRUE)) %>%
  group_by(Product, Month) %>%
  summarise(Sales = sum(Sales)) %>%
  ungroup()

It is important to note that one of the requirement of gtExtras functions is that almost exclusively they require you to pass data.frame with list columns. In view of this, code chunk below will be used to convert the report data.frame into list columns.

report %>%
  group_by(Product) %>%
  summarize('Monthly Sales' = list(Sales), 
            .groups = "drop")
# A tibble: 13 × 2
   Product           `Monthly Sales`
   <chr>             <list>         
 1 Amaretto          <dbl [12]>     
 2 Caffe Latte       <dbl [12]>     
 3 Caffe Mocha       <dbl [12]>     
 4 Chamomile         <dbl [12]>     
 5 Colombian         <dbl [12]>     
 6 Darjeeling        <dbl [12]>     
 7 Decaf Espresso    <dbl [12]>     
 8 Decaf Irish Cream <dbl [12]>     
 9 Earl Grey         <dbl [12]>     
10 Green Tea         <dbl [12]>     
11 Lemon             <dbl [12]>     
12 Mint              <dbl [12]>     
13 Regular Espresso  <dbl [12]>

31.6.1 Plotting Coffechain Sales report

report %>%
  group_by(Product) %>%
  summarize('Monthly Sales' = list(Sales), 
            .groups = "drop") %>%
   gt() %>%
   gt_plt_sparkline('Monthly Sales',
                    same_limit = FALSE)
Product Monthly Sales
Amaretto 1.2K
Caffe Latte 1.5K
Caffe Mocha 3.7K
Chamomile 3.3K
Colombian 5.5K
Darjeeling 3.0K
Decaf Espresso 3.2K
Decaf Irish Cream 2.7K
Earl Grey 3.0K
Green Tea 1.5K
Lemon 4.4K
Mint 1.5K
Regular Espresso 1.1K

31.6.2 Adding statistics

First, calculate summary statistics by using the code chunk below.

report %>% 
  group_by(Product) %>% 
  summarise("Min" = min(Sales, na.rm = T),
            "Max" = max(Sales, na.rm = T),
            "Average" = mean(Sales, na.rm = T)
            ) %>%
  gt() %>%
  fmt_number(columns = 4,
    decimals = 2)
Product Min Max Average
Amaretto 1016 1210 1,119.00
Caffe Latte 1398 1653 1,528.33
Caffe Mocha 3322 3828 3,613.92
Chamomile 2967 3395 3,217.42
Colombian 5132 5961 5,457.25
Darjeeling 2926 3281 3,112.67
Decaf Espresso 3181 3493 3,326.83
Decaf Irish Cream 2463 2901 2,648.25
Earl Grey 2730 3005 2,841.83
Green Tea 1339 1476 1,398.75
Lemon 3851 4418 4,080.83
Mint 1388 1669 1,519.17
Regular Espresso 890 1218 1,023.42

31.6.3 Combining the data.frame

Next, use the code chunk below to add the statistics on the table.

spark <- report %>%
  group_by(Product) %>%
  summarize('Monthly Sales' = list(Sales), 
            .groups = "drop")
sales <- report %>% 
  group_by(Product) %>% 
  summarise("Min" = min(Sales, na.rm = T),
            "Max" = max(Sales, na.rm = T),
            "Average" = mean(Sales, na.rm = T)
            )
sales_data = left_join(sales, spark)

31.6.4 Plotting the updated data.table

sales_data %>%
  gt() %>%
  gt_plt_sparkline('Monthly Sales',
                   same_limit = FALSE)
Product Min Max Average Monthly Sales
Amaretto 1016 1210 1119.000 1.2K
Caffe Latte 1398 1653 1528.333 1.5K
Caffe Mocha 3322 3828 3613.917 3.7K
Chamomile 2967 3395 3217.417 3.3K
Colombian 5132 5961 5457.250 5.5K
Darjeeling 2926 3281 3112.667 3.0K
Decaf Espresso 3181 3493 3326.833 3.2K
Decaf Irish Cream 2463 2901 2648.250 2.7K
Earl Grey 2730 3005 2841.833 3.0K
Green Tea 1339 1476 1398.750 1.5K
Lemon 3851 4418 4080.833 4.4K
Mint 1388 1669 1519.167 1.5K
Regular Espresso 890 1218 1023.417 1.1K

31.6.5 Combining bullet chart and sparklines

Similarly, we can combining the bullet chart and sparklines using the steps below.

bullet <- coffeechain %>%
  filter(Date >= "2013-01-01") %>%
  group_by(`Product`) %>%
  summarise(`Target` = sum(`Budget Sales`),
            `Actual` = sum(`Sales`)) %>%
  ungroup() 
sales_data = sales_data %>%
  left_join(bullet)
sales_data %>%
  gt() %>%
  gt_plt_sparkline('Monthly Sales') %>%
  gt_plt_bullet(column = Actual, 
                target = Target, 
                width = 28,
                palette = c("lightblue", 
                          "black")) %>%
  gt_theme_538()
Product Min Max Average Monthly Sales Actual
Amaretto 1016 1210 1119.000 1.2K
Caffe Latte 1398 1653 1528.333 1.5K
Caffe Mocha 3322 3828 3613.917 3.7K
Chamomile 2967 3395 3217.417 3.3K
Colombian 5132 5961 5457.250 5.5K
Darjeeling 2926 3281 3112.667 3.0K
Decaf Espresso 3181 3493 3326.833 3.2K
Decaf Irish Cream 2463 2901 2648.250 2.7K
Earl Grey 2730 3005 2841.833 3.0K
Green Tea 1339 1476 1398.750 1.5K
Lemon 3851 4418 4080.833 4.4K
Mint 1388 1669 1519.167 1.5K
Regular Espresso 890 1218 1023.417 1.1K

31.7 Interactive Information Dashboard Design: reactable and reactablefmtr methods

In this section, you will learn how to create interactive information dashboard by using reactable and reactablefmtr packages. Before getting started, it is highly recommended for you to visit the webpage of these two packages and review all the materials provided on the webpages at least once. You done not have to understand and remember everything provided but at least have an overview of the purposes and functions provided by them.

In order to build an interactive sparklines, we need to install dataui R package by using the code chunk below.

#remotes::install_github("timelyportfolio/dataui")

Next, you all need to load the package onto R environment by using the code chunk below.

#library(dataui,reactablefmtr,reactable)

31.7.1 Plotting interactive sparklines

Similar to gtExtras, to plot an interactive sparklines by using reactablefmtr package we need to prepare the list field by using the code chunk below.

report <- report %>%
  group_by(Product) %>%
  summarize(`Monthly Sales` = list(Sales))
sales
# A tibble: 13 × 4
   Product             Min   Max Average
   <chr>             <dbl> <dbl>   <dbl>
 1 Amaretto           1016  1210   1119 
 2 Caffe Latte        1398  1653   1528.
 3 Caffe Mocha        3322  3828   3614.
 4 Chamomile          2967  3395   3217.
 5 Colombian          5132  5961   5457.
 6 Darjeeling         2926  3281   3113.
 7 Decaf Espresso     3181  3493   3327.
 8 Decaf Irish Cream  2463  2901   2648.
 9 Earl Grey          2730  3005   2842.
10 Green Tea          1339  1476   1399.
11 Lemon              3851  4418   4081.
12 Mint               1388  1669   1519.
13 Regular Espresso    890  1218   1023.

Next, react_sparkline will be to plot the sparklines as shown below.

reactable(
  report,
  columns = list(
    Product = colDef(maxWidth = 200),
    `Monthly Sales` = colDef(
      cell = react_sparkline(report)
    )
  )
)

31.7.2 Changing the pagesize

By default the pagesize is 10. In the code chunk below, arguments defaultPageSize is used to change the default setting.

reactable(
  report,
  defaultPageSize = 13,
  columns = list(
    Product = colDef(maxWidth = 200),
    `Monthly Sales` = colDef(
      cell = reactablefmtr::react_sparkline(report$`Monthly Sales`)
    )
  )
)

31.7.3 Adding points and labels

In the code chunk below highlight_points argument is used to show the minimum and maximum values points and label argument is used to label first and last values.

reactable(
  report,
  defaultPageSize = 13,
  columns = list(
    Product = colDef(maxWidth = 200),
    `Monthly Sales` = colDef(
      cell = react_sparkline(
        report,
        highlight_points = highlight_points(
          min = "red", max = "blue"),
        labels = c("first", "last")
        )
    )
  )
)

31.7.4 Adding reference line

In the code chunk below statline argument is used to show the mean line.

reactable(
  report,
  defaultPageSize = 13,
  columns = list(
    Product = colDef(maxWidth = 200),
    `Monthly Sales` = colDef(
      cell = react_sparkline(
        report,
        highlight_points = highlight_points(
          min = "red", max = "blue"),
        statline = "mean"
        )
    )
  )
)

31.7.5 Adding bandline

Instead adding reference line, bandline can be added by using the bandline argument.

reactable(
  report,
  defaultPageSize = 13,
  columns = list(
    Product = colDef(maxWidth = 200),
    `Monthly Sales` = colDef(
      cell = react_sparkline(
        report,
        highlight_points = highlight_points(
          min = "red", max = "blue"),
        line_width = 1,
        bandline = "innerquartiles",
        bandline_color = "green"
        )
    )
  )
)

31.7.6 Changing from sparkline to sparkbar

Instead of displaying the values as sparklines, we can display them as sparkbars as shiwn below.

reactable(
  report,
  defaultPageSize = 13,
  columns = list(
    Product = colDef(maxWidth = 200),
    `Monthly Sales` = colDef(
      cell = react_sparkbar(
        report,
        highlight_bars = highlight_bars(
          min = "red", max = "blue"),
        bandline = "innerquartiles",
        statline = "mean")
    )
  )
)

31.8 Reference