nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL and using jira database for reporting

Mary Mark

Would like to know if there is a better / efficient way to query all jira issues and display the custom fields and values used. We have tried using sql however involves many joins, which makes query inefficient and slow. Looking for suggestions and alternatives.

Thanks

Accepted answers

All comments

Nic Brough -Adaptavist-

SQL is the single worst possible way you can try to report on Jira.

In descending order of good ways to do it:

Use the built in reports, gadgets, dashboards, etc
Find or write specific reports to fill in gaps when option 1 fails you (it will, especially if you want historic reports)
If 1 + some 2s still are not enough, use the more generalised 3rd party reporting apps built by Atlassian experts. EazyBI and Dataplane are the starting points for comparisons
Build something that interrogates Jira over its REST interface

Mohamed Adel

I add my voice to Nic, plus SQL option is the worst way, JIRA DB schema also is very complicated.

I would recommend to use one of the following :

- EasyBI

- PowerBi, you can integrate with Rest API and get all the issue information for reporting.

Mary Mark

Hi Nic,

First, thank you for your response. I have been actually following your blog/responses in the community for a while and I've seen answers regarding the topic about SQL not being efficient tool for reporting from the JIRA database.

I wanted to ask for your insight/opinion as well as other experts in this community regarding the following:

1. At our company we have many intake tools and we are trying to simplify by choosing JIRA as the primary intake tool. However currently intake from JIRA does not align well to our cost tracking system called Changepoint and does not provide key metrics reporting on support cost per application hence reporting for Total cost of ownership cannot be produced. Some new key (custom fields) to be created in JIRA have been identified, which supports this cost reporting to be added onto JIRA with a governance process to produce key metrics. We are looking to combine 3 systems: JIRA, Changepoint (resource cost tracking system) and Architecture library (an application repository) to allow cross referencing to occured to allow for reporting and analysis.

2. All 3 sources of data are hosted on 3 SQL databases with fields required from tables in each of the 3 databases. Initially was thinking of grabbing the tables from the 3 databases that contained the required fields and creating a new database.

What are thoughts around methods to produce/generate reporting? I hear that SQL would not be a very good option

Thanks in advance.

Piotr Bojko

With Smart QL you are able query with SQL and do reports based on those queries: https://marketplace.atlassian.com/apps/1218767/smart-ql?hosting=server&tab=overview

It honors JIRA permission system - you want see data from JIRA your are not meant to see.
It gives you an option to connect to JIRA via JDBC with dedicated JDBC driver.
It gives you simplified views for JIRA data.
It allowes you to use JQL inside SQL - so you can search data with regular JQL and then make summaries on this with power of SQL (aggregates functions, windows and partitions available).

Mohamed Adel

Hi Mary,

I would like to share with you my experience with a similar situation, I had also to generate report by collection the data from 5 different sources (SQL,EXCEL,API ).

After a lot of search & trial to have one integrated dashboard,I would definitely recommend Microsoft §PowerBI for you.

You can add more data sources and play with the table relations, formatting the data and present it in a modern and simple way.

Thanks

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server