How one can "join tables" using eazyBI

[Rodrigo Silva]

Hello community,

So, I have this situation regarding eazyBI reports:

Project A (let's say it has the key 'KEYA'), containing issue type "type I", with specifc custom fields (cf1, cf2)

Project B (let's say it has the key 'KEYB'), containing issue type "type II", with specific custom fields (cf3, cf4)

I have some issues of type I linked to issues of type II, using a link type x

How to produce a table, using eazyBI, in this format:

==============================================
|| key_issue_prjA_tpI | key_issue_prjB_tpII | cf1 | cf2 | cf3 | cf4 ||
==============================================
|| KEYA-2                  | KEYB-5                    | ab | df   | cd   | xy   ||
==============================================

So far I've managed to import a custom field of my link type as a dimension, but I'm still in trouble with the "join" itself (I know that this is not like an SQL join, I'm just using this image for the sake of example)

Does anybody know how to proceed?

Thanks!

[Rodrigo Silva · Accepted solution]

So, I've managed how to do the join.

There were two steps involved:

1 - Custom property created on advanced settings

2- Calculated measure using mdx

I'll put some sample codes, if it happens to someone to have the same need:

For step 1, the docs show how to import custom fields based on links: https://docs.eazybi.com/display/EAZYBIJIRA/Import+issue+links . This method however, didn't work for me (I've mistaken something). What in fact work is to put a custom javascript code, that reads from issue links field and set the value of the custom eazyBi field, as follows (adapted from documentation):

[jira.customfield_all_links]
name = "All links"
data_type = "string"
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 != "is Epic of" & link.type.outward != "jira_subtask_outward") {
                issuelinks.push(link.outwardIssue.key);       
            }
        }
        else {
            if( link.type.inward != "has Epic" & link.type.inward != "jira_subtask_inward") {
                issuelinks.push(link.inwardIssue.key);       
            }
        }
    }
    issue.fields.customfield_all_links = issuelinks[0];
}
'''

[Daina Tupule eazyBI]

All links import with calculated JavaScript custom field is a good solution when you want to analyze any link no matter a type of it. All links in many cases will have many issue links and a function GetmemberByKey will not work. You can use a similar function GetMembersByKeys instead. This function will retrieve a set of all linked issues instead of member, though. The function AllProperties works will not work there. You should apply some functions that work with sets.

In most cases, we suggest using Issue link import with link custom field configuration instead. 

With this option, you can import links supporting different scenarios. Here are two example scenarios:

- Bugs linked to StoriesA Feature linked to the same Story that groups several Stories. 

- A Feature linked to the same Story that groups several Stories. 

If you import link custom field as a dimension, it will work similar to Issue dimension. You will see Issue key and Summary as a member name. You can address issue properties from this dimension directly as well. 

For example, in a linked dimension created as link custom field has an option to address issue properties, here is an example of Bugs dimension created with link custom field:  

[Bugs].CurrentMember.Get('Created at')

GetmemberByKey or GetLinKedMember and GetMembersByKey will work in both cases. However, there will be more cases when you can use GetMemberByKey or GetLinkedMember to retrieve data from a particular linked issue, for example, address Feature (by link custom field Feature) of the Story and retrieve Feature assignee.

[Assignee].[User].GetMemberNameByKey(
[Issue].CurrentHierarchy.GetLinkedMember('Feature').Get('Assignee name'))

This example will help you address Hours spent on Bugs (by link custom field Bugs). The similar formula will work for All Links as well:  

 SUM(
 [Issue].[Issue].GetMembersByKeys([Issue].CurrentHierarchyMember.Get('Bugs')),
 DefaultContext((
 [Measures].[Hours spent],
 [Issue].CurrentMember,
 [Time].CurrentHierarchyMember,
 [Logged by].CurrentMember))
 )

Daina / support@eazybi.com

[Rodrigo Silva]

This custom javascript is reading just the first link - you'll need to create more custom fields and adapt as needed.

For step 2, the calculated member has this formula:

[Issue].[Issue].GetMemberByKey([Issue].CurrentHierarchyMember.get('All links')).AllProperties

In this example, I'm reading all properties from the linked issue, using the key imported on the first step ("all links" is not a good name in this example, since it refers to a single link key). So it's only a matter of changing the desired property on the linked issue, and the "join" is done.

Hope that solution can help someone!

Thanks,