We are currently building a Dimensional Reporting Model from the Jira database to do extensive Power BI Analysis\Trending and Tracking on the Jira data and are building directly from the jira database schema.
There is a need to view tasks per Kanban board and the challenge is to find a proper link from the ‘Kanban Board’ to its relevant project and jira issues in the database.
The table in question is the AO_60DB71_RAPIDVIEW table.
The below query returns all the sprint boards to the jiraissue table and works perfectly but the same logic does not apply for Kanban boards even though the Kanban board descriptions does appear in the A0…..RAPIDVIEW table.
Could you please be of assistance to help us identify the proper joins in the database to join the Kanban board to it’s project and issues.
The below code links jira issues to the relevant sprints.
[spr].[NAME] As [SprintName],
Case When spr.CLOSED = 1 Then 'Yes' Else 'No' End As [SprintClosed],
[customf].ISSUE As [IssueID],
[spr].ID As [SprintID],
[rv].[Name] As [BoardName],
Convert(Date,DateAdd(Second,Convert(Bigint,Convert(bigint,Left([START_DATE],10))) , Cast('1970-01-01 00:00:00' As DateTime))) As [SprintStartDate],
Convert(Date,DateAdd(Second,Convert(Bigint,Convert(bigint,Left(END_DATE,10))) , Cast('1970-01-01 00:00:00' As DateTime))) As [SprintEndDate]
From [jira].[customfieldvalue] AS [customf]
Inner Join [jira].[AO_60DB71_SPRINT] As [spr]
Convert(varchar,[spr].[ID]) = [customf].STRINGVALUE
Left Outer Join [jira].[AO_60DB71_RAPIDVIEW] As [rv]
[rv].[ID] = [spr].[RAPID_VIEW_ID]
Where [customfield] = (Select [ID] From [jira].[customfield] Where cfname = 'Sprint')
The relationship between a board and an issue is defined by the JQL used on the board and then filtered by the settings for the board (if you're interested in columns, swimlanes, quick filters or the Kanban board's "definition of done")
Your database query is, quite simply put, going to be horrid to construct and valid only once per board - you will need to build a new query for every single board, and adjust them as users configure their boards or create new ones. You'll have to replicate the JQL in SQL (they are very different things) and then apply board settings too.
I would strongly suggest that you do NOT try this with SQL.
I please have a follow up question. I realize that the Kaban to Issue link can only be retrieved by using the Rest API.
Is there any way to link the Project to the Boards using T-SQL.
The tables in questions is dbo.AO_60DB71_RAPIDVIEW, dbo.project maybe the [dbo].[searchrequest] table.
Thank you in advance,
I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...
Connect with like-minded Atlassian users at free events near you!Find an event
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no Community Events near you at the moment.Host an event
You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events