Pull data from various issuetypes in Jira

[Aswini Rajasekaran]

Hi, I have the below requirement. Can this be done in Jira?

Portfolio have the hierarchy defined as: Initiative, Feature, Epic, Story.

I want to build a report with the list of Stories that belong to an Epic (filtered by a custom field value) and the related fields of the corresponding Feature & Initiative.

I tried in EazyBI, but it times out every time I try to pull the data. It will be good if theres a solution in Jira itself rather than plugins

Thanks very much for any help.

[Roberts Čāčus · Accepted solution]

Hi @Aswini Rajasekaran

Reports in eazyBI can timeout because of the amount of data that needs to be considered. In your case, it could be the list of all issues expanded causing that.

Nevertheless,if you are open to exploring the option of creating the desired report with eazyBI I recommend importing inherited field dimensions with the help of JavaScript calculated custom fields - https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-custom-fields/javascript-calculated-custom-fields#JavaScriptcalculatedcustomfields-Inheritedfielddimension.

Define new JavaScript calculated custom fields or new calculated fields in eazyBI and pass the custom field value from Epics (use the value "epic_key" in the parameter `update_from_issue_key`) and from Advanced Roadmaps hierarchy issues (use the value "jpoh_parent_X" in the parameter `update_from_issue_key`, X being the number of level above sub-task in the Advanced Roadmaps hierarchy).

After importing these inherited fields as dimensions, you can use them in the report pages. In rows, you can select the Issue dimension in the "Advanced Roadmaps" hierarchy and use the "Story" level. The report could look similar to the one below:

The report is filtered by the inherited dimension "Epic Epic Direction" that is based on the the custom field "Epic Direction" available only in Epics. With this filter and a numeric measure in the report, you can also view the issues in the Epic with the particular value in the field.

Let me know if you have any questions.
Best,

Roberts // support@eazybi.com

[Aswini Rajasekaran]

Hi Roberts, thanks for that. I am able to pull the data from hierarchies using the javascript mentioned above. But as I said that the report times out even with 1 project, it doesn't look to be a viable solution.

EazyBI is a powerful tool, but timeout is a big issue when we are working with bigger dataset.

So, I am looking for other options.

Aswini

[Roberts Čāčus]

Hi @Aswini Rajasekaran

Fair enough. If you change your mind, you can share the definition of the report here or at our support email, and I can see whether there is room for optimization in the report itself -  https://docs.eazybi.com/eazybi/analyze-and-visualize/create-reports/export-and-import-report-definitions.

All the best,
Roberts // support@eazybi.com

[Aswini Rajasekaran]

Hi Robert, attached the report definition.

{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Project story",
"folder_name": "Aswini's",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issue Story Points]","[Measures].[Issue resolution date]"],"members":[]}]},"rows":{"dimensions":[{"name":"Advanced Roadmaps Initiatives/Theme","selected_set":["[Advanced Roadmaps Initiatives/Theme].[Advanced Roadmaps Initiatives/Theme].Members"],"members":[],"bookmarked_members":[]},{"name":"Advanced Roadmaps Features/Agile Initiative","selected_set":["[Advanced Roadmaps Features/Agile Initiative].[Advanced Roadmaps Features/Agile Initiative].Members"],"members":[],"bookmarked_members":[]},{"name":"Advanced Roadmaps Epic","selected_set":["[Advanced Roadmaps Epic].[Advanced Roadmaps Epic].Members"],"members":[],"bookmarked_members":[]},{"name":"Issue","selected_set":["[Issue.Advanced Roadmaps].[Story].Members"],"members":[],"bookmarked_members":[]},{"name":"Epic QBR","selected_set":["[Epic QBR].[Epic QBR].Members"],"members":[],"bookmarked_members":[]}],"filter_by":{"conditions":[{"expression":"[Epic QBR].CurrentHierarchyMember.GetCaption","operator":"matches","value":"QBR3","value_type":"string"}]},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"AIB Fulfilment","full_name":"[Project].[AIB Fulfilment]","drillable":true,"key":"AIBF","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":[],"current_page_members":["[Project].[AIB Fulfilment]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"row_dimension_headers":{"Epic QBR":true,"Advanced Roadmaps Epic":true,"Advanced Roadmaps Features/Agile Initiative":true,"Advanced Roadmaps Initiatives/Theme":true,"Issue":true}}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Issue resolution date","format_string":"mmm dd yyyy","formula":"[Issue].CurrentHierarchyMember.get('Resolved at')"},{"name":"Issue Story Points","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Story Points')","format_string":"#,##0.00"}]
}

Error:

Failed to execute query. Error message:
Illegal Capacity: -1104072124

EazyBI JVM options: Xms:4GB Xmx:8GB

Thanks

Aswini

[Roberts Čāčus]

Hi @Aswini Rajasekaran

Thank you for sharing the definition. You have a lot of dimensions in the report rows - that is the main cause of the encountered errors. eazyBI has to consider five dimension member cross-joins to return the results. Most of the dimensions have a lot of members.

As you focus on the Issue dimension "Advanced Roadmaps" hierarchy, I recommend removing all the Advanced Roadmaps dimension from report rows and keeping only the Issue dimension. There, you can expand the desired levels from top of your hierarchy to bottom with the "Expand All" option in "Drill or expand section".

Please remove the row filter on the Epic QBR dimension and move the dimension into the report pages. After that, define a new calculated member in the Epic QBR dimension with the formula below:

Aggregate(
  Filter(
    [Epic QBR].[Epic QBR].Members,
    [Epic QBR].CurrentMember.Name MATCHES '.*QBR3.*'
  )
)

Please see more details on defining calculated members in other dimensions here - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/calculated-members-in-other-dimensions.

Then, select this calculated member and use it in the report pages. Finally, use a numeric measure in your reports. They tie all the different dimensions used in your report together and help the report to return meaningful results faster. Instead of "Issue Story Points" property, please use the "Story Points created" or "Story Points resolved" measures.

Please see the suggested report structure below:

Best,
Roberts // support@eazybi.com

[Aswini Rajasekaran]

Thanks again Roberts, I am currently testing it. Will keep you posted.

Aswini

[Aswini Rajasekaran]

Hi Robert, this works and seems to solve my issue. Thanks very much again