Report filtering issues based on parent Epic child issue types

Hi,

I have Epics that contain User Story or/and Technical Story.
An Epic that contain at least one User Story is considered as “functional” otherwise it is considered as “non-functional”.

I want to build a report with:
Pages
Project
Fix Version
Issue Type (with User Story and Technical Story selected)

Rows
Issue (with Issue hierarchy level selected)

Measures
Original estimated hours with sub-tasks

And I want to build a report with only issues belonging to “functional” Epics and another one with only issues belonging to “non-functional” Epics.
What I have in mind is to create a calculated measure that counts the number of User Story under the issue’s parent Epic and then filter issues based on this calculated measure.

For another report listing Epics (Epic Link dimension), I’ve created the following calculated measure Functional Epic to filter “functional”/“non-functional” Epics:

CASE WHEN
(
[Measures].[Issues created],
[Issue Type].[User Story]
) > 0
THEN
1
END

What could be the equivalent formula in the context of my report listing issues?

Thanks for your support.

Best Regards,
Christophe

Hi @Christophe,

You can try to create a new calculated measure that would identify all the issues that are a part of an Epic that has at least one User Story. Any other issue under and Epic that does not have a User Story in them could be labeled differently. Please have a look at an example below:

CASE WHEN
  ([Measures].[Issues created],
  [Epic Link].[Epic].GetMemberByKey([Measures].[Issue Epic Link]),
  [Issue Type].[Task],
  [Issue].CurrentHierarchy.DefaultMember) > 0
THEN 'Task Epic'
WHEN 
  ([Measures].[Issues created],
  [Epic Link].[Epic].GetMemberByKey([Measures].[Issue Epic Link]),
  [Issue].CurrentHierarchy.DefaultMember) > 0
THEN 'Story Epic'
END

The first branch of the condition checks if the current Issue dimension “Issue” level member is under an Epic that has a “Task” issue type issue under it. The second branch checks if this issue is under any Epic.
The result could look similar to the one below:

Best,
Roberts // support@eazybi.com

Hi @roberts.cacus,

Thanks.

It works but when I add this calculated measure in my report, Epics are added in the list of issues even if I’ve only selected User Story and Technical Story in Issue Type dimension and Issue hierarchy level in Issue dimension.

How can we avoid this?

Best Regards,
Christophe

@Christophe,

The calculated measure resets the current context for the Issue dimension. For the report to display relevant issues based on other dimension filters in pages, add a measure, for example, “Issues created” to the report. After that, filter the rows of the report by this measure to be greater than zero.

Finally, you can remove the measure “Issues created” from the report. The filter will keep it in the definition.

eazyBI documentation has more information on filtering report rows - https://docs.eazybi.com/eazybijira/analyze-and-visualize/create-reports#Createreports-Orderandfilterrowsbymeasurevalues.

Best,
Roberts // support@eazybi.com

@roberts.cacus,

OK, thanks.

I’ve an additional question related to this topic.
For Epics, I’ve created a new calculated measure Epic type that works :

CASE WHEN
[Epic Link].CurrentHierarchyMember.Level.Name = “Epic”
AND
[Epic Link].CurrentHierarchyMember.Name <> “(no epic)”
THEN
CASE WHEN
(
[Measures].[Issues created],
[Issue Type].[User Story]
) > 0
THEN
“Business”
ELSE
“Enabler”
END
END

To avoid to hard code “Business” and “Enabler” in several calculated measures, I’ve created two calculated measures:
Business Epic

“Business”

Enabler Epic

“Enabler”

But, when I use them in my Epic type calulated measure instead of the hard coded string, I’ve the following error that I don’t have with hard coded string:

#ERR: mondrian.olap.fun.MondrianEvaluationException: Expected value of type MEMBER; got value ‘Enabler’ (STRING)

Could you please explain why?

Best Regards,
Christophe

Sometimes conditionals have trouble determining the type of data the calculated measure contains. To overcome this, explicitly define that both of those measures are strings in the “Epic type” calculated measure:

CASE WHEN
[Epic Link].CurrentHierarchyMember.Level.Name = “Epic”
AND
[Epic Link].CurrentHierarchyMember.Name <> “(no epic)”
THEN
CASE WHEN
(
[Measures].[Issues created],
[Issue Type].[User Story]
) > 0
THEN
CAST([Measures].[Business Epic] as string)
ELSE
CAST([Measures].[Enabler Epic] as string)
END
END

Best,
Roberts // support@eazybi.com

Thanks, it works fine.

1 Like