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.

Thanks

2 answers

0 votes
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 = "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?

 

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.

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!

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 27, 2018 in Portfolio for Jira

Introducing a new planning experience in Portfolio for Jira (Server/DC)

In the past, Portfolio for Jira required a high degree of detail–foresight that was unrealistic for many businesses to   have–in   order to produce a reliable long-term roadmap. We're tur...

2,506 views 15 20
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