Build a report with Initiative as columns and Labels as rows

Questions & Answers
, , , ,
1

Hi,

I have the following issue structure in Jira:

Initiative 1
|- Epic 1
  |- Task A (Label A)
  |- Task B (Label B)
  |- Task C (Label C)
|- Epic 2
  |- Task A (Label A)
  |- Task B (Label B)
  |- Task C (Label C)
|- Epic 3
  |- Task A (Label A)
  |- Task B (Label B)
  |- Task C (Label C)
Initiative 2
|- Epic 4
  |- Task A (Label A)
  |- Task B (Label B)
  |- Task C (Label C)
|- Epic 5
  |- Task A (Label A)
  |- Task B (Label B)
  |- Task C (Label C)
|- Epic 6
  |- Task A (Label A)
  |- Task B (Label B)
  |- Task C (Label C)
...

The tasks A, B and C are done in order and have a label attached to link all the task A logically together. The same for tasks B and C.

I want to create a report which indicate the number of Epic in

  • Step A (means Task A is Done or InProgress and Task B, C are in ToDo)
  • or Step B (means Task A is Done and B is Done or InProgress and Task C is in ToDo)
  • or Step C (means Task A and B are Done and Task C is in Progress or Done)

My issue is in the calculated measure to get the Epics from the Initiative and then the Tasks from the Epic to make the analysis of the current step.
I tried this in the calculated measure with no success:

Descendants([Advanced Roadmaps Initiative].CurrentHierarchyMember, [Advanced Roadmaps Initiative].[Epic])

[Advanced Roadmaps Initiative].CurrentHierarchyMember.Children

My report looks like this for now

image
image1367×1214 112 KB

Does anyone know how to navigate the issue hierachy ?

Thanks in advance.

Regards,
Gaël

2

Hi @citizen

Welcome to eazyBI Community! Thanks for posting your question!

I replicated the scenario on my test data.
Please make sure you are on the correct hierarchy level. In the screenshot below you see I have Issue dimension in Rows, Plans level (in your case it would be Advanced Roadmaps) and select the Initiative level.

This is the formula I used to count Epics in case the Stories are in certain Status Categories and with certain Labels (the example below should match the first case (Step A) in your use case. Follow the same approach for the other scenarios).
Adjust the formula below to your set up - amend the hierarchy level the Descendants function must iterate through - most likely it will be - Descendants([Advanced Roadmaps Initiative].CurrentHierarchyMember, [Advanced Roadmaps Initiative].[Epic]) and in the tuple functions correct the Issue type and Labels to match your use case.

NonZero(Count(
    Filter(
      Descendants([Issue.Plans].CurrentMember,[Issue.Plans].[Epic]),
      
      ([Measures].[Issues created],
      [Status.Category].[In Progress],
      [Issue Type].[Story],
      [Label].[labela])>0 AND
      
      ([Measures].[Issues created],
      [Status.Category].[To Do],
      [Issue Type].[Story],
      [Label].[labelb]) > 0  AND
      
      ([Measures].[Issues created],
      [Status.Category].[To Do],
      [Issue Type].[Story],
      [Label].[Labelc])> 0 
      
    )
  ))

image
image2148×1336 306 KB

Best wishes,

Elita from support@eazybi.com