Display only one issue per customer based on the most recent resolution date

I apologize if this has been asked before. I need a report that selects only one (1) Jira Issue per customer based on the most recent resolution date. Example:

4 different Jira issues have been created for Customer A in order to track software upgrades. I only want to see the most recent issue. All other issues are unnecessary for the report.

Ideally, the report will show the most recent software upgrade for each customer.

Any help would be great.

Hi @JJohnson

Welcome to the Community! :rocket:

Start off by importing the “Customer” custom field as a dimension in your account.

Next, you can create a report with the “Customer” dimension in the Rows and define a new calculated measure “Last resolved issue key” in the Measures dimension with the following formula:

CASE WHEN
[Measures].[Issues resolved] > 0
THEN
Cache(
  Order(
    Filter(
      Descendants([Issue].CurrentMember,[Issue].[Issue]),
      [Measures].[Issues resolved] > 0
    )
    ,
    [Measures].[Issue resolution date],
    DESC
  ).Item(0).Key
)
END

This will return the Key of the last resolved issue for each of the Row members.
If you would like to show the resolution date of this last issue, you can define another calculated measure with a formula like this:

CASE WHEN
[Measures].[Issues resolved] > 0
THEN
[Issue].[Issue].GetMemberByKey(
  [Measures].[Last resolved issue key]
).GetDate('Resolved at')
END

Be sure to set the Formatting to “Date” for this measure.

If you wish to have a clickable link that will take you straight to this issue in Jira, you can define a new calculated measure with a formula like this:

CASE WHEN
[Measures].[Issues resolved] > 0
THEN
"<a href='https:// YOUR JIRA URL HERE .com/browse/"
||[Measures].[Last resolved issue key]||"'>"||
[Issue].[Issue].GetMemberByKey(
  [Measures].[Last resolved issue key]
).GetCaption
||"</a>"
END

Set the Formatting to “Text” → “Markdown” for this and replace the “YOUR JIRA URL HERE .com” with your Jira URL.

The end result would be along these lines:

Let me know if this fits your use case!
​Best regards,
​Nauris / eazyBI support

This did work but it did require some optimization. With the number of records we have, I needed to get some help from our Jira Admin to optimize the import so that it would not timeout.

JJ

1 Like