Hi
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.
Select
[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]
On
Convert(varchar,[spr].[ID]) = [customf].STRINGVALUE
Left Outer Join [jira].[AO_60DB71_RAPIDVIEW] As [rv]
on
[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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nic,
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,
Drickus
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Not directly, because you will have to read and parse every single board query to see if it might include the project you are looking for.
Seriously, don't try it with SQL, it's the worst possible way to get information out of Jira.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.