Jul 14, 2022
The Tower of Babel by Pieter Bruegel the Elder, 1563
Scrumdog is a software program I created to help me automate my Jira Issue reporting. Scrumdog downloads Jira Issues to a local SQLite database for analysis and reporting. It is a really helpful tool if you are managing a complex, multi party project with both Jira and non-Jira users and you report regularly to a Steering Committee.
You can download Scrumdog from here.
Table of Contents:
Jira has many real-time dashboards and reports (“filters”) that can be used to manage Jira Issues. Jira also provides you with the ability to export issues in CSV, XML or Excel/Google Sheets format. However – if you find yourself (like me) downloading thousands of Jira issues regularly (e.g. daily) for reporting and analysis – it might be easier to just automate your steps.
The approach I took to automate Jira reporting consists of two steps:
Step 1 - Get the Jira Issues and save them in a local SQL database
Step 2 - Create Excel report(s) from local SQL database
In a previous post I showed how this can be done with Python. However to simplify the process further, Scrumdog can be used to automate Step 1 without you having to write any code.
The following was important to me as a user of Scrumdog:
Almost any general purpose programming language could be used to develop Scrumdog. The evaluation criteria I used to pick a suitable programming language:
I came up with the following shortlist of possible programming languages to use: Python, C#, Java, D, OCaml and Go. This list is based on my experience and requirements – yours will most definitely be different.
I excluded Python, C# and Java based on the negatives listed above and decided to create simple prototypes using D, OCaml and Go. The prototypes were 20-30 lines of code each to test the following:
After evaluating the prototypes, Go was excluded based on the following:
With OCaml and D left, I decided to proceed further with both. After a few hundred lines of code – I had the basics working for both the OCaml and D programs.
At this point it was almost a coin-flip on which one to use, but I decided to continue with OCaml based on the following:
After a few more days of developement and testing I had a working version of Scrumdog and was able to use it in my own automated reporting workflow.
You would need a tool to test SQL queries and manage the SQLite database files. I find the SQLite command-line shell program a bit too minamilistic to use. My prefered way to create and test SQL queries is with a (free) program called DB Browser for SQLite. With DB Browser for SQLite it is also possible to display the database table contents and/or edit data, which is very handy during testing.
Although not the focus of this post – you may want to create Excel reports from SQLite. Python has some great libraries to get you going very quickly. Here is an example of how to create a simple Excel report using data from SQLite.
import pandas as pd import sqlite3 # Get data from SQLite db con = sqlite3.connect("jira.db") sql = "select * from zz_issues; " df = pd.read_sql_query(sql, con) con.close() # write to Excel df.to_excel ("report.xlsx", index=False, header=True)
Some enhancements in the pipeline:
Feel free to test out Scrumdog – which you can download for free from here.
 Scrumdog website
 Scrumdog source code
 DB Browser for SQLite
 SQLite website
 JIRA Cloud REST API documention
 Post: using Python to automate your Jira reports
 TIOBE Programming Community index
Solving the Jane Street puzzle of December 2022
Jane Street puzzle Feb 2021 SOLVED! OCaml to the rescue
Solving the Jane Street puzzle of Dec 2020 - Backtracking with OCaml
Automate your Jira reporting with Python and Excel
Solving the Jane Street Puzzle of June 2020; Circle Time