How do use JQL to query all Stories and Tasks completed in the Last Sprint ?

[Michael Green]

How do use JQL to query all Stories and Tasks completed in the Last Sprint ?  Without manually hard-coding the name of the last sprint ?

project = PMO AND issuetype in (story,task, SUB-TASK) AND Sprint in closedSprints() AND STATUS = Done and sprint = 1008 ORDER BY ASSIGNEE

[Alexey Pavlenko _App Developer_]

Hi @Michael Green ,

Natively, it's not possible, unfortunately. The only way I see you can work around it is to name the latest sprint in the same manner all the time, therefore you won't need to update you hardcoded JQL filter.

Alternatively - you can install my app - Multi-team Scrum Metrics & Retrospectives.

You can select as many sprints as you need - including just one. The same applies to metrics - you can select only 'Completed Scope'.

If you need what you see in the view in the form of JQL - you can click 'View in Issue Navigator'.

The app has many other features - constructing custom JQL metrics, conducting quantifiable retrospectives, etc.

Best regards,
Alexey

[Bartek Szajkowski _ Orbiscend OU]

Hello @Michael Green 

Hope my answer meets you well.
I'm Bartek from Orbiscend OU

If you are open for third-party app, I would like to recommend to check ARGON - Powerful JQL Search.

The previousSprint function lets you reference the last closed sprint dynamically by board name — no hard-coded sprint ID needed.

Replace sprint = 1008 with issue in previousSprint("Your Board Name")

Example - direct replacement for your query:

project = PMO
AND issuetype in (Story, Task, Sub-task)
AND issue in previousSprint("PMO Board")
AND status = Done
ORDER BY assignee

ARGON Powerful JQL Search  you can find on Marketplace.

I hope you will find the above solution useful.

Greetings

Bartek Orbiscend OU

[Iryna Komarnitska_SaaSJet_]

Hi, @Michael Green 

Yes, you can use:

project = PMO AND issuetype in (Story, Task, "Sub-task") AND sprint in closedSprints() AND status = Done ORDER BY assignee

However, based on Atlassian’s JQL documentation, closedSprints() returns items from completed sprints in general, not specifically the most recently closed sprint. Atlassian documents closedSprints() as “Search for work items that are assigned to a completed Sprint,” with the example sprint in closedSprints().

Atlassian’s Sprint field documentation also shows that the supported Sprint functions are only openSprints() and closedSprints(), and that Sprint does not support >, >=, <, or <=. Because of that, there isn’t a native JQL way to dynamically say “last closed sprint only” without specifying the sprint name or ID.

So in native Jira JQL, your options are:

• use sprint in closedSprints() to get items from all closed sprints, or

• specify the sprint name/ID if you need only one exact sprint.

Source: Atlassian Support — JQL functions and JQL fields.

Alternatively, if you’re open to using third-party apps, I’d recommend Time in Status, an application developed by my team. It includes a Sprint Report that helps you analyze your sprint productivity and clearly identify which tasks were included, completed, added, deleted, or carried over to the next sprint.

I hope you find it helpful!

[Michael Green]

So none of that detailed response actually meets my use case i.e. to show only items marked as DONE in the last sprint.

[Trudy Claspill]

Hello @Michael Green 

How would you determine which sprint was "the last sprint"?.

The content of a sprint is not restricted to a project/space. It isn't even truly restricted to a Board. Technically Jira allows any work item to be added to any sprint, as long as the user has the necessary permissions.

Also there is not a native Jira JQL function to filter for "last sprint" since there is no standard definition for what that is.

Can you provide more information about your use case and the problem you are trying to solve by creating this filter?

[Michael Green]

Trudy - based on the JQL example I provided I am OBVIOUSLY only interested in items in one project.  So whilst I take your point ...... I think my use case is fairly obvious given I've supplied a hardcoded sprint number in the example i.e. I want to use a function to return the last closed sprint in the project.  Please let me know what i should have made clearer about that.

[Trudy Claspill]

Hello @Michael Green 

Perhaps I miscommunicated. I will try again.

How do use JQL to query all Stories and Tasks completed in the Last Sprint ?

There is ambiguity in what you mean by "last sprint". You did not say "...issues set to Done in the last sprint where"last" means the most recently completed sprint based on the timestamp for when sprints were completed." It is therefore possible that you could be identifying the "last" sprint based on a naming convention for sprints, or some other criteria I have not thought of.

Without manually hard-coding the name of the last sprint ?

That statement did not provide me with additional insight into how you defined "last sprint".

 I am OBVIOUSLY only interested in items in one project

<snip>

I want to use a function to return the last closed sprint in the project

Sprints are not associated to Projects and are not "in" Projects. Sprints are associated to Scrum Boards. Boards are not necessarily limited to issues from only one project, since it is possible to have boards based on filters that reference multiple projects. And Jira does not prevent users from adding items from one project to a sprint that should only contain items from a different project, if they have sufficient permissions.

Therefore, specifying a project as part of the criteria does not constrain the source of the Sprints and does not give us a way to identify the "last" sprint.

If your project is a Team-managed project one might assume that you are interested in the native, built-in Scrum board for that project. However there is no mention as to whether you are working with a Team-managed or Company-managed project.

Regardless of the initial ambiguity, now that you have clearly defined what you mean by "last sprint" the answer is that there is no native JQL solution to get what you want. Of the JQL-extending apps that I know about (i.e. ScriptRunner and others) none include a function that enables filtering for the last sprint as you require.

What would you want to do with the results of the query?

One alternative is to use the Jira Cloud Software API to retrieve the completed sprints associated with the Scrum board of interest. You could then parse the results to figure out which was the last sprint completed for that board. You could then use the ID obtained from that in the JQL you have specified.

If emailing a list of the results to specified individuals will satisfy your requirements, then that could maybe be accomplished through an Automation Rule. You would use the Send Web Request action to make the API call. I have not tried to work out how exactly the parsing of the results would be done. You could then use a Lookup Work Items action to execute the JQL. And finally you could use the Send Email action to send the list to the desired recipients.

• I would probably enhance the JQL to include criteria for
  • the item being in a "done" status currently
  • the item having been changed to that "done" status prior to the completion of the sprint.
  • the item not having a subsequent status change after that.

Otherwise you could end up with items that have subsequently been reopened, or were closed after the sprint ended.

[Michael Green]

Was ANYONE ELSE confused by my use of the term 'the last sprint' ?  I've been doing this a long time and that's the term EVERYBODY I've ever worked with has referred to as the last completed sprint.

[Ollie Guan]

Hi @Michael Green ,

Yes, you can, but it depends on which “Analytics” you mean and how exact you want the “last sprint” logic to be.

With only Jira Cloud Standard native features:

• You can approximate “last sprint” by date + closedSprints() in a filter, then use dashboards and reports.

• You cannot have a purely dynamic “lastClosedSprint()” JQL function.

With Atlassian Analytics (SQL):

• You can precisely compute “the most recent closed sprint” and list all completed Stories/Tasks.

BTW, this app can basically cover your needs for “last completed sprint” reporting, but in a different way than JQL.

https://marketplace.atlassian.com/apps/1233252/sprint-reviewer-pro-automate-sprint-reports-data-insights?hosting=cloud&tab=overview

[Trudy Claspill]

@Ollie Guan 

Isn't Analytics for Jira available only for Enterprise subscriptions?

[Michael Green]

@Ollie Guan Please give an example of using date.