Convert a scriptrunner jql into eazyBI

Hello dear madam and sir,

I have this JQL query that I want to integrate integrate in to eazyBI β†’

type in (Requirement) OR 
issueFunction in portfolioChildrenOf("type = Requirement") OR 
issueFunction in issuesInEpics("issueFunction in portfolioChildrenOf('type = Requirement')") OR 
issueFunction in linkedIssuesOf("issueFunction in issuesInEpics(\"issueFunction in portfolioChildrenOf('type = Requirement')\") ", "is cloned by") AND 
type = Story

Can you help please achieve that ?

I have already imported the β€˜is cloned by’ link as

[jira.customfield_isclonedby]
name = "Is cloned by"
inward_link = "is cloned by"
multiple_values = true
dimension = true

Can we solve it using just measure ( without defining new measure or using mdx)?

Best regards
@Bodi

Hi @Bodi,

You might want to check out this training video on translating JQL queries to eazyBI reports. The video (and PDF material) cover several examples illustrating the main principles. Training videos on specific topics.

Filters on report pages work well when JQL filtering criteria are related with AND operand (issue matches all listed criteria). In case filter criteria are related with OR operand (like in your JQL query), you might want to define a new calculated measure (in the Measures block) to check on each criteria separately and then see if any issue matched.

The structure for calculation is aggregate function Sum() where you can iterate through individual issues with function DescendatnSet() and filter those issues with a similar logic as for JQL. Note that enclosing brackets group some of the filter conditions as well.

Sum(
  Filter(
    --iterate through all issues at "Story" lelvel
    DescendantsSet([Issue.Advanced Roadmaps].CurrentHierarchyMember,[Issue.Advanced Roadmaps].[Parent]),
    --for each issue check filter criteri connected with OR condition 
    (
      --issue is descendant of Requirement 
      [Issue Type].[Issue Type].GetMemberNameByKey(
        Ancestor([Issue].CurrentHierarchyMember,[Issue.Advanced Roadmaps].[Requirement]
        ).Get('Issue type ID')) = "Requirement" 
      OR (
       --issue (at Story lelve) has link "is cloned by"
       NOT IsEmpty([Issue].CurrentHierarchyMember.Get('is cloned by')) AND
       [Measures].[Issue type] = "Story"
      )
    )
  ),
  --sum up issue count matching filter criteri
  CASE WHEN  --the measure "issues created" ensure to check on report filter criteri
    [Measures].[Issues created] > 0
  THEN --count each issues as one
    1
  END
)

Function Ancestor() would cover both conditions that issue itself or issue parent (Epic) is a descendant of Saga as eazyBI uses the hierarchy. There is no need to check on the Epic level separately.

The example above is built based on the assumption that you have an Advanced Roadmap hierarchy with hierarchy-level names: XXX β†’ Requirement β†’ Epic β†’ Story β†’ Sub-task.
When pasting the function in the formula editor, please use the autocomplete functionality to access the member library and correct the hierarchy levels and property names in lines 4, 9, and 13 (Calculated measures and members).

Best,
Zane / support@eazyBI.com

Thanks for the support

1 Like