Show epic children based on a custom field that only exists in the Epic

Julio_Oliveira · May 24, 2023, 9:09pm

Hello there!

I’m trying to create a JavaScript calculated custom field that will be used to filter/show all chilldren from an epic… based on an existing custom field that is only filled at the epic level.

In our business context all epics are identified by value stream (a custom field called “Cadeia de Valor”)… but at the Story Level we do not have this classification.

We want to show data in graphs that only exists on the epics children (story points for instance), but in the perspective of epics that are in a specific Value Stream.

To do that we’ve created this JavaScript calculated custom field:

[jira.customfield_28002_epic]
name = “Epic Cadeias de Valor”
dimension = true
data_type = “string”
multiple_values = true
split_by = “,”
javascript_code=‘’’
if (issue.fields.customfield_28002) {
issue.fields.customfield_28002_epic=issue.fields.customfield_28002;
}
‘’’

Although when we try to use this JS custom field to filter a graph… there is no value (none of the children are selected). May anyone help?

Thanks a lot!

Julio

zane.baranovska · May 31, 2023, 3:15pm

Hi @Julio_Oliveira,

Is the “Cadeias de Valor” single-select or multi-selection field?

Solution for Single-select field
If each epic can have only one value for “Cadeias de Valor”, then you should adjust the settings for “Epic Cadeias de Valor”. Remove parameters multiple_values and split_by, add parameter update_from_issue_key.

[jira.customfield_28002_epic]
name = "Epic Cadeias de Valor" 
data_type = "string"
dimension = true
update_from_issue_key = "epic_key"
javascript_code = '''
if(issue.fields.customfield_28002) {
   issue.fields.customfield_28002_epic = issue.fields.customfield_28002;
}
'''

Before applying the advanced settings, please validate the JavaScript code part on some Epic issue.

Here is how to validate the Javascript code before using it in the custom field definition
how to apply the changes in advanced settings.
More details on how to create JavaScript calculated custom fields that could be passed down to epic child issues are here: Inherited field dimension.

Solution for Multi-select field
If each epic can have one or more values for “Cadeias de Valor”, then you should use a different approach and create a calculated measure in the report that would look up epic field values for each issue.
The idea for the calculation is to iterate through all parent issues and filter parent issue that has epic with selected “Cadeias de Valor". Then sum up epic child issues ignoring their value for the custom field “Cadeias de Valor". The idea for calcaultion might look like this.

Sum(
  --set of epic child issues
  Filter(
    DescendantsSet([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Parent]),
    DefaultContext(
      ([Measures].[Issues created count],
      [Issue.Epic].CurrentHierarchyMember.Parent,
      [Cadeias de Valor].CurrentHierarchyMember)
    ) > 0
  ),
  --sum up child issues ignoring their value for Cadeias de Valor
  ([Measures].[Issues created count],
  [Cadeias de Valor].CurrentHierarchy.DefaultMember)
)

More details on calculated measures and mentioned functions are described here:

Best,
Zane / support@eazyBI.com

Julio_Oliveira · May 31, 2023, 10:56pm

Hi Zane!

Thanks a lot for your help

It is a multi-select field… so I’ve tried to apply your instructions in the measure called [Measures].[Story Points created for Child Issues] :

Sum(
–set of epic child issues
Filter(
DescendantsSet([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Parent]),
DefaultContext(
([Measures].[Story Points created],
[Issue.Epic].CurrentHierarchyMember.Parent,
[Cadeias de Valor].CurrentHierarchyMember)
) > 0
),
–sum up child issues ignoring their value for Cadeias de Valor
([Measures].[Story Points created],
[Cadeias de Valor].CurrentHierarchy.DefaultMember)
)

But it is not working.

For some reason everything is appearing is under a “none” option within the “Cadeias de Valor” filter:

And when a I try to choose a specific option on “Cadeias de Valor” (which is in use on some epics) nothing appears:

Here is the report definition export:

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Burnup da Release (Vazão de Story Points)”,
“folder_name”: “Release”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Story Points created for Child Issues]”,“[Measures].[Cumulative Story Points created for Child Issues]”,“[Measures].[Story Points resolved for Child Issues]”,“[Measures].[Cumulative Story Points resolved for Child Issues]”],“members”:[{“depth”:0,“full_name”:“[Measures].[Story Points created for Child Issues]”,“format_string”:“#,##0.00”,“report_specific”:true,“name”:“Story Points created for Child Issues”,“calculated”:true},{“depth”:0,“name”:“Cumulative Story Points created for Child Issues”,“full_name”:“[Measures].[Cumulative Story Points created for Child Issues]”,“drillable”:false,“calculation_of”:“[Measures].[Story Points created for Child Issues]”,“calculation”:“cumulative_sum”,“format_string”:“#,##0”},{“depth”:0,“full_name”:“[Measures].[Story Points resolved for Child Issues]”,“format_string”:“#,##0.00”,“report_specific”:true,“name”:“Story Points resolved for Child Issues”,“calculated”:true,“id”:“[Measures].[Story Points resolved for Child Issues]”},{“depth”:0,“name”:“Cumulative Story Points resolved for Child Issues”,“full_name”:“[Measures].[Cumulative Story Points resolved for Child Issues]”,“drillable”:false,“calculation_of”:“[Measures].[Story Points resolved for Child Issues]”,“calculation”:“cumulative_sum”,“format_string”:“#,##0.00”}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[“[Time.Weekly].[Story Points Planej. Release]”,“[Time.Weekly].[Sprint 6]”,“[Time.Weekly].[Sprint 7]”,“[Time.Weekly].[Sprint 8]”,“[Time.Weekly].[Sprint 9]”,“[Time.Weekly].[Sprint 10]”],“members”:[],“bookmarked_members”:[]}],“nonempty_crossjoin”:false},“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”:“Cadeias de Valor”,“selected_set”:[“[Cadeias de Valor].[All Cadeias de Valor]”],“members”:[{“depth”:0,“name”:“All Cadeias de Valor”,“full_name”:“[Cadeias de Valor].[All Cadeias de Valor]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:[“[Cadeias de Valor].[All Cadeias de Valor]”]},{“name”:“Cadeia de Serviços”,“selected_set”:[“[Cadeia de Serviços].[All Cadeia de Serviços]”],“members”:[{“depth”:0,“name”:“All Cadeia de Serviços”,“full_name”:“[Cadeia de Serviços].[All Cadeia de Serviços]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:[“[Cadeia de Serviços].[All Cadeia de Serviços]”]},{“name”:“Cadeia de Valor/Squad”,“selected_set”:[“[Cadeia de Valor/Squad].[All Cadeia de Valor/Squads]”],“members”:[{“depth”:0,“name”:“All Cadeia de Valor/Squads”,“full_name”:“[Cadeia de Valor/Squad].[All Cadeia de Valor/Squads]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:[],“current_page_members”:[“[Cadeia de Valor/Squad].[All Cadeia de Valor/Squads]”]},{“name”:“Epic Fix Version”,“selected_set”:[“[Epic Fix Version].[All Epic Fix Versions]”],“members”:[{“depth”:0,“name”:“All Epic Fix Versions”,“full_name”:“[Epic Fix Version].[All Epic Fix Versions]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Portfólio Listados”,“full_name”:“[Epic Fix Version].[Portfólio Listados]”,“drillable”:true,“key”:“PL”,“expanded”:true,“drilled_into”:false,“parent_full_name”:“[Epic Fix Version].[All Epic Fix Versions]”},{“depth”:2,“name”:“Unreleased”,“full_name”:“[Epic Fix Version].[Portfólio Listados].[Unreleased]”,“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:“[Epic Fix Version].[Portfólio Listados]”},{“depth”:3,“name”:“PI Planning #2 - 2023”,“full_name”:“[Epic Fix Version].[Portfólio Listados].[Unreleased].[PI Planning #2 - 2023]”,“url_id”:125869,“parent_key”:“PL”,“parent_full_name”:“[Epic Fix Version].[Portfólio Listados].[Unreleased]”}],“bookmarked_members”:[],“current_page_members”:[“[Epic Fix Version].[Portfólio Listados].[Unreleased].[PI Planning #2 - 2023]”]}]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{“freeze_header”:false}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Story Points created for Child Issues”,“formula”:“Sum(\n --set of epic child issues\n Filter(\n DescendantsSet([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Parent]),\n DefaultContext(\n ([Measures].[Story Points created],\n [Issue.Epic].CurrentHierarchyMember.Parent,\n [Cadeias de Valor].CurrentHierarchyMember)\n ) \u003e 0\n ),\n --sum up child issues ignoring their value for Cadeias de Valor\n ([Measures].[Story Points created],\n [Cadeias de Valor].CurrentHierarchy.DefaultMember)\n)”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Story Points created for Epics”,“formula”:“([Measures].[Story Points created],\n[Issue Type].[Epic])”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Story Points resolved for Epics”,“formula”:“([Measures].[Story Points resolved],\n[Issue Type].[Epic])”,“format_string”:“”},{“dimension”:“Measures”,“name”:“Story Points resolved for Child Issues”,“formula”:“–CASE WHEN NOT IsEmpty([Measures].[Story Points resolved for Child Issues]) THEN\n– CumulativeSum([Measures].[Story Points resolved for Child Issues])\n–END\nCache(\nSum(\n --set of epic child issues\n Filter(\n DescendantsSet([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Parent]),\n DefaultContext(\n ([Measures].[Story Points resolved],\n [Issue.Epic].CurrentHierarchyMember.Parent,\n [Cadeias de Valor].CurrentHierarchyMember,\n [Cadeia de Serviços].CurrentHierarchyMember)\n ) \u003e 0\n ),\n --sum up child issues ignoring their value for Cadeias de Valor\n ([Measures].[Story Points resolved],\n [Cadeias de Valor].CurrentHierarchy.DefaultMember,\n [Cadeia de Serviços].CurrentHierarchy.DefaultMember)\n)\n)”,“format_string”:“”}]}
} ],
“calculated_members”: [{“name”:“Sprint 6”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘01 May 2023’, ‘12 May 2023’)\n)”,“format_string”:“”,“dimension_hierarchy”:“Weekly”},{“name”:“Sprint 7”,“dimension”:“Time”,“dimension_hierarchy”:“Weekly”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘15 May 2023’, ‘26 May 2023’)\n)”,“format_string”:“”},{“name”:“Sprint 8”,“dimension”:“Time”,“dimension_hierarchy”:“Weekly”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘29 May 2023’, ‘09 Jun 2023’)\n)”,“format_string”:“”},{“name”:“Sprint 9”,“dimension”:“Time”,“dimension_hierarchy”:“Weekly”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘12 Jun 2023’, ‘23 Jun 2023’)\n)”,“format_string”:“”},{“name”:“Sprint 10”,“dimension”:“Time”,“dimension_hierarchy”:“Weekly”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘26 Jun 2023’, ‘07 Jul 2023’)\n)”,“format_string”:“”},{“name”:“Story Points Planej. Release”,“dimension”:“Time”,“formula”:“Aggregate(\n [Time.Weekly].[Week].DateMembersBetween(\n ‘10 Apr 2023’, ‘30 Apr 2023’)\n)”,“format_string”:“”,“dimension_hierarchy”:“Weekly”}]
}

Thanks a lot for your help!

Julio_Oliveira · May 31, 2023, 11:02pm

Regarding the custom field “Cadeias de Valor”:

With this configuration scheme:

Note that only covers the “Portfolio Listados” PA where all epics are created… and not the squads PAs that contain all stories (connected to the epics through epic link)

zane.baranovska · June 2, 2023, 6:44am

You might want to adjust the calculation slightly.

Use measure “Issues created” when checking on “Cadeias de Valor” for epic and keep the Sotry points created or Story points resolved when adding up values of epic child issues. Measure “Issues created” has value on any epic issues, on the other hand, Story points resolved would have value only on epics with story points assigned and also in resolved status.

Sum(
  --set of epic child issues
  Filter( 
    DescendantsSet([Issue.Epic].CurrentHierarchyMember,[Issue.Epic].[Parent]),
    DefaultContext(
      --use here "Issues created" to cover all epics regardless of resolution or assigned story points
      ([Measures].[Issues created], 
      [Issue.Epic].CurrentHierarchyMember.Parent,
      [Cadeias de Valor].CurrentHierarchyMember)
    ) > 0
  ),
  --sum up child issues ignoring their value for Cadeias de Valor
  ([Measures].[Story Points created],
  [Cadeias de Valor].CurrentHierarchy.DefaultMember)
)