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

Question

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.

Thanks

unknown · Answer

Or....

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!

Steven F Behnke · Answer

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.

Raphael Dreyfus · Answer

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 = "name@mail.com" 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?