How do I remove duplicate sub tasks from a query?

[Mark Bedford]

Hi,

I've been looking and experimenting for hours and can't find a solution.

In a project I have setup for a team we have the following setup

Initiative

 Epic

    Story

    Story

    Story

        Sub-task (Summary is product A)

        Sub-task (Summary is product B)

        Sub-Task (Summary is product C)

    Story

 Epic

    Story

    Story

    Story

        Sub-task (Summary is product A)

        Sub-task (Summary is product B)

        Sub-Task (Summary is product D)

        Sub-task (Summary is product E)

        Sub-task (Summary is product F)

        Sub-Task (Summary is product G)

    Story

This layout continues into 56 different Epics totally 622 sub tasks across the project.

This is fully intentional, as a requirement from the team. It has additional things like labels and components so I could show the relationship of the "product" across different Epics.

So what I'm trying to do now is replicate the reporting that they were doing in Excel (requirement was to move the team away from using Excel and into JIRA), part of this is to show how many UNIQUE sub-tasks there out of the 622 on a dashboard. 

The creation of a dashboard isn't the problem, it's the JQL query  to do something like "exclude duplicate summary" as the summary will be exactly the same.

It's worth noting I am on :-

JIRA Service Management Application v10.3.8

I work for a large company, don't have Admin access nor can I download/install any plugins.

Hopefully somebody can give some guidance on what seems simple but I'm rapidly losing my hair on it :-)

Thank you in advance.

[Marc -Devoteam-]

Hi @Mark Bedford 

First of all Jira is not Excel, its not a spreadsheet tool.

Stating that you want to replace excel with Jira doen't relate.

As mentioned by @Karim Buzdar there are no distinct or unique functions. JQL provides finds issues related to the search, but the overview is not an spreadsheet, it is a list of found items.

And use 2-dimension gadgets to show filter results, based on Component (Yes, use components or a custom field with defined values) Labels can cause issues, as they are case sensitive

Jira is not a reporting tool, but a tool to help, structure and visualize work.

Issue withIdentical summaries, are still separate issues, why would you want to show this as a single item, users can work on both issues with the same summary simultaneously

[Mark Bedford]

Hi, yes I know Jira is not Excel, I've used JIRA for the last 10 years in my organisation and I am very familiar with it's capabilities.

I know what JIRA's main purpose is, which is why there was a request to use it. The Team are working from Excel, management don't want them to work from Excel and to transition to be more agile, open and transparent. 

It's incredibly complicated what I've created and there is a requirement which I'm trying to help fill. 

I'm already using 2-dim gadgets to display other requirements. I'm aware of issues with labels. 

"Issue withIdentical summaries, are still separate issues, why would you want to show this as a single item, users can work on both issues with the same summary simultaneously" As mentioned, there is a requirement for this and to show the relationship between the Epic's.

It was just a request if there was a JQL query that would exclude duplicate summary title, much like Excel you can select "Remove Duplicates" not why I'm using it in this way or what it should do.

Thanks.

[Marc -Devoteam-]

Hi @Mark Bedford 

I understand, and for what you require, no there is no such option in JQL.

Not even with apps that extend JQL capabilities within Jira, as it finds issues based on the given query.

So your question then gets BI related, so you could use eazyBI, or you could see to get the information outside Jira in a BI tool, but based on your response this is not what management wants.

See this for eazyBI, https://community.eazybi.com/t/remove-duplicate-summary/12134 

[Karim Buzdar]

Hi,

Transitioning from Excel to Jira can be tricky since standard JQL doesn't have a 'unique' or 'distinct' function for summaries.

Since you don't have admin access or plugins, here are two quick workarounds:

Two-Dimensional Filter Statistics Gadget: On your dashboard, try using this gadget. Set the axes to a field that represents your products (like Labels or Components). This will group the 622 sub-tasks and give you a unique count per product.

Official Excel/Google Sheets Add-in: If your company allows it, pull the JQL results into a sheet. You can then use the =UNIQUE() formula to get the count and display that chart on your dashboard via the 'External Content' gadget.

Without JQL extensions, leveraging Dashboard gadgets for grouping is your best bet!"

Hope this helps.

[Mark Bedford]

Hi Karim, 

I'm already using the Two-Dimension filter for another view, I will adjust things and see if I can get it to show the unique count.

Sadly any add-in's are blocked. It's a restricted/sensitive company.

I think this morning I've come to the conclusion I will try EazyBi for this, hopefully that will do it. 

Thanks for comments. 

[Karim Buzdar]

eazyBI is a powerhouse for exactly this kind of complex reporting. It's ability to pull and transform that data should save you a lot of time.

Good luck!