How do I build a report with an epic-level field as a row dimension and a story level field as the column dimension

Questions & Answers
1

We only define the fixVersion at the Epic level. I would like to build a report that shows me the count of stories (that are under epics) by status (column dimension) and by fixVersion (row dimension)

When I put fixVersion in the row dimension, status in the column, and issueType in the Pages dimension (to select only the story type I’m reporting on), I see all my counts show up in the row labeled “(no version)”

Thanks for any advise/guidance.

1 Like
2

Hi,

The idea for such measures is to iterate over all issues and count those being in the context of the report regarding status and issue type but except the fix version. Simultaneously we need to check if the parent epic for these issues is in the fix version of the report, but the parent epic does not need to be checked regarding the other dimensions (status and issue type) of the report:

nonzero(count(
  Filter (Descendants([Issue].CurrentMember,[Issue].[Issue]),
    ([Measures].[Issues created],
     [Fix version].Currenthierarchy.DefaultMember)>0 and 
    DefaultContext((
      [Measures].[Issues created],
      [Fix version].CurrentHierarchyMember,
      [Issue.Epic].[Parent].GetMemberbyKey([Issue].CurrentHierarchyMember.key).Parent,
      [Issue type].[Epic]
    )) >0 
  )
))

Kindly,
Janis, eazyBI support

3

Janis, thank you for this code, but I don’t understand how you built this or how i would use it. This should be a custom measure or a Custom member under one of my dimensions? Could you, by any chance, share a sample report that is set up this way? I stuck to out-of-the-box Jira fields, so a demo/sample report could be created (we’re actually using custom fields).

Thanks again.

-Seth

4

Seth,

This formula is a custom measure (“Issues count by epic version”), and it is designed for use in the report like you describe. Please, find attached a screenshot of a report where I tried it out. The report is giving the number of stories (as selected on the report pages) by status and counted to the Fix version taken from the related epic:

Stories by epic fix version
Stories by epic fix version.png1864×948 98.4 KB

The measure should work also when you replace the “Fix version” dimension by some other dimension created from some custom field if that is your use case.

Janis

5

Thank you, Janis. I see how you’re using that and I can give it a try, even if I don’t fully understand the MDX logic. But when I tried it on a simple cube (35,000 records, limited number of dimensions), it timed out the first time I tried it. Any suggestions?

6

Hi @janis.plume,
This query looks like displaying the first epic’s story’s status. How can we get the aggregate for all issues for that epic?

7

Hi,

This complicated calculation should count all the stories under the epic and show the count of stories under each status. The implementation iterates over all the Issues (regardless the Fix version) and checks if the parent epic has the Fix version from rows.

That should be working fine for the use case originally mentioned (unfortunately, with the performance issues as mentioned above).

Kindly,
Janis, eazyBI support

8

There is a relatively new feature of eazyBI allowing an alternative solution for this use-case. By using the advanced settings it is possible to create a new dimension with the inherited value of a Jira standard field (custom fields not supported yet).

See here for more: https://docs.eazybi.com/eazybijira/data-import/advanced-data-import-options/issue-link-field-dimensions

Kindly,
Janis, eazyBI support