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.
SELECT
statementsThe 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.
Positives:
Negatives:
Positives:
Negatives:
Positives:
Negatives:
Positives:
Negatives:
Positives:
Negatives:
Positives:
Negatives:
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.
[0] Scrumdog website
https://scrumdog.app/
[1] Scrumdog source code
https://github.com/whoek/scrumdog
[2] DB Browser for SQLite
https://sqlitebrowser.org/
[3] SQLite website
https://www.sqlite.org/
[4] JIRA Cloud REST API documention
https://developer.atlassian.com/cloud/jira/platform/rest/
[5] Post: using Python to automate your Jira reports
https://whoek.com/b/use-jira-api-to-create-excel-reports
[6] TIOBE Programming Community index
https://www.tiobe.com/tiobe-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