Fetch data from JIRA to excel

Hi. I am trying to count number of bugs which are reported every week in specific projects with JQL (I am not sure how to get count only). and then I want to get this number in excel, so that I can see how many bugs have been reported, in comparison to last week, last month, last year. And I am drawing excels graphs using that data. Excel is storing my data and drawing graphs for me but I need to have data from JIRA automatically based on filters, I apply.

So I need counts of bugs in projects (not bugs), historic data and how to fetch them directly from excel so that I don't need to go to JIRA. I trying to automate my reports. We have JIRA version 6.4.9

I hope I am able to describe my question and now waiting for response.

 

Thank you.

5 answers

You can easily and automatically import Jira Cloud data directly into Excel by following these steps:

 

1. Create a filter view in Jira with all of your desired columns, save it, and make a note of the path.  Example: https://mycompany.atlassian.net/issues/?filter=11940

2. Change the path to https://mycompany.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/11940/SearchRequest-11940.csv

Note that going to this URL in your browser returns a CSV file.

3. Start Excel, and select Data menu, New query, From Other Sources, From Web

4. A dialog will appear asking you for the URL. Paste in your CSV URL from step 2, and click OK

5. You will be prompted with a permissions dialog.  Select Basic authentication, enter your Jira username and password, and click Connect.

6. After Excel connects, you will see a dialog with a preview of the table.  Click Load to load it into the current worksheet.

 

From this point forward, your Jira credentials will be cached, and you can update the query at any time by clicking Data/Refresh all.

This is exactly what I needed. I had been downloading the CSV, putting it into a folder and connecting to it that way. It never occurred to me that I could directly link Power Query to JIRA through a Web data connection. Brilliant! This saves me a huge amount of time.

If you enter you credentials wrong or need to change them, it's not easy to find as you're not prompted again after entering them. Here's how to fix it. I'm using Excel 2016 (Office 365):

  1. Right click your Power Query entry in "Queries & Connections" select Edit
  2. Click "Data source settings". A list of data sources is displayed.
  3. Find the data source you want to modify and select "Edit Permissions"
  4. Under "Credentials" you can change your login type, from using Windows Credentials, to Basic (to enter your own username/password) etc.
  5. For Privacy Level, I used "Private" in my setting.

Hope that helps and thanks for the info!

The path does not seem to work with JIRA v6.4.13.  My filter is in the format: https://mycompany.atlassian.net/jira/issues/?filter=39787

would someone know the corrsponding CSV path?

I tried various choices like https://mycompany.atlassian.net/jira/sr/jira.issueviews:searchrequest-csv-current-fields/39787/SearchRequest-39787.html  but no luck.  

searchrequest-printable works but it creates 2 rows if I include the labels column so wanted to try CSV.

The URL's have not changed for me.  Not sure why your path is different. The way I determined the CSV path was by looking at the network traffic in Fiddler when I clicked the link to download a CSV of the report.  You can use the same technique.

Thanks I don't see an option to download csv files in the jira instance we have.  Perhaps it has been disabled. 

0 votes
Timothy Chin Community Champion Sep 02, 2015

You'll probably be able to find some reporting add ons in the marketplace:

For example:

 

 

getIssueList from JIRA Command Line Interface (CLI) produces a CSV file that can be imported into Excel.

SQL for JIRA is a standard SQL API for reporting on JIRA with support for SQL aggregation functions like COUNT, SUM, etc. It also supports JIRA JQL, therefore you can execute:

SELECT COUNT(*) FROM ISSUES WHERE JQL = 'project=MYPROJECTID and type = Bug' 

The database SQL query above will execute the JIRA JQL query and count the number of issues returned.

You could write a small Java program to save the results in a file with CVS format, for instance.

SQL for JIRA is really powerful add-on and it would be able to provide all the data sequence at once (all the bugs for a project grouped weekly for a period of time).

SQL for JIRA  can be integrated seamlessly with Birt Reports for JIRA if you want to create a nice chart graph and display it on a JIRA Dashboard.

In this way, you would be able to see the graph up to date on JIRA itself at any time.

Use the Better Excel Plugin that makes creating this report easy:

  1. Write all issues to include in the report to a worksheet (make sure you export the project and creation date for each issue)
  2. Calculate the creation year, month, week from the creation date using Excel functions
  3. Create a pivot chart from this worksheet, using the project field on the horizontal axis, and the creation year, month, week on the other
  4. Draw a pivot chart from the pivot table

If you have the working template, you can refresh this any time using the latest issue data.

 

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,780 views 11 18
Join discussion

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