how to find CSP related subtask,ccds and worklog hrs , i am using jiradatabase ?

[saurabh verma]

I have access to database and i see following tables are available 

Jiraissue, issuelink , worklog ,project.

I need something like this

                    Linked tickets   Linked -subtask    Hour_logged

CSP- 100                                                                 7

                    BIJIRA                                                 8

                                               Subtask 1                 3

                                               subtask 2                5

                                               -------

                    CCDS                                                 10

                                             subtask 1                  5

                                             subtask 2                  5

I am really struggling to ge the same from JIRA database                                                

[Nic Brough -Adaptavist- · Accepted solution]

This sort of reporting should not be done with database access.   The database is not in the slightest bit suitable for reporting, and building a report, even a simple one like you've shown here, is a nightmare.  The SQL for your report needs 21 joins and a load more if anyone has logged more work, plus you'll need to do some arithmetic if they have.

Please,stop looking at the database.  Use the REST API to fetch the data you want to report on, it is easier, faster and won't take your server down.

[saurabh verma]

Hi Nic ,

Thanks for the answer , but i do not have option to use rest API , i have to use the database and i am trying to create a reporting data model .A sour business did not want to spend on API.

Best,

S

[Nic Brough -Adaptavist-]

You might want to explain to them that spending on the API is a lot cheaper than trying to do it with SQL.

You are totally unsupported if you read the database, it can easily change between versions, and Server is going away soon, so you'll end up throwing all your work away (no database access any more) and re-doing it with the API anyway.