Track progress/generate MI across multiple Jira projects

Question

Hoping this community can solve my problem!

I wish to get what i describe as a 'horizontal' view of Epics & stories across multiple 'vertical' Jira projects/teams.

Ideally this would be consolidated in a Dashboard etc which would also allow generation of MI such as # of issues, status, assignees etc.

Our Jira is quite basic in that Labels are not available and Jira Portfolio Plans are already in use for other purposes.

I don't think linking with 'Relates to' etc will help but not certain.

I had hoped Components would be the best solution but don't seem to work after initial investigation.

Themes, Fix Version/s and Teams are available to add to issues but I can't confirm if they are options or not.

Hoping i have missed something the community can suggest!

Earl McCutcheon · Answer

Hi @Adrian.Enright ,

Happy to help out,

As for where to display the data I would personally recommend using Confluence as your data dashboard, as you can use Jira dashboard gadgets on a confluence page as well as drop-in an iframe with a link to a google sheet described in my previous comment for a custom report tool.

Also, I played around with it a bit and came up with a few google Sheets specific formulas and quey examples on collecting information on Epic to Story relationships to build out a custom report.

Again Noting that this would be a starting point to display the data points but depending on the full details you are looking for in the report modify as needed.

The layout in the example below is 2 sheets titled Sheet1 and Sheet2.  On Sheet 1 I have a basic data pull using the Jira Cloud for Google Sheets (Official) connector app with a =jira formula like this:

=jira("project in(asdf,zxcv) and Issuetype in (Epic,story) ORDER BY Rank ASC","Issue Type,Key,Summary,Assignee,Status,Epic Link,issuelinks",0,)

It outputs the raw data dump like this, noting that I have Epics in both projects (ASDF & ZXCV), and the epics are tied to Stories in each project as well:

On Sheet 2 I set it up to show headers as the unique issue types from sheet 1 then using those headers to set up a few different join, filter, and query breakdowns to show the Stories that are connected to the Epics.

As a reference here is a screenshot of that data output once all the groupings occur, Column B is the list of Epics in the two projects, Column C is a Comma-separated list of the Stories in the rows Listed Epic, and then Column D-H is a list of the same stories from C however spaced out in individual columns for an alternate view of the data:

In Cell B1 I have the following formula that gives me a vertical list of all the epic issue types from sheet1 based on the header Row titled "Epic":

=filter(Sheet1!B:B,Sheet1!A:A = B1)

Next, I broke the subsequent stories out in 2 ways Column C is a condensed, comma-separated list of the stories in the Epic, and Columns D - H are those same stories but listed out as individual columns depending on whether you wanted a condensed list or an individual data point per issue.

to set up column "C" for the condensed list, in rows 2-5 the following Formula is used to generate a comma-separated list of all the stories contained in the Epic from column B iteration the row number for the B cell reference:

=join(", ",transpose(query(Sheet1!B:F,"Select B where F matches '"&join("|",split(B2,","))&"'",0)))

To get the individual Story values listed out as individual columns the following Formula was used in column D iterating the B cell reference again noting the transpose will take all available values and horizontally display them to the right of the cell the formula is in so make sure that you do not place an extra date to the right otherwise you would get an error here:

=transpose(query(Sheet1!B:F,"Select B where F matches '"&join("|",split(B2,","))&"'",0))

Hopefully, the example gives you some ideas on how far you can go to get exactly the report you are looking for with this tool.  but let me know if this helped you out or not, or if you have any other questions on parsing out specific data points for your reporting needs.

Regards,
Earl

Adrian.Enright · Answer

Hi Earl & Gorka,

Many thanks for taking the time to reply. Apologies for not being more clear - by MI I meant Management Information.

Having a data point that can be applied across Epics/Stories in multiple backlogs is the big problem.

This would minimise the amount of manual manipulation of details from a mass data dump as you suggested.

I believe our only option is a new Custom field to be used like a label which could be the unique data point/identifier to give a 'horizontal' cut of data.

I will look into this blog and apps you have suggested to see if they can help.

One question on Reporting & Dashboards - would you recommend JIRA over Confluence for this purpose?

Thanks again!

Gorka Puente {Appfire} · Answer

Hi @Adrian.Enright

There's an app called Comala Canvas for Jira that may fit for your use case. You can create boards with rows and columns, selecting any field, like the following example:

Hope this helps!