Track progress/generate MI across multiple Jira projects

[Adrian.Enright]

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! 

[Gorka Puente _Appfire_]

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!

[Adrian.Enright]

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!

[Earl McCutcheon]

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

[Earl McCutcheon]

Hello @Adrian.Enright ,

I am not entirely sure what you mean by the acronym "MI" but from the context, I can assume that you are looking for some metric based data point such as "measurable item" or "management insight" or "Miscellaneous Item"  as opposed to referencing the state of Michigan or "Mission Impossible".

But the Key take away would be to define a JQL to isolate the items you are looking to report on and then pump those data points into something to display the content.

A good initial kick-off point is setting up some dashboard gadgets and I recommend starting with the Blog post "5 steps to a killer JIRA dashboard" and referencing the Advanced Searching reference pages to define the JQL's that you want to use for the saved filters.

• Advanced search reference - JQL functions
• Advanced search reference - JQL fields
• Advanced search reference - JQL keywords
• Advanced search reference - JQL operators

in addition to the Blog above, setting up dashboards can be a bit limited in scope in the available gadgets to relay that data, and you could also look into one of our free add-on app options that will allow you to make customized reports by linking the live Jira data to external spreadsheet applications either Google Sheets or Excel via:

• Jira Cloud for Google Sheets (Official)
• Jira Cloud for Excel (official)

These connectors will allow you to pipe Jira data directly to a spreadsheet where you can then use all the native functionality in the spreadsheet applications to build out any custom reporting you are looking for.

You noted that you are trying to get a view of epic and stories spread across a few teams,  so looking at that requirement I would suggest pulling data from the desired projects the teams work across (i.e. Project A, B & C) and include all relevant fields,  Assignee, team, epic link, issue types, etc..... to do a mass data dump to your first sheet,  then in subsequent sheets break out the data by doing calls, counts, and lookup operations to build out the reports as simple or complex as you would like.

Let me know a little more about the data points you are trying to report on and I can take a look to see if I can help with some of the organization or formulas or possible gadgets that would help out.

Regards,
Earl