Help with JQL to write a custom filter / report

[alexhobday]

Hey all. I need some help writing a custom report or filter whichever makes the most sense.

I would like to create a report that lists the following:

Pull results from multiple projects
Show 52 rows, one for each week
Limit results by label = Maintenance
Limit results for the current year

Column 1: Week 1, 2, 3 etc
Column 2: Date range for that week
Column 3: List ticket numbers that are clickable to relevant ticket
Column 4: Total each week's logged hours in last column

Last row: Running total of all hours for year so far

[alexhobday]

Hi All, I was able to find what I needed with the 

Timesheet Tracking for Jirav4.7.6

app, if anyone needs this for the future.

Thanks for all your responses.

[Gracjan Wesołowski _HeroCoders_]

Hi @alexhobday 

you might consider using time-tracking apps that in most cases have a customizable report of tracked time on issues.

I can recommend our Clockwork Automated Time Tracking & Timesheets Pro, or Free app which offers you the possibility to review timesheets and create reports that can be configured in various ways using break-down and filtering options.

Using timesheets, you can create a report and filter it based on several options including labels, or other fields. You can choose a range for a whole year and break down the data by chosen fields. This information can be also exported to excel files.

Please find below the sample of how the timesheet looks like:

If you have any questions you can contact us via our help center.

Cheers!

Gracjan

[Aron Gombas _Midori_]

If you can use Excel, then you can produce a report like this (more samples) with a click using the Better Excel Exporter app:

This is, in fact, a standard Excel pivot table. You can re-configure it to group by project, issue type, status, year/month/date, etc. see all the available dimensions in the top right!

(Discl. it is a paid and supported app developed by our team. Free for 10 users!)

[Amay Purohit_RVS]

Hi @alexhobday 

If you would be interested in a mktplace app, you can try the add-on we have developed. You can easily view time entries across multiple tickets not just on a per ticket basis.

Worklogs Time Tracking & Timesheets

The app allows to filter/Categorize/Group By your worklogs data and a timesheet view too to track individual time spent. 

Disclaimer : I work for the vendor who built this app

[Mary from Planyway]

Hi @alexhobday 

It's Mary from Planyway for Jira

Creating a custom report in JIRA that matches your specifications requires a combination of JQL for filtering issues and some scripting or report configuration to format the output as desired. JIRA does not natively support custom reports with such specific requirements directly in the UI, but you can achieve your goal through a few steps, possibly involving external tools or JIRA add-ons like eazyBI, ScriptRunner, or JIRA's API for custom development.

Here's a high-level approach to achieve your desired report:

1. Create a JQL Query to Filter Issues

First, you need to create a JQL query to filter issues by your criteria (multiple projects, label = Maintenance, issues created in the current year). Here is a basic example of what the JQL might look like:

project in (PROJECTKEY1, PROJECTKEY2) AND labels = "Maintenance" AND created >= startOfYear()

Replace PROJECTKEY1, PROJECTKEY2 with your actual project keys. This query filters issues in the specified projects with the "Maintenance" label that were created since the start of the current year.

2. Use a Scripting Add-on or API for Custom Reporting

Since JIRA's built-in reporting features may not support creating a report with the structure you described directly, you might need to use an add-on like ScriptRunner to write a custom script or use JIRA's REST API to fetch the data and generate the report externally.

Using ScriptRunner

With ScriptRunner, you can write a Groovy script to execute your JQL query, iterate through the results, and organize them by week. You would also calculate the total logged hours for each week and for the year.

Using JIRA's REST API

If you prefer to generate the report externally (e.g., in a Python script), you can use JIRA's REST API to execute the JQL query and fetch the necessary data. You would then write a script to process this data according to your requirements, including calculating date ranges for each week, listing ticket numbers, and summing up logged hours.

3. Output Format

Regardless of the method you choose (ScriptRunner, JIRA API, etc.), you'll need to format the output to include:

• A row for each week of the year, with 52 rows total.
• Columns for week number, date range, clickable ticket numbers, and total logged hours for each week.
• A final row with the running total of all hours for the year.

4. Tools for Generating the Report

Depending on your technical preference and available resources, you could:

• Develop a custom web application or script (using Python, for example) that uses the JIRA API to generate this report.
• Use a reporting add-on like eazyBI, which offers advanced reporting capabilities and can be configured to produce complex reports. eazyBI allows for importing JIRA data and creating custom reports with a drag-and-drop interface, which might be able to meet your requirements with less need for custom development.
• ScriptRunner for JIRA allows for enhanced scripting capabilities directly within JIRA, including custom JQL functions, scripted fields, and reports.

[Fabian Lim]

Hi @alexhobday

Unfortunately, you won't be able to do this with jqls.  You will have to consider 3rd party plugins from the marketplace. 

I would recommend something like Eazybi for a customized report like the one you mentioned. Others can chime in with their recommendations.

Fabian

[alexhobday]

Hi @Fabian Lim thanks for your response. I wanted to avoid plugins if possible but maybe that's not possible. Do you know of a way to total up time entries across multiple tickets not just on a per ticket basis?

[Fabian Lim]

You can try building separate components, but not everything into 1. For example I'm pretty sure you can track hours vs project with a time series gadget they have.