How to insert report data in Excel in a way it can be refreshed?

This question is in reference to Atlassian Documentation: Setting up service desk reports

I do not want to have to export each time the result of a query into a new CSV file and then reprocess the file.

I would like to have dynamic data in a cell and refresh it in Excel, whenever I need.

More specifically, I would like to have in my Excel cells a call to JIRA that will return the number of open tickets for a specific user and that I could refresh easily, using the Excel data refresh button.

Of course, it is a column of tens of users with tens of similar calls.

Your help on how to do it would be well appreciated.


2 answers

0 vote
Steven Behnke Community Champion Aug 21, 2016

There's no way to just 'do this.' Excel does not work that way. Your best bet would probably be a script or a VBA script inside of excel.

Thanks Steve for your prompt answer.

I was hoping to be able to use the Excel "Get External Data" function to do this, but let's say that I need to learn more about Excel so that I could write a VBA script inside Excel:

How can I access the data of number of open/closed/open this week/closed this week/... tickets for every single customer of my JIRA Service Desk projects?

Query like:

reporter = "" AND resolution = Unresolved AND project in (MYDESK1,MYDESK2) AND "Customer Request Type" is not EMPTY

returns a list of tickets.

How can I get instead of the list, the total number of tickets?


Steven Behnke Community Champion Aug 21, 2016

I only have Excel 2011 which appears to have a lame Get External Data function, I'm not sure I can help much with that feature.

JIRA will only ever return a list of issues. It's fairly easy to convert a list into a count in Excel though, once you get it there.

Typically you would do this on a JIRA Dashboard, a Two-Dimensional Chart should show you the counts you're looking for.


Once you get the report setup in search issues, just setup a subscription and it will automatically send the new information to you.

Otherwise the dashboard approach Steven suggested may be just what you need!

Suggest an answer

Log in or Join to answer
Community showcase
Alexey Matveev
Published Saturday in Jira

How to run Jira in a docker container

Everything below is tested on Ubuntu 17.10. I prefer to use Jira in a docker container because: 1. I can install Jira with a couple of commands. 2. I can start and stop Jira just by starting and s...

363 views 6 8
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot