In a cross project Initative - Epic - Story set up only show the Initiatives that are linked to the project I choose


I use Jira roadmaps, and I have all my Initiatives in project XXX. The rest of the projects link their epics to the Initiatives of project XXX so a common set up would be:

(Inviative) XXX-1 → (Epic) AAA-1 → (Story) AAA-3
(Inviative) XXX-2 → (Epic) BBB-1 → (Story) BBB-3

I want to create a report with that only shows tickets from project AAA and Initiatives linked to that project.
How can I filter out the Initiatives linked to other projects?

Best Regards

Hi @Lara_LG
Please find how to import custom hierarchy in “Issue” dimension using Advanced Roadmaps.

Once the hierarchy is imported, you can use the “issue” dimension and select the initiative level in rows.
Then add the “Project” dimension to page filters and select the project AAA-1
If Nonempty rows filter is enabled, you should see only the count of epics and children of epics that belong to the filtered project.

Martins / eazyBI

Thanks Martin! With this same set up I wanted to get the name of the Initiative that is linked to the Story. But guess since the Parent Link is not in the Story but in the Epic is not possible without creating a custom metric?

Your guess is right. You would need a custom calculated measure to generate the name of the linked initiative for the story’s epic.
Try searching “Issue Advanced Roadmaps Initiative” property in “Measures” dimension.
That should return the key of the inititive
Then create new calculated meaure using this code:

  [Measures].[Issue Advanced Roadmaps Initiative]

Martins / eazyBI

1 Like

Hello Martins,
Thanks for your reply, regarding this. I have tested it and it is not very clear to me which is the meaning of the numbers.

In this example IDPO-400 shows a 2, and it has two issues underneath, but IDPO-401 shows a 1 and it has no issues. Which could mean it represents itself.
If issues have number 1 when they are on their own, shouldn’t IDPO-400 have a 3? To count itself?
Best Regards

In the hierarchy, the results are filtered by the report context.
Please export and share the report definition.
That should explain why one isue has just 2 issues (as normally in clear report it should have 3 issues created if 2 children below) if there is no other dimension filters that affects the results for issues created

Martins / eazyBI

Sorry Martins for the late answer.
The definition is:

  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Initiative Progress by Issues Status v2",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues resolved]","[Measures].[Open issues]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[2022]"],"members":[],"bookmarked_members":[]},{"name":"Issue","selected_set":["[Issue.Advanced Roadmaps].[Initiative].Members"],"members":[{"depth":1,"name":"IDPO-400","full_name":"[Issue.Advanced Roadmaps].[IDPO-400]","drillable":true,"key":"IDPO-400","expanded":true,"drilled_into":false},{"depth":2,"name":"UXT-3796","full_name":"[Issue.Advanced Roadmaps].[IDPO-400].[UXT-3796]","drillable":true,"key":"UXT-3796","expanded":true,"drilled_into":false,"parent_full_name":"[Issue.Advanced Roadmaps].[IDPO-400]"}],"bookmarked_members":[]}],"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}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Status","selected_set":["[Status].[All Statuses]"],"members":[{"depth":0,"name":"All Statuses","full_name":"[Status].[All Statuses]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Status].[All Statuses]"]},{"name":"Time","duplicate":true,"selected_set":["[Time].[All Times]","[Time].[Current year]"],"members":[{"depth":0,"name":"All Times","full_name":"[Time].[All Times]","drillable":true,"type":"all","drilled_into":false,"expanded":true},{"depth":1,"name":"2022","full_name":"[Time].[2022]","drillable":true,"parent_full_name":"[Time].[All Times]"}],"bookmarked_members":[],"current_page_members":["[Time].[2022]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"freeze_header":true}},"calculated_members":[]}
  } ],
  "calculated_members": [{"dimension":"Measures","name":"Open issues","format_string":"#,##0","formula":"CASE WHEN [Issue].CurrentMember.Level.Name \u003c\u003e 'Issue' THEN\n  Cache(\n    NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n      Cache([Measures].[Issues created]\n          - [Measures].[Issues resolved])\n    ))\n    + [Measures].[Issues created]\n    - [Measures].[Issues resolved]\n  )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n  -- optimized formula for drill through Issue\n  NonZero(IIF(\n      DateBeforePeriodEnd(\n        [Issue].CurrentMember.get('Created at'),\n        [Time].CurrentHierarchyMember) AND\n      NOT DateBeforePeriodEnd(\n        [Issue].CurrentMember.get('Resolved at'),\n        [Time].CurrentHierarchyMember),\n    ([Time].CurrentHierarchy.DefaultMember,\n      [Measures].[Issues created]),\n    0\n  ))\nEND"},{"dimension":"Time","name":"Current year","format_string":"","formula":"Aggregate({\n  [Time].[Year].CurrentDateMember\n})"}]

The measure “Issues resolved” counts only resolved issues in the tree view for your report.
are all 3 tickets IDPO-400, UXT-3796, and UXT-3820 currently resolved?
Can you add “Issue resolution date” column to your screenshot?

Btw, there is a simple way to hide the summary for issue dimension. Just click on any issue name in table report results and choose different display name for members from Issue dimension.


Thanks Martins for the tips on the display. Yes, all the issues have a resolution date.


As you can see by resolution dates, the UXT-3796 was not resolved in 2022 which is the page filter you use in “Time” dimension, therefore this issue key is not counted in the tree.
If you would remove the “Time” dimension or filter “All times”, you would see the result 3 for “Issues resolved” measure in your hierarchy tree at the IDPO-400 issue level

Martins / eazyBI