How to get a CustomField Value from a Linked Issue

Ersin · May 22, 2020, 2:31pm

Hello eazybi team and community,

I am pretty new to eazyBI, so please excuse me if I totally overlook something.

I have a parent issue called ANDE and this ANDE issue has several linked issues. In most cases, a SYS issue, CM issue and one or two LINIE issues. One of these LINIE issues is open and not canceled. My Import Setting is all Issues from the ANDE Project (only Parent Issues).

I have managed to display the key of the LINIE issue that is not closed.

What I still need now is value of the custom field called “bundle number” from this LINIE issue.

My code which I entered in the Settings → Advanced Settings to get the non-canceled LINIE issue IssueKey:

[jira.customfield_verlteillin]
name = "Verlinkte Teilaufgaben LINIE"
data_type = "string"
dimension = true
multiple_values = true
split_by = ","
javascript_code = '''
var issuelinks = new Array();
  if ( issue.fields.issuelinks ) {
 
  var links = issue.fields.issuelinks;
  for (var i = 0; i < links.length; i++) {
    var link = links[i];
    
    if (link.outwardIssue) {
        if( link.type.outward == "has Task" && 
           link.outwardIssue.key.indexOf("LINIE-")>=0
           ) {
          issuelinks.push(link.outwardIssue.key);       
        }
    }
    }
  issue.fields.customfield_verlteillin = issuelinks.pop(",");
}
'''

My Question is: How do I get the value in my report from the Customfield “bundle number”, which is in the LINIE issue? The Linktype is “has Task” Example: ANDE-1234 has Task LINIE-1234 (The ANDE issue creates the LINIE issue) LINIE Issues are not sub-tasks.

Greetz
Ersin

martins.vanags · May 27, 2020, 8:14am

Hi @Ersin

I see you have figured out how to import issue links using Javascript in advanced settings. Nice.

However, there is also one easier approach (with less lines in the code) to import linked issues:
https://docs.eazybi.com/eazybijira/data-import/advanced-data-import-options/import-issue-links
Depending on the use-case you could try using both approaches.
If you need import linked issues just from a specific project.

In this case, the answer is in MDX side.

After you have imported linked issues in new dimension and imported your numeric custom field “Bundle number” as measures, you could use the MDX function GetMembersbykeys to extract the custom field values from your linked issues using imported properties and new dimension members.

CASE 
When
  [Issue].CurrentMember.Level.Name = "Issue"
  AND
  [Verlinkte Teilaufgaben LINIE].Currentmember is [Verlinkte Teilaufgaben LINIE].Defaultmember
Then
  Case when
    not isEmpty([Issue].CurrentHierarchyMember.Get('Verlinkte Teilaufgaben LINIE'))
  then
    NonZero(SUM(
    [Issue].[Issue].GetMembersByKeys(
      [Issue].CurrentHierarchyMember.get('Verlinkte Teilaufgaben LINIE')),
      DefaultContext((
        [Measures].[Bundler number created],
        [Issue].CurrentMember
        ))
    ))
  end
Else
  NonZero(SUM(
    Filter(
      Descendants([Verlinkte Teilaufgaben LINIE].Currentmember, [Verlinkte Teilaufgaben LINIE].[Verlinkte Teilaufgaben LINIE]),
      ([Measures].[Bundler number created]
        ) > 0),
    DefaultContext((
      [Measures].[Bundler number created],
      [Issue].[Issue].GetMemberByKey([Verlinkte Teilaufgaben LINIE].Currentmember.Key),
      [Verlinkte Teilaufgaben LINIE].DefaultMember
    ))
 ))
End

Martins / eazyBI support

Ersin · June 2, 2020, 1:31pm

Hi @martins.vanags,

thanks for your help and the information.

The problem is, the “Bundle Number” field is unfortunately not a NumberField but a text field. Because the “Bundle Number” contains letters.
As far as I know I import Textfields as Properties. Is that correct?

Can I also use MDX function GetMembersbykeys? Do I have to replace [Measures] with [Properties] in your code?

Thanks for your help

Best Regards

martins.vanags · June 4, 2020, 9:20am

Hi,

Can there be only one linked LINIE issue per each main issue?

Otherwise, how are you planing to return results for Bundle number in case when there are two linked LINIE issues (or even more than 2)? If that is not the sum, is it perhaps a comma separated string?

Martins / eazyBi support

Ersin · June 4, 2020, 9:50am

Hi @martins.vanags,

sometimes (in very few cases) there are two linked LINIE issues. One is canceled (Resolution: Canceled) and closed (Status: Closed). The other one is still in progress and not closed. The not closed LINE issue is the most important one.

To get this one I use “issuelinks.pop()” because the LINIE issue with the highest issue number is always not the closed one.
For Example linke issues: “LINIE 125, LINIE 4182”
LINIE 125: Close and Canceled.
LINIE 2763: open or in progress and has a Bundle Number and value looks like “LINIE_4182_3254_3196”

In the most cases the closed (Status: Closed) and canceled (Resolution: Canceled) LINIE Issue does not have a Bundle Number.

Best Regards
Ersin

martins.vanags · June 4, 2020, 10:48am

Hi,

In query language, you can’t use “most cases” when defining exact steps for calculation. Either there is one LINIE, one LINIE with bundle or there can be more than 1 with bundle number across the imported data set, the calculation should be prepared for all possible scenarios, or data should be cleaned in Jira.

For example:
There is one main issueA with two linked issues (LINIE 1 and LINIE 2).
And each of those 2 issues LINIES has Bundle numbers (Bundle 1 for the first and Bundle 2 for the second)
How the report then should display them in the report (assuming this is not one of the most cases)?

Martins / eazyBI support

Ersin · June 4, 2020, 11:50am

Hi @martins.vanags,

I understand.

I looked at the issues. And we only have one case where a Closed and Canceled LINIE issue has a bundle number. It is one of 12000 LINIE issues.

It would be okay if the bundle number of the canceled is shown in a comma separated string.

For Example: Main Issue A has linked issues: “LINIE 125, LINIE 4182”
Column in report: Bundle Number: “LINIE_125_254_126, LINIE_4182_3254_3196”

Best regards and thanks for your time

Ersin

martins.vanags · June 4, 2020, 12:36pm

@Ersin

Try this formula

CASE WHEN
[Issue].CurrentHierarchyMember.level.Name = "Issue"
AND
Not IsEmpty([Issue].CurrentHierarchyMember.get('Verlinkte Teilaufgaben LINIE'))
THEN
Generate(
[Issue].[Issue].Getmembersbykeys(
  [Issue].CurrentHierarchyMember.get('Verlinkte Teilaufgaben LINIE')
),
[Issue].CurrentHierarchyMember.key ||"_"|| Cast ([Issue].CurrentMember.get('Bundle Number') as string),  ', '
)
END

It would return all bundle numbers for each issue who has linked Linies,
But this would work only for issue level members from “Issue” dimension.
As you can imagine, you can’t aggregate totals as the bundle number is a string value.

Martins / eazyBI support

joey · September 10, 2020, 8:37pm

I THINK I’m trying to do the same thing @martins.vanags but more basic, I hope?? I have issues in project A that use 'Issue Link" and i just want those issues, the assignee and their version to appear. whats the easiest way to do that?

martins.vanags · September 14, 2020, 12:42pm

Hi @joey

The concept is the same. You would need to import linked issues and then create calculated measures to find the assignee, version etc for linked issues.

Martins / eazyBI support

joey · September 14, 2020, 1:27pm

Thanks @martins.vanags , can you help me with the formula? i don’t even know where to start.

martins.vanags · September 24, 2020, 10:42am

Hi @joey
You could start by importing linked issues as described here:
https://docs.eazybi.com/eazybijira/data-import/advanced-data-import-options/import-issue-links

After importing linked issues, make sure that both projects are selected for import.
Then you could create new calculated measures that find the assignee, version from linked issues.

Martins / eazyBI support

massaf · January 30, 2021, 11:11pm

@martins.vanags
I have similar problem here,
I imported all links and would like to have a measure to show values from a custom field in linked issues, I used below code as suggested and it works fine:

CASE WHEN
[Issue].CurrentHierarchyMember.level.Name = “Issue”
AND
Not IsEmpty([Issue].CurrentHierarchyMember.get(‘All links’))
THEN
Generate(
[Issue].[Issue].Getmembersbykeys(
[Issue].CurrentHierarchyMember.get(‘All links’)
),
Cast ([Issue].CurrentMember.get(‘Module’) as string), ', ’
)
END

However, values in my custom field "Module’ can be similar for linked issues, I need to show the unique values only, how can I avoid adding duplicates in the generate function? in other words, how can I check if the value I am adding has already been added to the calculated field or not and avoid adding it if it exists in the comma separated list?

martins.vanags · February 8, 2021, 7:46am

Hi @massaf

Here is what you can try.
First, make sure that you imported “Module” as a dimension in eazyBI.

Then use this formula for your calculated measure:

CASE WHEN
[Issue].CurrentHierarchyMember.level.Name = "Issue"
AND
Not IsEmpty([Issue].CurrentHierarchyMember.get('All links'))
THEN
Generate(
    Filter(
    [Module].[Module].Members,
      Instr(
          Generate(
          [Issue].CurrentHierarchyMember.GetLinkedMembers("All links"),
          [Issue].CurrentHierarchyMember.GetString('Module'), ","
          ),
                  cast([Module].CurrentMember.name as string)
      )>0
    ),
  [Module].CurrentMember.name,
  ","
)
END

Martins / eazyBI

Christine · June 2, 2021, 12:06pm

Hi Martins,
I would like to have the same but for the “Fix Version” of the linked issues. Afterwards I would like to find out what is the next higher fix version of the linked issues. For example:
Issue 1 has the fix Version 1
Issue 2 has the Fix Version 2
Issue 3 has the Fix Version 2
Issue 4 has the Fix Version 1
At the end I would like to have one value. Fix Version 2 since this is the next high Fix Version of the linked issues.

I have the Measure below and want to have the next higher fix version and unspecified shall be ignored.
Generate(
Filter([Fix Version].[Version].Members,
([Measures].[Issues created],
[Logged by].DefaultMember)>0
),
[Fix Version].CurrentHierarchyMember.Name,
“,”
)

Can you support here?

martins.vanags · June 3, 2021, 6:46am

@Christine

is the integer number in the Fix version name that defined the highest fix version from all?
or can you perhaps sort them by fix version start dates (the highest fix version is always started the last)?
In your case, you would also need to “Order” function and some parameter - how to order filtered set of fix versions. either by release date, start date or the integer at the end of fix version name.

Martins

Christine · June 8, 2021, 10:46am

Hi Martins,
This is a good idea. Thanks for the hint. I have the measure below and it works.

`CASE
WHEN [Measures].[Issue type]=“Concept Deliverable”
THEN
Generate(
Filter(
[Fix Version].[Version].Members,
DateInPeriod(
[Fix Version].CurrentMember.get(‘Release date’),
[Time].CurrentHierarchyMember
)
AND
([Time].CurrentHierarchy.DefaultMember,[Measures].[Issues created]) > 0

),
[Fix Version].CurrentHierarchyMember.GetString(‘Release date’),
', ’
)
END`

I get the result: 2020-11-30, 2021-11-30. This are the release dates of the Fix Version of the linked issues. But how can I compare yet? Do you have a proposal or is it better to use java code?

martins.vanags · June 14, 2021, 6:29am

@Christine

What exactly would you compare (in your example) and how it would look like in report results then?

Martins / eazyBI

Christine · June 18, 2021, 11:08am

Hi @martins.vanags ,
I have two issuetypes : Issuetype 1 (called Concept Deliverable) and Issuetype 2.
All issues of iyyuetype2 are linked to issue of issuetype 1 and have a Fix Version. I would like to use issuetype 1 as a summary row. That means I would like to have the information of the first fix version of the linked issues of issuetype 1. I get all fix versions of the linked issue in row one. But I would like to have only the first fix version of the linked issues.

I would like to have R20-11 in the first row only or the release date in the first date only.

I would like to have 2021-11-30 in the first row only or the release date in the first date only.

Here my code:
Planned Release:
CASE
WHEN [Measures].[Issue type]=“Concept Deliverable”
THEN
Generate(
Filter([Fix Version].[Version].Members,
[Measures].[Issues created]>0 AND
[Fix Version].CurrentHierarchyMember.Name <> “(no version)” AND
[Fix Version].CurrentHierarchyMember.Name <> “unspecified”
), [Fix Version].CurrentHierarchyMember.Name, ", "
)
ELSE [Measures].[Issue fix Versions]

END

Code for Release Date:
CASE

WHEN [Measures].[Issue type]=“Concept Deliverable”
THEN
Generate(
Filter(
[Fix Version].[Version].Members,
DateInPeriod(
[Fix Version].CurrentMember.get(‘Release date’),
[Time].CurrentHierarchyMember
)
AND
([Time].CurrentHierarchy.DefaultMember,[Measures].[Issues created]) > 0

),
[Fix Version].CurrentHierarchyMember.GetString(‘Release date’),
', ’

)
ELSE
Generate(
Filter(
[Fix Version].[Version].Members,
DateInPeriod(
[Fix Version].CurrentMember.get(‘Release date’),
[Time].CurrentHierarchyMember
)
AND
([Time].CurrentHierarchy.DefaultMember,[Measures].[Issues created]) > 0

),
[Fix Version].CurrentHierarchyMember.GetString(‘Release date’),
', ’
)
END

martins.vanags · July 5, 2021, 2:26pm

@Christine

Try this approach to find the earliest fix version (sorted by released date)

CASE
WHEN [Measures].[Issue type]="Concept Deliverable"
THEN
Order(
Filter([Fix Version].[Version].Members,
[Measures].[Issues created]>0 AND
[Fix Version].CurrentHierarchyMember.Name <> "(no version)" AND
[Fix Version].CurrentHierarchyMember.Name <> "unspecified"
), 
[Fix Version].CurrentHierarchyMember.getDate('Release date'),
BASC
).item(0).getCaption
ELSE cast([Measures].[Issue fix Versions] as string)
END

Martins