Can I retrieve data from Jira Database using sql instead of jql ?

[Jurgen Sërbo]

I want to do something like this to make reports easy for me and for my unit.

[Nic Brough -Adaptavist- · Accepted solution]

No, you don't.  Seriously, that is NOT going to "make reports easy".

To perform a full search for JIRA issues matching something, you could have to join the main issue table up to 30 times, and via multi-layered joins.  Even a search for a simple custom field is 3 or 4 joins, depending on the type.

SQL is the worst way to do it.

Have a look in the marketplace for reporting add-ons.  And maybe tell us what report(s) you are looking for as we might be able to point you at something better.

[Jurgen Sërbo]

Thanks

[Nic Brough -Adaptavist-]

Tell them they're wrong.  You have the support of all of us who've looked at the database!

[Jurgen Sërbo]

Please, can you suggest me an add-on to use for this porpuse. 
Thanx :D

[Nic Brough -Adaptavist-]

You'll want one that understands the JIRA database properly.  There's quite a few around, but I'd lean towards EazyBI and Arsenale's Dataplane.  They're very different beasts, so it's worth a look at both to compare them for your needs, and any others you find in the marketplace.

[Jurgen Sërbo]

Hey there, 

Please can u say to me if it is a good way to connect Jira DB with Access, for reports to come easy ? 

Thanx

[Nic Brough -Adaptavist-]

No.  Absolutely not.

Access is not a proper database and it's pretty much impossible to use it as a back-end for JIRA.  Even if you could get it to work (you won't), you are totally unsupported and won't be able to upgrade it.

If you mean you want to keep JIRA on a proper supported database and then try to use Access for reporting, you'll still be trying to use SQL to read JIRA, which we've already discussed as being a terrible thing to do and never "easy".

[Jurgen Sërbo]

I asked this, because someone here has made some queries in access and this is the way that we report LogWorked and the add-on we use for time tracking is : https://marketplace.atlassian.com/plugins/org.everit.jira.timetracker.plugin/server/overview

[Nic Brough -Adaptavist-]

Your user will have to download copies of the work logged data to mangle in Access.

By far the best way to do that is to use the REST API to pull out the data they want, then write something to change it into the shape you need.Or use the functions in the add-on you have?

[Jurgen Sërbo]

Every time i make the timetracker report, i export all the fields in excel and then copy them in the access table and re-run the query to track the time worked.

[Nic Brough -Adaptavist-]

Ok, that's another way to do it. 

Although I would question why your add-on doesn't do the report you want.

[Jurgen Sërbo]

Becuase : 
Senior = Grade 1
Specialist = Grade 2
Junior = Grade 3 

and if a seniour user has loged 43210 sec we calculate this : 43210/60/60/8/grade of the user.

[Nic Brough -Adaptavist-]

Tempo's folio add-on does that doer of thing.

[Jurgen Sërbo]

So sthm is clear, in Jira almost everything is done using add-ons.. 
Am I right ? 

[Nic Brough -Adaptavist-]

No.  Projects, issues, fields, workflows, time logs, comments, etc - everything that is related to "issue tracking" is done in JIRA

When the main product can't meet your specific needs because it's a generalised application, then you start looking at add-ons

[Jurgen Sërbo]

Ok thanks for your help.. :)