How-To Enable Reporting on Issue Links (Workaround)
In this post, we tackle a common challenge faced by many Atlassian Analytics users: effectively reporting on the Worklog data from Jira.
* The below in no longer required! *
In our latest video tutorial, we tackle a common challenge faced by many Atlassian Analytics users: effectively reporting on the Issue Links data from Jira.
While Issue Links data are not explicitly loaded into the Atlassian Analytics schema, in this blog, we will share a workaround that allows you to report on linked issues.
If the record has a NULL LinkIssueID - then one of 2 possible scenarios is present:
1) The Linked Issue belongs to a Project not included in the data source!
2) The Linked Issue was deleted!
Step 1: Create a Custom Table in Atlassian Analytics
To begin extracting the Issue Link data, we first create a custom table that reconstruct the Issue Links from the issue history - where each Issue Link entry in the data source has its own row (for each direction). This approach allows for easier aggregation, filtering and analysis, as each Issue Link is treated as an individual entry.
Create a new custom table named 'view_IssueLink' - using the SQL query below:
WITH Jira_Keys_Lookup
AS (
SELECT `cte_Issue`.`issue_id` AS `IssueID`
,`cte_Issue`.`issue_key` AS `IssueKey`
FROM `jira_issue` AS `cte_Issue`
UNION ALL
SELECT `cte_IssueHistory`.`issue_id` AS `IssueID`
,`cte_IssueHistory`.`prev_value_string` AS `IssueKey`
FROM `jira_issue_history` AS `cte_IssueHistory`
WHERE (`cte_IssueHistory`.`field` = 'Key')
)
,Jira_Issue_Links
AS (
SELECT `cte_IssueHistory`.`issue_id` AS `IssueID`
,`cte_IssueHistory`.`value` AS `LinkIssueKey`
,`Jira_Keys_Lookup`.`IssueID` AS `LinkIssueID`
,TRIM(REPLACE(REPLACE(`cte_IssueHistory`.`value_string`, 'This issue', ''), `cte_IssueHistory`.`value`, '')) AS `LinkType`
,1 AS `LinkCount`
FROM `jira_issue_history` AS `cte_IssueHistory`
LEFT JOIN `Jira_Keys_Lookup` ON `Jira_Keys_Lookup`.`IssueKey` = `cte_IssueHistory`.`value`
WHERE (`cte_IssueHistory`.`field` = 'Link')
UNION ALL
SELECT `cte_IssueHistory`.`issue_id` AS `IssueID`
,`cte_IssueHistory`.`prev_value` AS `LinkIssueKey`
,`Jira_Keys_Lookup`.`IssueID` AS `LinkIssueID`
,TRIM(REPLACE(REPLACE(`cte_IssueHistory`.`prev_value_string`, 'This issue', ''), `cte_IssueHistory`.`prev_value`, '')) AS `LinkType`
,- 1 AS `LinkCount`
FROM `jira_issue_history` AS `cte_IssueHistory`
LEFT JOIN `Jira_Keys_Lookup` ON `Jira_Keys_Lookup`.`IssueKey` = `cte_IssueHistory`.`prev_value`
WHERE (`cte_IssueHistory`.`field` = 'Link')
)
,Jira_Issue_LinksAgg
AS (
SELECT `Jira_Issue_Links`.`IssueID`
,`Jira_Issue_Links`.`LinkType`
,`Jira_Issue_Links`.`LinkIssueID`
,SUM(`Jira_Issue_Links`.`LinkCount`) AS `TotalLinkCount`
FROM `Jira_Issue_Links`
WHERE `Jira_Issue_Links`.`LinkIssueID` IS NOT NULL
GROUP BY `Jira_Issue_Links`.`IssueID`
,`Jira_Issue_Links`.`LinkType`
,`Jira_Issue_Links`.`LinkIssueID`
UNION ALL
SELECT `Jira_Issue_Links`.`IssueID`
,`Jira_Issue_Links`.`LinkType`
,`Jira_Issue_Links`.`LinkIssuekey` AS `LinkIssueID`
,SUM(`Jira_Issue_Links`.`LinkCount`) AS `TotalLinkCount`
FROM `Jira_Issue_Links`
WHERE `Jira_Issue_Links`.`LinkIssueID` IS NULL
GROUP BY `Jira_Issue_Links`.`IssueID`
,`Jira_Issue_Links`.`LinkType`
,`Jira_Issue_Links`.`LinkIssuekey`
)
SELECT `Jira_Issues`.`IssueID`
,`Jira_Issues`.`LinkType`
,`Jira_Issues`.`LinkIssueID`
,`Jira_Issues`.`LinkIssueKey`
FROM (
SELECT `Jira_Issue_LinksAgg`.`IssueID`
,`Jira_Issue_LinksAgg`.`LinkType`
,`Jira_Issue_LinksAgg`.`LinkIssueID`
,`cte_Issue`.`issue_key` AS `LinkIssueKey`
,`Jira_Issue_LinksAgg`.`TotalLinkCount`
FROM `Jira_Issue_LinksAgg`
INNER JOIN `jira_issue` AS `cte_Issue` ON `Jira_Issue_LinksAgg`.`LinkIssueID` = `cte_Issue`.`issue_id`
WHERE length(`Jira_Issue_LinksAgg`.`LinkIssueID`) >= 30
UNION ALL
SELECT `Jira_Issue_LinksAgg`.`IssueID`
,`Jira_Issue_LinksAgg`.`LinkType`
,NULL AS `LinkIssueID`
,`Jira_Issue_LinksAgg`.`LinkIssueID` AS `LinkIssueKey`
,`Jira_Issue_LinksAgg`.`TotalLinkCount`
FROM `Jira_Issue_LinksAgg`
WHERE length(`Jira_Issue_LinksAgg`.`LinkIssueID`) < 30
) AS `Jira_Issues`
WHERE `Jira_Issues`.`TotalLinkCount` > 0
Create Custom Table: "view_LoggedWork"
Ensure that the new custom table integrates smoothly with the existing data structure by linking it to the primary tables using appropriate SQL JOINs
Step 3: Test
Finally, we test our new structure by creating a chart in the visual SQL editor. This step confirms the effectiveness of our setup and showcases the ease of applying filters and creating interactive dashboards.
- Start by selecting from the 'view_IssueLink' table the 'IssueID', and from the Issue table the 'Issue Key', and then the following 3 fields from the 'view_IssueLink' table; LinkType, LinkIssueID, and LinkIssueKey.
- Adding conditions to filter where 'Project Key' = {Your Project Key}.
- Check the Join Path: This helps confirm that the 'view_IssueLink' is correctly linked to the Issue table using the desired column (in this case, IssueID = Issue ID).
Final Thoughts
With the workaround above, you can create dashboards and reports on the linked issues and can also aggregate based on hierarchy.
As mentioned above, the main limitation of this workaround is that it will return a Linked Issue record for an already deleted Jira Issue!
If the record has a NULL LinkIssueID - then one of 2 possible scenarios is present:
- The Linked Issue belongs to a Project not included in the data source!
- The Linked Issue was deleted!