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.
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
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):
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.
You'll probably be able to find some reporting add ons in the marketplace:
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:
If you have the working template, you can refresh this any time using the latest issue data.
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 ...
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot