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.

[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:

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, or
2. Calling the Get issues for sprint endpoint for a specific sprint, and then filtering by the work item status, or
3. 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

[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.

[AjayShankar AN]

I wrote many queries, in some I grouped all the sprints of a story. In that query all sprints where this story is present is shown in one row. Eg: 1898 | Sprint1, Sprint2, Sprint3, Sprint4|

I will try other filtering approach.

[AjayShankar AN]

Alright, I changed the approach, instead of calling closedsprint function and then calculating stories, ( in a way reinventing the wheel) I directly called JIRA Sprint Report using the url. Modified the query to parse the data from the report. 

Every count matched with JIRA. Worked like a charm.

Thanks for the interaction.

[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