Intro

Kita akan membuat sebuah dashboard untuk memonitor list pekerjaan yang dimaintain menggunakan Aplikasi Smartsheet. Pada aplikasi smartsheetnya, kita harus membuat Row Report terlebih dahulu, dimana format report dapat disesuaikan dengan kebutuhan.

Tahap berikutnya adalah mengambil data dari Report Smartsheet menggunakan API dalam Python lalu menampilkan dashboard sederhana atas project tsb.

Load Library yang Dibutuhkan

library(plotly)
library(ggplot2)
library(DT)           #datatables
library(dplyr)        #praprocess data
library(lubridate)    #format tanggal
library(formattable)  #formattable()
library(tidyr)        #untuk fungsi spread(), complete()
library(ggrepel)      #align pie chart label
library(stringr)      #word()

Ambil Report Smartsheet

Karena API smartsheet tersedia dalam Python, maka kita akan menggunakan library reticulate untuk menjalankan script Python di aplikasi R.

#running python script
library("reticulate")
py_config()

## python:         C:/ProgramData/Anaconda3/python.exe
## libpython:      C:/ProgramData/Anaconda3/python37.dll
## pythonhome:     C:/ProgramData/Anaconda3
## version:        3.7.4 (default, Aug  9 2019, 18:34:13) [MSC v.1915 64 bit (AMD64)]
## Architecture:   64bit
## numpy:          C:/ProgramData/Anaconda3/Lib/site-packages/numpy
## numpy_version:  1.17.4
## 
## NOTE: Python version was forced by RETICULATE_PYTHON

Pada saat men-deploy aplikasi ke server (misal : ShinyApps), kita harus menyiapkan environment untuk Python kita dengan cara :

# ------ uncomment when deploy to shinnyapps # ------ 
# create a new virtual env
reticulate::virtualenv_create("~/r-reticulate",
                              python= '/usr/bin/python3')

# use virtual env
reticulate::use_virtualenv(virtualenv = '~/r-reticulate',
                           required = TRUE)

# install python library on virtual env
reticulate::virtualenv_install(packages = c("smartsheet-python-sdk"),
                               envname = "~/r-reticulate",
                               pip = TRUE)
# ------ end of uncomment when deploy to shinnyapps # ------ 

Setelah environment Python siap, kita bisa mengambil data report dari Smartsheet lalu ditampung ke CSV file yang bernama report.csv dengan cara :

# get smartsheet data
ss <- reticulate::import('smartsheet')
xSheet <- ss$Smartsheet("[token]")
tmp <- xSheet$Reports$get_report_as_csv("[report_id]", "data/", "report.csv")

Token dan report ID harus diisi sesuai dengan informasi dari Smartsheet masing-masing.

Setelah download CSV file berhasil, kita akan load CSV file tsb :

#read csv data
report_file <- "data/report.csv"
reports <- read.csv(report_file)
data_last_modified <- file.info(report_file)$mtime

Contoh data yang dihasilkan dari Report Smartsheet adalah :

Keterangan :

1. Sheet.Name : masing-masing project akan di-manage di sheet terpisah, sehingga Sheet.Name = Project Name.
2. Sprint : masing-masing project akan terdiri terdiri dari beberapa Sprint.
3. Primary : berisi keterangan sesuai Type.
4. Type : berisi Tipe keterangan : Project / Features / Backlogs / Tasks / Checklists / Issues.
5. Status : status dari masing-masing items.
6. Assigned.To : PIC yang mengerjakan masing-masing items.
7. Start : plan tanggal mulai pengerjaan items.
8. Finish : plan tanggal selesai pengerjaan items.
9. X..Complete : %progress masing-masing items.

Setelah Load Data berhasil, kita akan mengubah tipe data sesuai kebutuhan:

#change data types
reports$Primary <- as.character(reports$Primary)
reports$Start <- as.Date(reports$Start, "%d/%m/%y")
reports$Finish <- as.Date(reports$Finish, "%d/%m/%y")
reports$X..Complete <- as.integer(gsub("%", "", reports$X..Complete))

Buat Dashboard Sederhana

Pertama, kita akan membuang tipe “Project” dari dashboard kita karena kita akan memonitor detail projectnya.

#exc "Project" Type
reports <- reports %>% 
  filter(
    Sheet.Name == project_name,
    Type != "Project"
  ) %>% 
  mutate(
    completed = if_else(Status=="Complete", "Y", "N")
  )

# buat 2 baris untuk task yang sama bagi developer & tester
reports_detail_1 <- reports %>% 
  select(Sprint, Primary, Type, Status, Assigned.To, Start, Finish, X..Complete, completed)

reports_detail_2 <- reports %>% 
  select(Sprint, Primary, Type, Status, Review.By, Start, Finish, X..Complete, completed) %>% 
  mutate(Primary = paste(Primary,"(Testing)")) %>% 
  rename(Assigned.To = Review.By)

reports_detail <- bind_rows(reports_detail_1, reports_detail_2)
reports_detail %>% arrange(Primary) %>% head(10)

##    Sprint                                                    Primary     Type
## 1                                                                            
## 2                                                          (Testing)         
## 3       1                                     01. Template Web Admin Features
## 4       1                           01. Template Web Admin (Testing) Features
## 5       1                                  01.01. Template Web Admin Backlogs
## 6       1                        01.01. Template Web Admin (Testing) Backlogs
## 7       1                                    01.01.A. Pilih template    Tasks
## 8       1                          01.01.A. Pilih template (Testing)    Tasks
## 9       1           01.01.B. Ubah tampilan web lama ke template baru    Tasks
## 10      1 01.01.B. Ubah tampilan web lama ke template baru (Testing)    Tasks
##      Status Assigned.To      Start     Finish X..Complete completed
## 1                             <NA>       <NA>          NA         N
## 2                             <NA>       <NA>          NA         N
## 3   Testing             2020-01-06 2020-02-11          88         N
## 4   Testing             2020-01-06 2020-02-11          88         N
## 5   Testing             2020-01-06 2020-02-11          88         N
## 6   Testing             2020-01-06 2020-02-11          88         N
## 7   Testing             2020-01-06 2020-02-11          83         N
## 8   Testing             2020-01-06 2020-02-11          83         N
## 9  Complete             2020-01-06 2020-01-10         100         Y
## 10 Complete             2020-01-06 2020-01-10         100         Y

1. Menampilkan jumlah Features per Status :

# get features
reports_features <- reports %>% 
  filter(
    Type == "Features"
  )

reports_features %>% 
  filter(Status == "Not Started") %>% 
  # filter(Status == "In Progress") %>% 
  # filter(Status == "Complete") %>% 
  summarise(count = n())

##   count
## 1     2

2. Menampilkan jumlah Backlogs dan Tasks per Status :

current_sprint <- reports %>%
  filter(
    Sprint == curr_sprint
  ) %>% 
  group_by(Type, Status) %>%
  summarize(count = n()) %>%
  ungroup() %>% 
  group_by(Type) %>% 
  mutate(subtotal = sum(count)) %>%
  mutate(prop = round(count / subtotal *100,1)) %>%
  mutate(ypos = cumsum(prop) - 0.5*prop ) %>% 
  mutate(Status = factor(Status, levels = c("Not Started", "In Progress", "Testing", "Complete"))) %>%
  arrange(factor(Status, levels = c("Not Started", "In Progress", "Testing", "Complete")))

# Backlogs piechart
current_sprint %>% 
  filter(Type=='Backlogs') %>% 
  ggplot(aes(x="", y=prop, fill=Status)) +
  geom_bar(stat="identity", width=1, color="white") +
  coord_polar(theta='y') +
  geom_text(aes(label = count, x = 1.1),
            position = position_stack(vjust = 0.5),
            color="white",
            size=6) +
  scale_fill_manual(values = c('Not Started' = "#001f3f", 
                               'In Progress' = "#3c8dbc", 
                               'Testing' = "#f39c12", 
                               'Complete' = "#00a65a")) +
  theme_minimal() +
  theme(axis.title.x=element_blank(),
        axis.text.x = element_blank(),
        axis.title.y=element_blank(),
        axis.text.y=element_blank(),
        panel.grid  = element_blank(),
        legend.direction = "horizontal",
        legend.position = c(0.5, 0.05)) +
  labs(fill = NULL,
       x = NULL,
       y = NULL,
       title = NULL)

# Sprint piechart
current_sprint %>% 
  filter(Type=='Tasks') %>% 
  ggplot(aes(x="", y=prop, fill=Status)) +
  geom_bar(stat="identity", width=1, color="white") +
  coord_polar(theta='y') +
  geom_text(aes(label = count, x = 1.1),
            position = position_stack(vjust = 0.5),
            color="white",
            size=6) +
  scale_fill_manual(values = c('Not Started' = "#001f3f", 
                               'In Progress' = "#3c8dbc", 
                               'Testing' = "#f39c12", 
                               'Complete' = "#00a65a")) +
  theme_minimal() +
  theme(axis.title.x=element_blank(),
        axis.text.x = element_blank(),
        axis.title.y=element_blank(),
        axis.text.y=element_blank(),
        panel.grid  = element_blank(),
        legend.direction = "horizontal",
        legend.position = c(0.5, 0.05)) +
  labs(fill = NULL,
       x = NULL,
       y = NULL,
       title = NULL)

3. Menampilkan tasks yang overdue per hari ini :

overdue_task <- reports %>% 
  filter(
    Sprint == curr_sprint,
    Finish < Sys.Date() & Status != "Complete",
    Type == "Tasks"
  ) %>% 
  mutate(
    Overdue = Sys.Date() - Finish,
    Finish = format(Finish, "%d %b %y"),
    X..Complete = paste(X..Complete, "%")
  ) %>% 
  select(Overdue, Primary, Finish, Assigned.To, Review.By, Status, X..Complete) %>% 
  rename(Tasks = Primary, "Due Date" = Finish, "Assigned To" = Assigned.To, 
         "Review By" = Review.By, "Progress" = X..Complete) %>% 
  arrange(-Overdue)

# define colors
customRange <- c(min(overdue_task$Overdue), max(overdue_task$Overdue)) # custom min / max values
colors <- csscolor(gradient(as.numeric(c(customRange, overdue_task$Overdue)), "#f26c4f", "#9e0b0f"))
colors <- colors[-(1:2)] ## remove colors for min/max
fmt <- formatter("span", 
                 style = function(x){formattable::style(
                                     display = "block",
                                     padding = "0 4px",
                                     'border-radius' = "4px",
                                     'background-color' = colors,
                                     color = "white",
                                     width = "80px")})

# show data
as.datatable(
  formattable(
  overdue_task, 
  align =c("r","l","l","l","l","l","r"), 
  list(
        Overdue = fmt
      )
  )
) %>%
  formatStyle(c('Overdue','Due Date'), 'text-align' = 'center') %>% 
  formatStyle('Progress', 'text-align' = 'right')

4. Menampilkan workload masing-masing PIC untuk task2 yang belum selesai dikerjakan:

workload <- reports_detail %>% 
  filter(
    Sprint == curr_sprint,
    Type == "Tasks"
  )  %>% 
  mutate(Assigned.To = word(Assigned.To, 1)) %>% 
  group_by(Assigned.To, completed) %>% 
  summarise(count=n()) %>%
  mutate(completed = factor(completed, levels = c("N", "Y"))) %>%
  complete(completed) %>% 
  mutate(count = zoo::na.fill(count, fill = 0))

workload$Assigned.To <- factor(workload$Assigned.To, 
                               levels = unique(workload$Assigned.To)[order(workload$count, decreasing = TRUE)])

# show plot
workload %>% 
  ggplot(aes(x=Assigned.To, y=count, fill=completed)) +
  geom_bar(stat="identity", position=position_dodge()) +
  geom_text(aes(label=paste0(count, " Tasks")), vjust=-1, size=6, position = position_dodge(width = 1)) +
  ylim(0, max(workload$count) + 1) +
  theme_minimal() +
  theme(axis.title.x=element_blank(),
        axis.text.x = element_text(size=14,face="bold"),
        axis.title.y=element_blank(),
        axis.text.y=element_blank(),
        legend.position = "bottom", 
        legend.direction = "horizontal") +
  scale_fill_manual(values=c('red','#008548')) +
  guides(fill=guide_legend(title="Complete Tasks"))

5. Menampilkan task-task yang blm selesai dikerjakan :

deadlines_by_pic <- reports %>% 
  filter(
    Sprint == curr_sprint,
    Status != "Complete",
    Finish >= Sys.Date(),
    Type == "Tasks"
  ) %>% 
  group_by(Assigned.To) %>% 
  mutate(min_finish = min(Finish)) %>% 
  ungroup() %>% 
  filter(
    Finish <= min_finish
  ) %>% 
  mutate(
    due_in = Finish - Sys.Date(),
    Finish = format(Finish, "%d %b %y"),
    X..Complete = paste(X..Complete, "%")
  ) %>% 
  select(due_in, Primary, Finish, Assigned.To, Review.By, Status, X..Complete) %>% 
  arrange(due_in) %>% 
  rename(Tasks = Primary, "Due Date" = Finish, "Assigned To" = Assigned.To, 
         "Review By" = Review.By, "Due.In" = due_in, "Progress" = X..Complete)

# define colors
customRange <- c(min(deadlines_by_pic$Due.In), max(deadlines_by_pic$Due.In)) # custom min / max values
colors <- csscolor(gradient(as.numeric(c(customRange, deadlines_by_pic$Due.In)), "#ff6700", "#ffa366"))
colors <- colors[-(1:2)] ## remove colors for min/max
fmt <- formatter("span", 
                 style = function(x){formattable::style(
                                     display = "block",
                                     padding = "0 4px",
                                     'border-radius' = "4px",
                                     'background-color' = colors,
                                     color = "white",
                                     width = "80px")})

# show data
as.datatable(
  formattable(
  deadlines_by_pic, 
  align =c("r","l","l","l","l","l","r"), 
  list(
        "Due.In" = fmt
      )
  )
) %>%
  formatStyle(c("Due.In",'Due Date'), 'text-align' = 'center') %>% 
  formatStyle('Progress', 'text-align' = 'right')

Penutup

Setelah semua data selesai dipersiapkan, langkah berikutnya adalah men-deploy dashboard ke server (misal : ShinyApps). Hasilnya dapat dilihat disini : Smartsheet Dashboard.

Dashboard ini dapat digunakan oleh Management untuk melihat summary dari masing-masing project sehingga dapat mengindentifikasi delivery sebuah project apakah on time atau delay berdasarkan dari tasks dan workload masing-masing PIC.