JQL aggregate function

[Chi-Yen Ho]

how can I create aggregate filter results for a dashboard like the velocity report that you can select from a active sprint board? I want to show the story points per sprint in a dashboard 

[elita_kalane]

Hi  @Chi-Yen Ho 

If you are willing to try an Atlassian Marketplace app, check out eazyBI for Jira Cloud. 

eazyBI is a reporting and analytics add-on that lets you build exactly what you're describing - story points aggregated by sprint, shown as a chart, and embedded directly in your Jira dashboard.

Out of the box, it comes with predefined sprint metrics such as:

Sprint Story Points committed - story points assigned to a sprint at the time it started
Sprint Story Points completed - story points from issues that were in "Done" status when the sprint closed
Running Story Points velocity - rolling average across the last N closed sprints
You can check out an example of the Sprint Velocity Chart here:
Sprint Velocity Chart

And a full Sprints overview dashboard here:
Sprints Overview 

The reports can be added as gadgets to your Jira dashboard, and you can filter by project, board, issue type, and more. You can also export sample report definitions and import them directly into your account to get started quickly.

Disclosure: I work for the eazyBI team.

Best,

Elita from support@eazybi.com

[Rahul_RVS]

Hi @Chi-Yen Ho 

If you would be interested in a mktplace app for this requirement, we have built a solution for this exact need

Agile Velocity & Sprint Status Gadgets 

The app comes with few dashboard gadgets to track team member's / team's velocity / productivity based on story points / time spent / issue count in a sprint  and also track sprint status with multiple parameters.

You can easily customize the gadgets to view only the sprints you are interested in.

Disclaimer : I am part of the team which developed this app

[Paul Glantschnig _Appfire_]

Hi @Chi-Yen Ho ,

JQL itself doesn't support aggregate functions (SUM, COUNT, AVG, etc.), so there's no way to write a JQL query that returns "total story points grouped by sprint." It's a filter language, not a reporting language.

That said, here are a few native options to get what you're after:

1. Velocity Report (Board-level) You mentioned this already. It's available from any Scrum board under Reports > Velocity Report. The limitation is that it's tied to a specific board and can't be embedded as a dashboard gadget.

2. Sprint Burndown / Sprint Report gadget You can add a "Sprint Burndown" or "Sprint Report" gadget to your dashboard. These show story point totals for a single sprint at a time, but don't give you a cross-sprint comparison on one chart.

3. Dashboard gadgets with custom JQL The "Filter Results" gadget shows individual issues (not aggregates). The "Two Dimensional Filter Statistics" gadget can give you counts by field, but it doesn't sum story points.

4. Advanced Roadmaps (Jira Premium) If you're on a Premium plan, Advanced Roadmaps has capacity and progress views that aggregate estimates, though it's more of a planning tool than a dashboard widget.

In short, native Jira doesn't have a straightforward way to show "story points per sprint" as a simple dashboard chart. It's a very common gap.

--

If you're open to solutions from the Atlassian Marketplace, you might want to check out JXL for Jira. My team and I build it.

JXL gives you a spreadsheet-like view of your Jira issues with built-in aggregation ("sum-ups"). You could set up a view that groups your issues by sprint and shows story point totals per group, like this:

You can also embed any JXL sheet as a Jira dashboard gadget, so it integrates right into your existing dashboard:

Beyond sprint-level aggregation, JXL also supports grouping and summing by assignee, epic, label, component, or any other field. So if your reporting needs grow, the same tool covers it.

Any questions, just let me know.

Best, Paul

Disclosure: I work for the team that builds JXL.