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; } } }

JIRA API returning incorrect count of closed stories

AjayShankar AN

I used the function closedSprints() to get the closed items and used pagination to get all the closed items. Then filtered it with sprint number. It returned 80 items for sprint 4. In JIRA Sprint report for sprint 4, it was 89 stories. When I verified those extra 9 from sprint report they were spill over from sprint 1,2,3 and were closed in sprint 4. But these stories were not present in ClosedSprint() query in sprint 4, but in sprint2, or sprint 3.

How to tackle this mismatch. I used PowerBI to write queries. I dont want to use JIRA Connectors as it is against company policy.

I want to write power Query which extracts closed, spilled over stories for a sprint and accurately matched with Sprint Report data, using Power BI Desktop and JIRA Rest APIs.

Accepted answers

All comments

Bill Sheboy

Hi @AjayShankar AN

Would you please clarify how you are searching for those work items? Specifically, which endpoint are you using?

For example, are you doing one of these:

Calling the Search for issues using JQL enhanced search endpoint, passing JQL that uses the closedSprints() function, and then filtering the results for a specific sprint ID, or
Calling the Get issues for sprint endpoint for a specific sprint, and then filtering by the work item status, or
Something else

And, for the work items which are missing from the results, have you confirmed all pages where returned for the endpoint calls?

Kind regards,
Bill

AjayShankar AN

Hi Bill,

The first 1. Calling the Search for issues using JQL enhanced search endpoint, passing JQL that uses the closedSprints() function, and then filtering the results for a specific sprint ID

Yes, confirmed all pages were returned. Saw the results were there, but in different sprints.

Best,

Ajay

Bill Sheboy

Thanks for that information, and please try to stay with one thread when responding. That will help people reading this question in the future know if there are multiple possible solution approaches. Thanks!

FYI -- the closedSprints() function only indicates a work item has a value in the sprint field for a completed sprint, but not which specific one it was completed in.

Here are some additional questions to help identify the cause:

If you run that same JQL within Jira as a search, are those 9 work items returned?
If the 9 work items are returned, does the sprint field contain your Sprint 4?
When you look at an example of one of the 9 work items, do you see in the history if anyone removed it from Sprint 4 and later re-added it?
Is the team using multiple boards in the project, perhaps with parallel sprints in progress at once?

AjayShankar AN

JQL query is issuetype = Story AND project = XXGRTN AND sprint in closedSprints(). For eg, let's take story 1898.
In JIRA it shows all the sprints from 1-4 under sprint column for story 1898. In Power BI query, 1898 is shown under sprint 2 ( Don't know why). So this alters the total story count of sprint 4.
No such entries in it's history.
We have multiple board in the project, but none are used in parallel. Now all other boards are dormant.
And sorry for different thread, it was late midnight, I didn't notice it. Thanks for alerting.

Bill Sheboy

Thanks; this helps to isolate the cause a bit.

Just to confirm, when you use that JQL with the REST API call for an issue search, does your example story 1898 show all the expected sprints 1, 2, 3, and 4 in the returned sprint data of the response? You could really isolate this if you temporarily add that specific issue's key to the JQL for the search, perhaps calling the endpoint from a browser tab.

If the sprint value is present, the problem is in your filtering approach in PowerBI.

Welcome!

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

Quick Links

Find more posts tagged with

jira

cloud

rest-api

jira-cloud