Create report if the custom date field has been empty for more than 7 days

markus_neuwald_canon_at April 8, 2024

Hello!

We use Jira to assign installation tasks to technicians.

After assigning the task to the technician, the technician has 7 working days to contact the customer and organize an installation date.

There is a custom field configured in Jira called "Installation Date" [Date] where the technician enters the organized date.

Now I would like to create an annual report on how often the technician needs more than 7 working days.

Is this possible and can you please give me a tip on how to do this?

Kind regards,

Markus

2 answers

1 accepted

1 vote
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 8, 2024

Hi @markus_neuwald_canon_at 

That information is in the issue history, but it cannot be easily queried for a custom field such as your date field.  There are probably marketplace addons which can do this type of reporting: https://marketplace.atlassian.com/

It might also be possible to create an automation rule to read the issue histories, and calculate the time between issue created and setting that field, and save that in a custom field.  There are some constraints on this approach, so if you need this type of report often consider checking the marketplace for options.

Kind regards,
Bill

markus_neuwald_canon_at April 8, 2024

Hi Bill,

thank you for your reply.

Is there a marketplace addon that you would recommend for my request?

kind regards,

Markus

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 8, 2024

I have not used any like that before so I do not have a recommendation. 

Perhaps search on "issue history" in the marketplace to find examples that may help, installing trial versions for comparisons.

markus_neuwald_canon_at April 8, 2024

thank you.

1 vote
Aron Gombas _Midori_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
April 9, 2024

You can do this with the Better Excel Exporter app and a few clicks in Excel.

First, to have the source data, export all installation tasks to Excel, at least with the "Installation Date" field, the "Created" field and the repeated field change history. It will look like this:

jira-issues-selected-fields-with-repeating-change-history.png

Then, using the Excel filter narrow the spreadsheet to those rows where the "Installation Date" field was changed from a blank old value to a non-blank new value. It will show the event of setting up the date for each installation task.

Then:

  1. Add new column "Time to organize", and use formula to calculate the difference between the "Created" column and the "Change Time" column.
  2. Add another column "Within 7 days?", and use a formula which results TRUE if the previous column is less than 7 days.

That's it! With this, you have the precise time required and whether that was less than 7 days.

From this point, you can filter to show only those where the 7-days criterion wasn't met, create a pivot chart to calculate the percentage for each assignee (technician) and do other kind of data analysis easily!

(Pro tip: if you have to create this report frequently, consider creating an Excel report template  that will deliver the report fully ready-made with one click!)

markus_neuwald_canon_at April 9, 2024

Hi Aron, thanks for your suggestion. It sounds great and i will give it a try.

kind regards,

Markus

Like Aron Gombas _Midori_ likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS
AUG Leaders

Atlassian Community Events