Get Workload Pie Chart Report Data From REST API

cball

I've seen a couple scattered Atlassian Community questions about similar things, but nothing addresses the exact situation I am in.

I need the data from the Workload Pie Chart Report for a Jira project via the Jira REST API.

I have tried using the JQL queries for each section of the pie chart along with the search API endpoint. I set the `maxResults` parameter to zero using the advice from this article to get the total number of issues (via the `total` value) from the query without having it return any actual data about the issues (which seems to make the API response much faster and use less data).

However, this unfortunately isn't exactly what I need; I need the hours-spent value from each issue, summed up into a single metric for each pie chart section. This is with the end-goal of using chart.js to build my own chart inside a custom Microsoft SharePoint web part, if it matters.

One way to do this is to get every issue from the JQL query for each pie chart section via the API, get the issue's hours-spent value, and compute the aggregate sum in my programming language. That is not feasible though, primarily because the API caps the amount of issues that it returns for a query. Even if it did return the full, accurate amount of issues along with their data, paginating through them and doing the sum manually via the API would take an eternity and I'd probably get rate-limited, haha. So that is not a feasible method.

I've heard something about a Jira SQL database as well... that sounds moderately promising for a fast enough computation and ease of querying. I don't actually know if that exists though, or if it would even be accessible via API (edit: after more research it seems the SQL is only available on Jira Data Center, not Jira Cloud). I've also heard about an app called ScriptRunner and it seems to offer extended JQL functionality per its description. Maybe that's a possibility?

If anyone has any advice on how I can accomplish this, via JQL or another method, that would be much appreciated.

unknown

Hello @cball 

Unfortunately, there is no easy way out. You are just going to have to bite the bullet and use a properly constructed JQL search to get all the issues that you need, then do all the summing of the time spent per issue and constructing the pie chart in your app. If you have to deal with a large data set that is paginated and needs to downloaded in batches, then so be it.

The REST API just isn't designed to do any of that pre-preparation work for you... it just provides data. Remember, don't do open ended, unconstrained searches and you won't hit the rate limits; only search for what you really need.

If you want to avoid the development overhead of this approach, 95% of the time you'll find you can solve the reporting problem with an off the shelf reporting tool for Jira or Confluence, and they are reasonably priced.